[RESOLVED]Commit Assitance
Hey all,
I need to start using Commit, and rollback functionality to my code and need some advice in how best to implement it.
Currently I have two tables, a header table and a detail table. I first insert a header record, recording the primary key in a field and then I loop through all the details rows inserting them in to the detail table.
If there is any problems with the insertions along the way I need a way to rollback in order to cancel all insertions and display an error message to the user.
The following code is pretty much how I currently do it minus the commit, rollback code.
I use VB.Net 2008, MS SQL Server.
Many Thanks
Code:
Dim connBranch As SqlClient.SqlConnection
Dim cmdBranch As New SqlClient.SqlCommand
connBranch = New SqlClient.SqlConnection(<Connection String>)
connBranch.Open()
sBldQuery = New System.Text.StringBuilder
sBldQuery.AppendLine("INSERT INTO")
sBldQuery.AppendLine(" mytable1")
sBldQuery.AppendLine(" (")
sBldQuery.AppendLine(" <fields>")
sBldQuery.AppendLine(" )")
sBldQuery.AppendLine(" VALUES")
sBldQuery.AppendLine(" (")
sBldQuery.AppendLine(" <data>")
sBldQuery.AppendLine(" );")
sBldQuery.AppendLine("")
sBldQuery.AppendLine("SELECT Scope_Identity();")
cmdBranch.CommandText = sBldQuery.ToString
cmdBranch.CommandType = CommandType.Text
cmdBranch.Connection = connBranch
pkReturned = Integer.Parse(cmdBranch.ExecuteScalar().ToString)
For Each lvItem As ListViewItem In listview1.Items
sBldQuery = New System.Text.StringBuilder
sBldQuery.AppendLine("INSERT INTO")
sBldQuery.AppendLine(" mytabledetail")
sBldQuery.AppendLine(" (")
sBldQuery.AppendLine(" <fields>")
sBldQuery.AppendLine(" )")
sBldQuery.AppendLine(" VALUES")
sBldQuery.AppendLine(" (")
sBldQuery.AppendLine(" <data>,")
sBldQuery.AppendLine(" pkReturned")
sBldQuery.AppendLine(" );")
cmdBranch.CommandText = sBldQuery.ToString
iNumberOfRowsAffected = cmdBranch.ExecuteNonQuery()
Next
