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.