471,344 Members | 1,556 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,344 software developers and data experts.

SQLTransaction rollback problem

I have two SQLConnection objects having the same connection string and
two corresponding SQLCommand objects for each connection object. I am
using SQLTransaction with the first SQLConnection object but the second
SQLConnection object does not have any SQLTransaction object.

I am inserting some data using the two command objects. The problem
happens when I try to do a rollback on the SQLTransaction object. The
rollback happens only before the first insert happens with the second
connection object. After that, the rollback does not happen.

here is the code snippet:

Dim cnTracker As New
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings.Item("sqlconntracker"))
Dim cmdTracker As New SqlCommand
Dim transTracker As SqlTransaction

Dim cnError As New
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings.Item("sqlconntracker"))
Dim cmdError As New SqlCommand

cnTracker.Open()
cmdTracker.Connection = cnTracker
transTracker = cnTracker.BeginTransaction(IsolationLevel.ReadComm itted)
cmdTracker.Transaction = transTracker

cnError.Open()
cmdError.Connection = cnError

'this gets rolled back correctly
cmdTracker.CommandType = CommandType.Text
cmdTracker.CommandText = "INSERT INTO Table1 (Field1) VALUES(1)"
cmdTracker.ExecuteNonQuery()

cmdError.CommandType = CommandType.StoredProcedure
cmdError.CommandText = "spImportColError"
cmdError.Parameters.Add(New SqlParameter("@DestColName",
enumDestCols.Value))
cmdError.Parameters.Add(New SqlParameter("@tblImp", "tblImport"))
cmdError.Parameters.Add(New SqlParameter("@fldImp", "FieldImport"))
cmdError.ExecuteNonQuery()

'this does NOT gets rolled back!!!!
cmdTracker.CommandType = CommandType.Text
cmdTracker.CommandText = "INSERT INTO Table1 (Field1) VALUES(2)"
cmdTracker.ExecuteNonQuery()

transTracker.Rollback()

Jul 21 '05 #1
2 1873
hi:

both commands should have the same connection and transaction in order for
both to be rolledbacked, in the way you have it right now, those are
independent transactions.

Hope this helps.

"ma*************@gmail.com" wrote:
I have two SQLConnection objects having the same connection string and
two corresponding SQLCommand objects for each connection object. I am
using SQLTransaction with the first SQLConnection object but the second
SQLConnection object does not have any SQLTransaction object.

I am inserting some data using the two command objects. The problem
happens when I try to do a rollback on the SQLTransaction object. The
rollback happens only before the first insert happens with the second
connection object. After that, the rollback does not happen.

here is the code snippet:

Dim cnTracker As New
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings.Item("sqlconntracker"))
Dim cmdTracker As New SqlCommand
Dim transTracker As SqlTransaction

Dim cnError As New
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings.Item("sqlconntracker"))
Dim cmdError As New SqlCommand

cnTracker.Open()
cmdTracker.Connection = cnTracker
transTracker = cnTracker.BeginTransaction(IsolationLevel.ReadComm itted)
cmdTracker.Transaction = transTracker

cnError.Open()
cmdError.Connection = cnError

'this gets rolled back correctly
cmdTracker.CommandType = CommandType.Text
cmdTracker.CommandText = "INSERT INTO Table1 (Field1) VALUES(1)"
cmdTracker.ExecuteNonQuery()

cmdError.CommandType = CommandType.StoredProcedure
cmdError.CommandText = "spImportColError"
cmdError.Parameters.Add(New SqlParameter("@DestColName",
enumDestCols.Value))
cmdError.Parameters.Add(New SqlParameter("@tblImp", "tblImport"))
cmdError.Parameters.Add(New SqlParameter("@fldImp", "FieldImport"))
cmdError.ExecuteNonQuery()

'this does NOT gets rolled back!!!!
cmdTracker.CommandType = CommandType.Text
cmdTracker.CommandText = "INSERT INTO Table1 (Field1) VALUES(2)"
cmdTracker.ExecuteNonQuery()

transTracker.Rollback()

Jul 21 '05 #2
I do not want the second connection in a transaction and I do NOT want
to roll back any changes on the second connection object. The issue is
that the inserts that are executed using the first connection object
(within the transaction) before the insert on the second connection
object get rolled back but NOT the inserts executed on the first
connection object but after the insert on the second connection object!

Jul 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by mahajan.sanjeev | last post: by
5 posts views Thread by Swami Muthuvelu | last post: by
2 posts views Thread by mahajan.sanjeev | last post: by
reply views Thread by eric.goforth | last post: by
1 post views Thread by Jason Huang | last post: by
2 posts views Thread by samuelberthelot | last post: by
15 posts views Thread by =?Utf-8?B?UGhpbCBKb2huc29u?= | last post: by
reply views Thread by shalini | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.