Help With SqlTransactions and the SqlHelper object

[E. Rodriguez]

I am having difficulty using the SQLHelper object in the DataAccess
application block with SQLTransactions.

I have a function that performs 2 inserts. I intentionally broke the second
insert to see if my rollback occurs on the first inserted row. It appears
that the first row is in fact inserted, despite the error being thrown and
the transaction rolled back.

See the following code. It should rollback upon attempting the second call
to ExecuteNonQuery:

objConn.Open()

objTrans = objConn.BeginTransaction

Try

SqlHelper.ExecuteNonQuery(objTrans, CommandType.StoredProcedure,
"Procedure1", MySqlParametersHere)

SqlHelper.ExecuteNonQuery(objTrans, CommandType.StoredProcedure,
"Procedure2", MoreParametersHere)

objTrans.Commit()

Catch ex As Exception

objTrans.Rollback()

Finally

objConn.Close()

End Try

Any reason why this doesnt work?
Thanks

 

[Rogas69]

do you use
set implicit_transactions off
clause in your procedures? if not, statements are committed on their own.

Peter