How do I make a field automatically get its values from anothe

David,
Thanks for your response. We're using an Access Form as a front-end to enter
data into this SQL Server table. So I'm not really using a storedproc to
enter data. Would using a trigger the only way to handle this then?
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> I'm assuming you'll use stored procs for your inserts of course. So use
> an optional parameter and assign the default in the proc:
> CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
> UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
> GO
> CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
> (
> @.PaymentDate SMALLDATETIME,
> @.UpdatedPaymentDate SMALLDATETIME = NULL
> )
> AS
> INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
> VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
> GO
> EXEC dbo.usp_PaymentScheduleInsert
> @.PaymentDate = '2006-04-30T00:00:00.000' ;
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>Sam (Sam@.discussions.microsoft.com) writes:
> Thanks for your response. We're using an Access Form as a front-end to
> enter data into this SQL Server table. So I'm not really using a
> storedproc to enter data. Would using a trigger the only way to handle
> this then?
Yes, but I guess David's hint is that you should start using stored
procedures.
The trigger would look like:
CREATE TRIGGER sams_trigger ON tbl FOR INSERT AS
UPDATE tbl
SET updatedpaymentdate = i.paymentdate
FROM tbl
JOIN inserted i ON tbl.pkcol = i.pkcol
However, judging from the narrative, it seems to me that it would be
better to leave the column NULL. I'm assuming then that when the data is
entered, there has been no update to the payment date yet.
Then again, it was not clear to me whether this column is intended to
catch the date the client actually paid, or if this is a date agreeed-on
beforehand as the new date for the payment.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

How do I insert data from an Access db to a empty SQL database

Hi,

I'm new to VS2005 (vb.net) and here my situation

I have form with a dataset1 (tbl1, tbl2, tbl3, tbl4) pulling data from a Access db. and showing it on the form1(databound)

I need to write what is on form1 to the empty dataset2 in SQL 2005 db

I have created a new DB in SQL 2005 with a Table SQL1 which has the same fields as on form1. Please can some one show me how do I do this. Please

Thanks in advance for your response.

-NM

You can use the Export functionality of Access.

See this post for details:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295865&SiteID=1

|||

Thanks, Will do

-NM

How do I get uppercase values returned only.

I have a table where inactive names are lower case and active names are
uppercase. Note: Not my design.

Anyways I want to select all names form this table where the name is
uppercase. I see collate and ASCII pop up in searches but the examples
don't seem usable in queries as much as they were for creating tables
and such.

Thanks,
Philselect * from mytable where name = upper(name)

Joe Weinstein at BEA|||No luck with that

Here is my query
SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE (last_nme = UPPER(last_nme))
ORDER BY last_nme, first_nme

I still see lower case names.|||I fixed it with this
where ASCII(last_nme) = (ASCII(UPPER(last_nme))

Thanks for the responses.
Phil|||As Phillip found out... this will only work if you have set your
instance of SQL Server set to be case-sensitive. The default
installation makes the instance NOT case-sensitive.

-Tom.|||Phillip (pputzback@.ECommunity.com) writes:
> No luck with that
> Here is my query
> SELECT *, last_nme AS Expr1, first_nme AS Expr2
> FROM members
> WHERE (last_nme = UPPER(last_nme))
> ORDER BY last_nme, first_nme
> I still see lower case names.

This should do it:

SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE last_nme COLLATE Finnish_Swedish_CS_AS =
UPPER(last_nme) COLLATE Finnish_Swedish_CS_AS
ORDER BY last_nme, first_nme

You may prefer to use something else than Finnish_Swedish. It's the
CS_AS part that is the important.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How do I get a new record ID

I am taking values entered by a user in a form and inserting them into a
table. I then would like to obtain the record ID after I have added the
record. However I do not understand why the following code does not work.
Set conn = New ADODB.Connection
conn.Open
" Provider=SQLNCLI;Server=myserver\sqlexpr
ess;Database=MyDatabase;Trusted_Con
nection=yes;"
Set rs = New ADODB.Recordset
rs.Open "tblContract", conn, adOpenDynamic, adLockOptimistic, adcmdtype
With rs
.AddNew
[value1]
[value2]
etc
.Update
End With
Dim strGetID As String
strGetID = "SELECT SCOPE_IDENTITY() AS last_identity_value"
Set rs = conn.Execute(strGetID)
lContractID = rs.Fields("last_identity_value").Value
I have also tried:
lContractID = rs.Fields("ContractID").Value
instead of the scope_identity but this doesn't work either.
The variable lContractID is '0' in both cases and "last_identity_value" is
shown as empty. However the record is successfully entered in the table with
an ID.
Can someone please let me know what I'm doing wrong.Recordset objects are for RETRIEVING data. Please do not use them for
affecting data. Use a stored procedure.
CREATE PROCEDURE dbo.AddContact
@.value1 VARCHAR(32),
@.value2 VARCHAR(32),
..etc,
@.NewContactID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblContact(value1, value2, etc)
SELECT @.value1, @.value2, etc;
SELECT @.NewContactID = SCOPE_IDENTITY();
END
GO
Now, call the stored procedure and retrieve the new id from the output
parameter.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:CBAEE2FD-81C6-439F-B299-AAFD30AD3D89@.microsoft.com...
>I am taking values entered by a user in a form and inserting them into a
> table. I then would like to obtain the record ID after I have added the
> record. However I do not understand why the following code does not work.
> Set conn = New ADODB.Connection
> conn.Open
> " Provider=SQLNCLI;Server=myserver\sqlexpr
ess;Database=MyDatabase;Trusted_C
onnection=yes;"
> Set rs = New ADODB.Recordset
> rs.Open "tblContract", conn, adOpenDynamic, adLockOptimistic, adcmdtype
> With rs
> .AddNew
> [value1]
> [value2]
> etc
> .Update
> End With
>
> Dim strGetID As String
> strGetID = "SELECT SCOPE_IDENTITY() AS last_identity_value"
> Set rs = conn.Execute(strGetID)
> lContractID = rs.Fields("last_identity_value").Value
>
> I have also tried:
> lContractID = rs.Fields("ContractID").Value
> instead of the scope_identity but this doesn't work either.
> The variable lContractID is '0' in both cases and "last_identity_value" is
> shown as empty. However the record is successfully entered in the table
> with
> an ID.
> Can someone please let me know what I'm doing wrong.
>|||Aaron
I'm just writing in Access VB and using Wrox Beginning Access 2002 VBA as a
reference which shows the method I used to add a new record. Unfortunately
your Create Procedure is not recognised in Access VB. Maybe I posted this to
the wrong section.
Can you offer me any other help?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Recordset objects are for RETRIEVING data. Please do not use them for
> affecting data. Use a stored procedure.
> CREATE PROCEDURE dbo.AddContact
> @.value1 VARCHAR(32),
> @.value2 VARCHAR(32),
> ...etc,
> @.NewContactID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblContact(value1, value2, etc)
> SELECT @.value1, @.value2, etc;
> SELECT @.NewContactID = SCOPE_IDENTITY();
> END
> GO
> Now, call the stored procedure and retrieve the new id from the output
> parameter.
>
>
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:CBAEE2FD-81C6-439F-B299-AAFD30AD3D89@.microsoft.com...
>
>|||No, Aaron is right, and this is the right section. Based on your connection
string, you are connecting to SQL Server, not an Access DB. With SQL
Server, it's usually best to perform modifications with stored procedures.
Invoke the Execute method on the Connection object to create the procedure
(this only needs to be done once). Invoke the Execute method on a Command
object with parameters to execute the stored procedure.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:B481AD0C-E4F8-4977-B38E-D1797D475BB2@.microsoft.com...
> Aaron
> I'm just writing in Access VB and using Wrox Beginning Access 2002 VBA as
> a
> reference which shows the method I used to add a new record. Unfortunately
> your Create Procedure is not recognised in Access VB. Maybe I posted this
> to
> the wrong section.
> Can you offer me any other help?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Brian thank you for your response. I did migrate the db from Access to SQL
Express and am now trying to amend my code. I think I have more to learn! Ar
e
there any online tutorial which you'd recommend?
"Brian Selzer" wrote:

> No, Aaron is right, and this is the right section. Based on your connecti
on
> string, you are connecting to SQL Server, not an Access DB. With SQL
> Server, it's usually best to perform modifications with stored procedures.
> Invoke the Execute method on the Connection object to create the procedure
> (this only needs to be done once). Invoke the Execute method on a Command
> object with parameters to execute the stored procedure.
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:B481AD0C-E4F8-4977-B38E-D1797D475BB2@.microsoft.com...
>
>|||I think your best bet is to hit Borders and buy a book, unless you're
comfortable with BOL and MSDN. I'm sure that there are a plethora of good
books on SQL Server, ADO, VB Database, etc. Maybe you should repost and ask
for suggestions. My library's pretty lean on introductory database books.
The only one I own is a vintage 1990 Que book, "Using SQL," that features
dBASE.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:5A75C0E6-B3B3-47A5-B242-1A4D3F96EBCC@.microsoft.com...
> Brian thank you for your response. I did migrate the db from Access to SQL
> Express and am now trying to amend my code. I think I have more to learn!
> Are
> there any online tutorial which you'd recommend?
> "Brian Selzer" wrote:
>|||Brian, you are probably right there are no quick fixes.
I see that you don't part with your old books either!
"Brian Selzer" wrote:

> I think your best bet is to hit Borders and buy a book, unless you're
> comfortable with BOL and MSDN. I'm sure that there are a plethora of good
> books on SQL Server, ADO, VB Database, etc. Maybe you should repost and a
sk
> for suggestions. My library's pretty lean on introductory database books.
> The only one I own is a vintage 1990 Que book, "Using SQL," that features
> dBASE.
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:5A75C0E6-B3B3-47A5-B242-1A4D3F96EBCC@.microsoft.com...
>
>

How do I encapsulate Transactions in a data entry form with multiple insert and update statement

I have 3 tables - Sales, Purchases, and PurchasesAppropriation

Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table.

I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that?

(Note, this whole thing takes place in the form's button's click event)
(Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1679851&SiteID=1)

I have an insert statement passed in the beginning of the form's button's click event (to the Sale table)

Code Snippet

connstr.Open()
Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)
cmd.ExecuteNonQuery()
connstr.Close()

