transactions causing exceptions
-
I have a couple sprocs that I wanted to enclose in a transaction. The transaction only has to assure that if one fails they all fail, I don't need to prevent updates or anything of the like, but that is somewhat irrelevant I guess. The problem is, is that as soon as the trans was put into place it always threw exceptions on the first sproc. I think I will just combine the sprocs together in sql, but I am curious to what I am doing wrong. Here is the code
Public Function UpdateFieldErrors(ByRef conn As SqlConnection, ByRef dt As DataTable) As Integer 'counter of the number of records updated Dim counter As Integer = 0 Dim corrected_fl As String Dim trans As SqlClient.SqlTransaction 'submit all the corrected values and the comments to the database For Each row As DataRow In dt.Rows 'ensure that the columns that were not corrected were not submitted Try 'only require a transaction that will allow all to rollback if required 'since there is a lock on the image other users updating or querying 'the data is of no concern trans = conn.BeginTransaction(IsolationLevel.ReadCommitted) Dim cmd As New SqlCommand("dbo.mySproc", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@err_id", row.Item("err_id")) cmd.Parameters.Add("@new_value", row.Item("new_value")) cmd.Parameters.Add("@comments", row.Item("comments")) 'only set the error to corrected if there is user input in the newvalue field If row.Item("new_value") = "" Or row.Item("new_value") Is DBNull.Value Then cmd.Parameters.Add("@corrected_fl", "N") Else cmd.Parameters.Add("@corrected_fl", "Y") End If cmd.ExecuteNonQuery() 'update the answer string containing all the students answers DbAnswerUpdate.UpdateAnswerString(conn, row) trans.Commit() counter += 1 Catch ex As Exception trans.Rollback() MessageBox.Show("Exception occured when inserting one of the updated rows.") End Try Next dt.AcceptChanges() Return counter End FunctionThanks for the help -
I have a couple sprocs that I wanted to enclose in a transaction. The transaction only has to assure that if one fails they all fail, I don't need to prevent updates or anything of the like, but that is somewhat irrelevant I guess. The problem is, is that as soon as the trans was put into place it always threw exceptions on the first sproc. I think I will just combine the sprocs together in sql, but I am curious to what I am doing wrong. Here is the code
Public Function UpdateFieldErrors(ByRef conn As SqlConnection, ByRef dt As DataTable) As Integer 'counter of the number of records updated Dim counter As Integer = 0 Dim corrected_fl As String Dim trans As SqlClient.SqlTransaction 'submit all the corrected values and the comments to the database For Each row As DataRow In dt.Rows 'ensure that the columns that were not corrected were not submitted Try 'only require a transaction that will allow all to rollback if required 'since there is a lock on the image other users updating or querying 'the data is of no concern trans = conn.BeginTransaction(IsolationLevel.ReadCommitted) Dim cmd As New SqlCommand("dbo.mySproc", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@err_id", row.Item("err_id")) cmd.Parameters.Add("@new_value", row.Item("new_value")) cmd.Parameters.Add("@comments", row.Item("comments")) 'only set the error to corrected if there is user input in the newvalue field If row.Item("new_value") = "" Or row.Item("new_value") Is DBNull.Value Then cmd.Parameters.Add("@corrected_fl", "N") Else cmd.Parameters.Add("@corrected_fl", "Y") End If cmd.ExecuteNonQuery() 'update the answer string containing all the students answers DbAnswerUpdate.UpdateAnswerString(conn, row) trans.Commit() counter += 1 Catch ex As Exception trans.Rollback() MessageBox.Show("Exception occured when inserting one of the updated rows.") End Try Next dt.AcceptChanges() Return counter End FunctionThanks for the helpYou forgot to tell us what the Exceptions are and on what lines they are occuring. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
You forgot to tell us what the Exceptions are and on what lines they are occuring. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome