SQLTransaction rollback problem

[mahajan.sanjeev]

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()

[Jportelas]

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()

[mahajan.sanjeev]

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!