Then I have it populate the dataset (with the data from the Purchase table)

Code Snippet

Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)
dataAdapter.Fill(dt)
DataGridView1.DataSource = dt
dataAdapter.Dispose()

Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated.

Code Snippet

connstr.Open()
Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)
cmd2.ExecuteNonQuery()
connstr.Close()

If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table.

Code Snippet

Sub MarkBalanceEmpty()
connstr.Open()
Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)
cmd4.ExecuteNonQuery()
connstr.Close()
End Sub

Code Snippet

Sub MarkBalancePartial()
connstr.Open()
Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)
cmd5.ExecuteNonQuery()
connstr.Close()
End Sub

(Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.)

Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table.

Code Snippet

connstr.Open()
Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)
cmd3.ExecuteNonQuery()
connstr.Close()

And the whole thing is done.

Now what I did was, add a Begin Transaction before the record in the sale table is inserted

Code Snippet

'Dim TransStart as String = "Begin Transaction"
connstr.Open()
Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr)
cmd0.ExecuteNonQuery()
connstr.Close()

and a Commit at the end, when everything is done (i.e after the cmd3 is executed)

Code Snippet

'Dim TransEnd as String = "Commit Transaction Go"
connstr.Open()
Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr)
cmd6.ExecuteNonQuery()
connstr.Close()

I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong? Why can't I get the transaction to behave as it should?

I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP).

You can manage transactions at the ADO.NET level, using the Transaction and Connection objects.

Here's the basic approach:

Code Snippet

Dim cn as New SqlConnection(cnstring)

Dim tr As SqlTransaction

cn.Open()

Try

tr = cn.BeginTransaction(IsolationLevel.Serializable)

'do your work here

tr.Commit()

Catch as Exception

tr.Rollback()

End Try

|||

Okay, I did as you said, and it seems to work fine, atleast it didn't make any changes to the database, as it is supposed to do (or not do, depends on the way you look at it). But, it did throw an error on the rollback statement, as is shown here -

http://img502.imageshack.us/img502/818/rollbackerror1hs2.jpg

Is it supposed to do that? I mean, shouldn't it rollback the changes and continue with the statements after the rollback, without throwing an error? Where am I going wrong?

|||The connection object needs to remain open throughout the whole transaction. You shouldnt close it until all of your statements have finished executing.|||

Oh, you can do that? I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure.

I'll try that and let you know what happens.

|||

Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above.

Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not.

Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement.

|||

You need to keep the connection open for the duration of the transaction, ie. you need to open before or at the top of the try and close after you've done a commit or rollback.

I don't see where you are initiating the transaction. You open the connection, create a command and then execute it. The transaction is never being created.

|||

If you're referring to my screenshot, obviously you won't see where I'm opening the connection, because, as you mentioned, it has to be at the top. The portion you're seeing only pertains to where in the code the error appears, and that is at the rollback, which obviously will be at the bottom of the code. You're seeing lines 91 - 119, whereas the transaction is explicitly started at line 15 and the connection is opened on line 46.

I see now where I'm going wrong, I closed the connection on line 97 before the rollback is being executed on line 104. Even when I tried to keep the connection open throughout, I made the mistake of closing the connection before the rollback statement. I'll put the close connection after the rollback, and see what happens.

Nope, no luck, tried putting the close connection string statement after the rollback, but still get the same error.

I think there's something wrong with the try catch block you gave me, maybe a line of code or more are missing.

I don't get any squigly lines underneath any text in my entire code window, meaning everything seems to be fine so far, no syntax errors, but I think something else might be missing.

(e.g. in your code, you said -

Code Snippet

Catch As Exception

you forgot the 'ex' and that gave me a blue squigly line underneath the 'As Exception'. Also you had said -

Code Snippet

Dim cn as New SqlConnection(cnstring)

whereas it should have been -

Code Snippet

Dim cn as New SqlClient.SqlConnection

).

Mind you, I am still a beginner, so most of the times I don't know if the code samples you guys give are incomplete/incorrect or not. So of there are any other such mistakes, I might not be able to find them and correct them.

|||

No problem.

We all make boo-boo's.

My post was meant as an outline to the approach.

Can you post that entire section of your code so I can see what all is going on?
Kinda hard to troubleshoot in the dark

|||

No problem. It's a little big, but here's the entire 150 lines of code for my form -

The form designer itself has 9 textboxes with corresponding labels, and a datagridview and a button.

Code Snippet

Public Class frmSales

Dim dt As New DataTable()

Dim connstr As New SqlClient.SqlConnection

Dim RowIndex As Integer = 0

Dim PurNo As Integer

Dim NewBalPurQty As Integer

Dim tr As SqlClient.SqlTransaction

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

tr = connstr.BeginTransaction(IsolationLevel.Serializable)

Dim InsertSales As String

Dim SelectPurchases As String

Dim InsertPurAppropriation1 As String

Dim InsertPurAppropriation2 As String

Dim SaleNo As String = TextBox1.Text

Dim BillNo As String = TextBox2.Text

Dim SaleDate As String = TextBox3.Text

Dim Broker As String = TextBox4.Text

Dim Share As String = TextBox5.Text

Dim Quantity As String = TextBox6.Text

Dim Rate As String = TextBox7.Text

Dim Amount As String = TextBox8.Text

Dim BalPurQty As String

Dim FullPurQty As Integer

Dim PurDate As Date

Dim PurRate As Double

Dim PurAmount As Double

InsertSales = "Insert Into Sales Values (" _

& SaleNo & "," & "'" & BillNo & "'" & "," & "'" & SaleDate & "'" & "," & "'" & Broker & "'" & "," & _

"'" & Share & "'" & "," & Quantity & "," & Rate & "," & Amount & ");"

SelectPurchases = "Select PurchaseNo, BillNo, Date, Broker, ShareName, Quantity, Rate, Amount, Old, Balance, BalanceQty From Purchases Where (ShareName = " & "'" & Share & "')" & " And (Balance <> 'e') Order By Date;"

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open()

Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)

cmd.ExecuteNonQuery()

'connstr.Close()

Dim dataAdapter2 As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)

dataAdapter2.Fill(dt)

DataGridView1.DataSource = dt

dataAdapter2.Dispose()

For i As Integer = 0 To (dt.Rows.Count - 1)

RowIndex = i

BalPurQty = dt.Rows(RowIndex)("BalanceQty")

PurDate = dt.Rows(RowIndex)("Date")

