email article printer friendly

Prevent ADO.NET Transaction Errors
by Roman Rehak

August 2003 Issue

Technology Toolbox: SQL Server, ADO.NET, VB.NET

Many database applications require the use of database transactions initiated from the client application. ADO.NET supports transactions just like its predecessor ADO, although the implementation is slightly different. Whether you're using ADO or ADO.NET, your code likely uses the traditional flow for transactions: Start a transaction, execute one or more commands, and either commit the transaction if no errors occur, or roll back the transaction inside of an error handler. There is a slight difference in the way ADO.NET handles transaction rollback compared to ADO. In some cases, your application might throw an unhandled error and crash even though your code implements an error handler. When Transact SQL executes on SQL Server and an error occurs, the error is considered either fatal or non-fatal. SQL Server rolls back fatal errors automatically before the execution returns to the client application. When your code on the client catches the error and tries to roll back the transaction again, ADO.NET raises the error, "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." The same code executed in ADO works without problems because the error is ignored by ADO.

I'll show you an example of this behavior and also provide you with a workaround. Start by creating a connection and a transaction:

Dim cn As SqlConnection = New _   SqlConnection()
cn.ConnectionString = _ 
   "Server=.;Integrated Security=SSPI"
cn.Open()
Dim tr As SqlTransaction = _ 
   cn.BeginTransaction()

Next, create a SqlCommand object, initialize it with SQL code to create a temporary table, and associate it with the existing SqlTransaction object:

Dim strSQL As String
strSQL = "CREATE TABLE " & _   "#TestTable(ID int NOT NULL)"
Dim cmd As SqlCommand = _ 
   New SqlCommand(strSQL, cn, tr)

Finally, create a Try-Catch block that executes the command, assigns and executes another command, and either commits or rolls back the transaction:

Try
cmd.ExecuteNonQuery()
cmd.CommandText = _ 
      "INSERT INTO #TestTable SELECT 'xyz'"
cmd.ExecuteNonQuery()
tr.Commit()
Catch ex As SqlException
MsgBox(ex.Message)
tr.Rollback()
End Try