SQLTransaction rollback problem

[mahajan.sanjeev@gmail.com]

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(S ystem.Configura tion.Configurat ionSettings.App Settings.Item(" sqlconntracker" ))
Dim cmdTracker As New SqlCommand
Dim transTracker As SqlTransaction

Dim cnError As New
SqlConnection(S ystem.Configura tion.Configurat ionSettings.App Settings.Item(" sqlconntracker" ))
Dim cmdError As New SqlCommand

cnTracker.Open( )
cmdTracker.Conn ection = cnTracker
transTracker = cnTracker.Begin Transaction(Iso lationLevel.Rea dCommitted)
cmdTracker.Tran saction = transTracker

cnError.Open()
cmdError.Connec tion = cnError

'this gets rolled back correctly
cmdTracker.Comm andType = CommandType.Tex t
cmdTracker.Comm andText = "INSERT INTO Table1 (Field1) VALUES(1)"
cmdTracker.Exec uteNonQuery()

cmdError.Comman dType = CommandType.Sto redProcedure
cmdError.Comman dText = "spImportColErr or"
cmdError.Parame ters.Add(New SqlParameter("@ DestColName",
enumDestCols.Va lue))
cmdError.Parame ters.Add(New SqlParameter("@ tblImp", "tblImport" ))
cmdError.Parame ters.Add(New SqlParameter("@ fldImp", "FieldImpor t"))
cmdError.Execut eNonQuery()

'this does NOT gets rolled back!!!!
cmdTracker.Comm andType = CommandType.Tex t
cmdTracker.Comm andText = "INSERT INTO Table1 (Field1) VALUES(2)"
cmdTracker.Exec uteNonQuery()

transTracker.Ro llback()

[Jportelas]

RE: SQLTransaction rollback problem

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.

"mahajan.sanjee v@gmail.com" wrote:
[color=blue]
> 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(S ystem.Configura tion.Configurat ionSettings.App Settings.Item(" sqlconntracker" ))
> Dim cmdTracker As New SqlCommand
> Dim transTracker As SqlTransaction
>
> Dim cnError As New
> SqlConnection(S ystem.Configura tion.Configurat ionSettings.App Settings.Item(" sqlconntracker" ))
> Dim cmdError As New SqlCommand
>
> cnTracker.Open( )
> cmdTracker.Conn ection = cnTracker
> transTracker = cnTracker.Begin Transaction(Iso lationLevel.Rea dCommitted)
> cmdTracker.Tran saction = transTracker
>
> cnError.Open()
> cmdError.Connec tion = cnError
>
> 'this gets rolled back correctly
> cmdTracker.Comm andType = CommandType.Tex t
> cmdTracker.Comm andText = "INSERT INTO Table1 (Field1) VALUES(1)"
> cmdTracker.Exec uteNonQuery()
>
> cmdError.Comman dType = CommandType.Sto redProcedure
> cmdError.Comman dText = "spImportColErr or"
> cmdError.Parame ters.Add(New SqlParameter("@ DestColName",
> enumDestCols.Va lue))
> cmdError.Parame ters.Add(New SqlParameter("@ tblImp", "tblImport" ))
> cmdError.Parame ters.Add(New SqlParameter("@ fldImp", "FieldImpor t"))
> cmdError.Execut eNonQuery()
>
> 'this does NOT gets rolled back!!!!
> cmdTracker.Comm andType = CommandType.Tex t
> cmdTracker.Comm andText = "INSERT INTO Table1 (Field1) VALUES(2)"
> cmdTracker.Exec uteNonQuery()
>
> transTracker.Ro llback()
>
>[/color]

[mahajan.sanjeev@gmail.com]

Re: SQLTransaction rollback problem

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!