PurRate = CStr(dt.Rows(RowIndex)("Rate"))

If CInt(BalPurQty) = CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

Exit For

ElseIf CInt(BalPurQty) > CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

NewBalPurQty = CInt(BalPurQty) - CInt(Quantity)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkPartial()

Exit For

ElseIf CInt(BalPurQty) < CInt(Quantity) Then

FullPurQty = CInt(BalPurQty)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

Quantity = CInt(Quantity) - CInt(BalPurQty)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

InsertPurAppropriation1 = "Insert Into PurchaseAppropriation Values (" _

& SaleNo & "," & PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & _

"'" & "," & FullPurQty & "," & PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)

cmd2.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

End If

Next

InsertPurAppropriation2 = "Insert Into PurchaseAppropriation Values (" & SaleNo & "," & _

PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & "'" & "," & FullPurQty & "," & _

PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)

cmd3.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

tr.Commit()

MsgBox("Record Inserted")

Catch ex As Exception

tr.Rollback()

connstr.Close()

End Try

ClearTextBoxes()

End Sub

Sub ClearTextBoxes()

TextBox1.Clear()

TextBox2.Clear()

TextBox3.Clear()

TextBox4.Clear()

TextBox5.Clear()

TextBox6.Clear()

TextBox7.Clear()

TextBox8.Clear()

End Sub

Sub MarkEmpty()

Dim UpdatePurchases1 As String

UpdatePurchases1 = "Update Purchases Set Balance = 'e', BalanceQty = 0 Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)

cmd4.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

End Sub

Sub MarkPartial()

Dim UpdatePurchases2 As String

UpdatePurchases2 = "Update Purchases Set Balance = 'p', BalanceQty = " & NewBalPurQty & " Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)

cmd5.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

End Sub

End Class

|||

Ah, the problem is that your trying to initiate the transaction before you open the connection.

The transaction needs a connection in place.

You need this basic structure:

Code Snippet

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open() 'Open Connection

Dim tr As SqlClient.SqlTransaction = connstr.BeginTransaction(IsolationLevel.Serializable) 'Start transaction

Try

... do stuff

tr.Commit() 'Save our work

Catch ex as Exception

tr.Rollback() 'remove our work

...handle exception, throw error, etc.

Finally

connstr.Close() 'close the transaction

End Try

...continue processing

|||

My form's code is exactly as I've posted above, except, now I've modifed the order of the connection string's opening and the transaction starting, as you've suggested in the post above, and I have added this line to the catch block

Code Snippet

Dim ex as Exception

tr.Rollback()

MsgBox("Database Error!" & vbCrLf & ex.Message)

So after I ran the form, this is what I got. There still seems to be something missing.

http://img141.imageshack.us/img141/8656/transactionerror1yc0.jpg

On a bright note, I don't get that above error anymore. The transaction's rollback feature seems to be working fine, and it also processes the lines after the try catch block as it should, that is it clears the textboxes fine.

|||

Progress!!

ExecuteNonQuery's apparently need to be manually enlisted into the transaction.

See if this fixes you up:

After you create each command, add a line:

Code Snippet

cmd.Transaction = tr

Before you execute the command.

Since you have multiple command objects with different names, you'll need to adjust the object name for each occurance.

|||

Nope, only this time, I get a new error.

http://img510.imageshack.us/img510/5476/transactionerror2ds3.jpg

|||DaleJ, I'm still stuck with this thing, and need your help. Waiting for your reply.

How do I encapsulate Transactions in a data entry form with multiple insert and update statement

I have 3 tables - Sales, Purchases, and PurchasesAppropriation

Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table.

I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that?

(Note, this whole thing takes place in the form's button's click event)
(Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1679851&SiteID=1)

I have an insert statement passed in the beginning of the form's button's click event (to the Sale table)

Code Snippet

connstr.Open()
Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)
cmd.ExecuteNonQuery()
connstr.Close()

Then I have it populate the dataset (with the data from the Purchase table)

Code Snippet

Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)
dataAdapter.Fill(dt)
DataGridView1.DataSource = dt
dataAdapter.Dispose()

Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated.

Code Snippet

connstr.Open()
Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)
cmd2.ExecuteNonQuery()
connstr.Close()

If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table.

Code Snippet

Sub MarkBalanceEmpty()
connstr.Open()
Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)
cmd4.ExecuteNonQuery()
connstr.Close()
End Sub

Code Snippet

Sub MarkBalancePartial()
connstr.Open()
Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)
cmd5.ExecuteNonQuery()
connstr.Close()
End Sub

(Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.)

Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table.

Code Snippet

connstr.Open()
Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)
cmd3.ExecuteNonQuery()
connstr.Close()

And the whole thing is done.

Now what I did was, add a Begin Transaction before the record in the sale table is inserted

Code Snippet

'Dim TransStart as String = "Begin Transaction"
connstr.Open()
Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr)
cmd0.ExecuteNonQuery()
connstr.Close()

and a Commit at the end, when everything is done (i.e after the cmd3 is executed)

Code Snippet

'Dim TransEnd as String = "Commit Transaction Go"
connstr.Open()
Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr)
cmd6.ExecuteNonQuery()
connstr.Close()

I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong? Why can't I get the transaction to behave as it should?

I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP).

You can manage transactions at the ADO.NET level, using the Transaction and Connection objects.

Here's the basic approach:

Code Snippet

Dim cn as New SqlConnection(cnstring)

Dim tr As SqlTransaction

cn.Open()

Try

tr = cn.BeginTransaction(IsolationLevel.Serializable)

'do your work here

tr.Commit()

Catch as Exception

tr.Rollback()

End Try

|||

Okay, I did as you said, and it seems to work fine, atleast it didn't make any changes to the database, as it is supposed to do (or not do, depends on the way you look at it). But, it did throw an error on the rollback statement, as is shown here -

http://img502.imageshack.us/img502/818/rollbackerror1hs2.jpg

Is it supposed to do that? I mean, shouldn't it rollback the changes and continue with the statements after the rollback, without throwing an error? Where am I going wrong?

|||The connection object needs to remain open throughout the whole transaction. You shouldnt close it until all of your statements have finished executing.|||

Oh, you can do that? I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure.

I'll try that and let you know what happens.

|||

Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above.

Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not.

Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement.

|||

You need to keep the connection open for the duration of the transaction, ie. you need to open before or at the top of the try and close after you've done a commit or rollback.

I don't see where you are initiating the transaction. You open the connection, create a command and then execute it. The transaction is never being created.

|||

If you're referring to my screenshot, obviously you won't see where I'm opening the connection, because, as you mentioned, it has to be at the top. The portion you're seeing only pertains to where in the code the error appears, and that is at the rollback, which obviously will be at the bottom of the code. You're seeing lines 91 - 119, whereas the transaction is explicitly started at line 15 and the connection is opened on line 46.

I see now where I'm going wrong, I closed the connection on line 97 before the rollback is being executed on line 104. Even when I tried to keep the connection open throughout, I made the mistake of closing the connection before the rollback statement. I'll put the close connection after the rollback, and see what happens.

Nope, no luck, tried putting the close connection string statement after the rollback, but still get the same error.

I think there's something wrong with the try catch block you gave me, maybe a line of code or more are missing.

I don't get any squigly lines underneath any text in my entire code window, meaning everything seems to be fine so far, no syntax errors, but I think something else might be missing.

(e.g. in your code, you said -

Code Snippet

Catch As Exception

you forgot the 'ex' and that gave me a blue squigly line underneath the 'As Exception'. Also you had said -

Code Snippet

Dim cn as New SqlConnection(cnstring)

whereas it should have been -

Code Snippet

Dim cn as New SqlClient.SqlConnection

).

Mind you, I am still a beginner, so most of the times I don't know if the code samples you guys give are incomplete/incorrect or not. So of there are any other such mistakes, I might not be able to find them and correct them.

|||

No problem.

We all make boo-boo's.

My post was meant as an outline to the approach.

Can you post that entire section of your code so I can see what all is going on?
Kinda hard to troubleshoot in the dark

|||

No problem. It's a little big, but here's the entire 150 lines of code for my form -

The form designer itself has 9 textboxes with corresponding labels, and a datagridview and a button.

Code Snippet

PublicClass frmSales

Dim dt AsNew DataTable()

Dim connstr AsNew SqlClient.SqlConnection

Dim RowIndex AsInteger = 0

Dim PurNo AsInteger

Dim NewBalPurQty AsInteger

Dim tr As SqlClient.SqlTransaction

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

tr = connstr.BeginTransaction(IsolationLevel.Serializable)

Dim InsertSales AsString

Dim SelectPurchases AsString

Dim InsertPurAppropriation1 AsString

Dim InsertPurAppropriation2 AsString

Dim SaleNo AsString = TextBox1.Text

Dim BillNo AsString = TextBox2.Text

Dim SaleDate AsString = TextBox3.Text

Dim Broker AsString = TextBox4.Text

Dim Share AsString = TextBox5.Text

Dim Quantity AsString = TextBox6.Text

Dim Rate AsString = TextBox7.Text

Dim Amount AsString = TextBox8.Text

Dim BalPurQty AsString

Dim FullPurQty AsInteger

Dim PurDate AsDate

Dim PurRate AsDouble

Dim PurAmount AsDouble

InsertSales = "Insert Into Sales Values (" _

& SaleNo & "," & "'" & BillNo & "'" & "," & "'" & SaleDate & "'" & "," & "'" & Broker & "'" & "," & _

"'" & Share & "'" & "," & Quantity & "," & Rate & "," & Amount & ");"

SelectPurchases = "Select PurchaseNo, BillNo, Date, Broker, ShareName, Quantity, Rate, Amount, Old, Balance, BalanceQty From Purchases Where (ShareName = " & "'" & Share & "')" & " And (Balance <> 'e') Order By Date;"

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open()

Dim cmd AsNew SqlClient.SqlCommand(InsertSales, connstr)

cmd.ExecuteNonQuery()

'connstr.Close()

Dim dataAdapter2 AsNew SqlClient.SqlDataAdapter(SelectPurchases, connstr)

dataAdapter2.Fill(dt)

DataGridView1.DataSource = dt

dataAdapter2.Dispose()

For i AsInteger = 0 To (dt.Rows.Count - 1)

RowIndex = i

BalPurQty = dt.Rows(RowIndex)("BalanceQty")

PurDate = dt.Rows(RowIndex)("Date")

PurRate = CStr(dt.Rows(RowIndex)("Rate"))

IfCInt(BalPurQty) = CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

ExitFor

ElseIfCInt(BalPurQty) > CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

NewBalPurQty = CInt(BalPurQty) - CInt(Quantity)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkPartial()

ExitFor

ElseIfCInt(BalPurQty) < CInt(Quantity) Then

FullPurQty = CInt(BalPurQty)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

Quantity = CInt(Quantity) - CInt(BalPurQty)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

InsertPurAppropriation1 = "Insert Into PurchaseAppropriation Values (" _

& SaleNo & "," & PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & _

"'" & "," & FullPurQty & "," & PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd2 AsNew SqlClient.SqlCommand(InsertPurAppropriation1, connstr)

cmd2.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

EndIf

Next

InsertPurAppropriation2 = "Insert Into PurchaseAppropriation Values (" & SaleNo & "," & _

PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & "'" & "," & FullPurQty & "," & _

PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd3 AsNew SqlClient.SqlCommand(InsertPurAppropriation2, connstr)

cmd3.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

tr.Commit()

MsgBox("Record Inserted")

Catch ex As Exception

tr.Rollback()

connstr.Close()

EndTry

ClearTextBoxes()

EndSub

Sub ClearTextBoxes()

TextBox1.Clear()

TextBox2.Clear()

TextBox3.Clear()

TextBox4.Clear()

TextBox5.Clear()

TextBox6.Clear()

TextBox7.Clear()

TextBox8.Clear()

EndSub

Sub MarkEmpty()

Dim UpdatePurchases1 AsString

UpdatePurchases1 = "Update Purchases Set Balance = 'e', BalanceQty = 0 Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd4 AsNew SqlClient.SqlCommand(UpdatePurchases1, connstr)

cmd4.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

EndSub

Sub MarkPartial()

Dim UpdatePurchases2 AsString

UpdatePurchases2 = "Update Purchases Set Balance = 'p', BalanceQty = " & NewBalPurQty & " Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd5 AsNew SqlClient.SqlCommand(UpdatePurchases2, connstr)

cmd5.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

EndSub

EndClass

|||

Ah, the problem is that your trying to initiate the transaction before you open the connection.

The transaction needs a connection in place.

You need this basic structure:

Code Snippet

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open() 'Open Connection

Dim tr As SqlClient.SqlTransaction = connstr.BeginTransaction(IsolationLevel.Serializable) 'Start transaction

Try

... do stuff

tr.Commit() 'Save our work

Catch ex as Exception

tr.Rollback() 'remove our work

...handle exception, throw error, etc.

Finally

connstr.Close() 'close the transaction

End Try

...continue processing

|||

My form's code is exactly as I've posted above, except, now I've modifed the order of the connection string's opening and the transaction starting, as you've suggested in the post above, and I have added this line to the catch block

Code Snippet

Dim ex as Exception

tr.Rollback()

MsgBox("Database Error!" & vbCrLf & ex.Message)

So after I ran the form, this is what I got. There still seems to be something missing.

http://img141.imageshack.us/img141/8656/transactionerror1yc0.jpg

On a bright note, I don't get that above error anymore. The transaction's rollback feature seems to be working fine, and it also processes the lines after the try catch block as it should, that is it clears the textboxes fine.

|||

Progress!!

ExecuteNonQuery's apparently need to be manually enlisted into the transaction.

See if this fixes you up:

After you create each command, add a line:

Code Snippet

cmd.Transaction = tr

Before you execute the command.

Since you have multiple command objects with different names, you'll need to adjust the object name for each occurance.

|||

Nope, only this time, I get a new error.

http://img510.imageshack.us/img510/5476/transactionerror2ds3.jpg

|||DaleJ, I'm still stuck with this thing, and need your help. Waiting for your reply.

How do I embed and retrieve Crystal Reports from my app

Using VB .Net 2005 with Crystal Reports XI Release 2. I have a form that has a CrystalReportViewer control on it. When someone prints I run a sub that create a new instance of the form, creates a new reportdocument, loads the report to the new document, then passes the document to the report viewer on the form. This works great except I would like to either embed my crystal reports directly into my app or better yet embed them into thier own reports dll file. I want to do this so updates are easier and I can use the "Publish" function of VB 05 which doesn't seem to like publishing all the seperate files. The embedding should be the easy part, just set them as embedded resource. But I'm having trouble with the syntax to pull them out. For example you can use this to pull out a embdded icon:
Function GetEmbeddedIcon(ByVal strName As String) As Icon
Return New Icon(System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream(strName))
End Function

But I cannot get the syntax right to pull out and return a crystal report. Anyone have any ideas on how to pull the reports back out or a better way to do this. I dont have a lot of reports, about 15 right now, but that could grow.

-AllanWell...I couldn't find the answer anywhere and no one replied here. But I figured it out so I might as well help someone down the road.

Set your crystal reports to "embedded resource" and the copy to "do not copy". Then in your code you can clal them like any other object with a "New (reportname)". For example I have a crystal reports viewer control (apptly named CrystalReportViewer) on a form called ReportViewerForm. I made a small sub to print reports:

Friend Sub PrintReports(ByVal myReportFileName As ReportDocument, Optional ByVal mySQLFormula As String = "")
' Prints out reports pass to it by other procedures and forms.
Dim PrintPreview As New ReportViewerForm
Try
If mySQLFormula <> "" Then myReportFileName.RecordSelectionFormula = mySQLFormula
PrintPreview.CrystalReportViewer.ReportSource = myReportFileName
PrintPreview.ShowDialog()
Catch ex As CrystalDecisions.CrystalReports.Engine.EngineException
MsgBox("A error was generated by the Crystal Reports engine. Error details: " & ex.Message, MsgBoxStyle.Critical, "Engine Error")
Finally
myReportFileName = Nothing
PrintPreview = Nothing
End Try
End Sub

Then when I need to print a report, for example my Log report (called LogEntryReport.rpt) I call it like this:

PrintReports(New LogEntryReport, "{qLogEntryReport.LogIdNumber} = " & CurrentLogId)

passing my report as a new ReportDocument and my sql command (which is optional for those reports that don't need it). Now I can have one routine that will display reports and call it from anywhere passing the report name. Works dandy and the reports are embedded in the app...less clutter.

Hopefully this helps someone and its not a waste of bandwidth.

-Allan.