Err "SqlConnection does not support parallel transactions"

[Rey]

Howdy all.
I'm getting the err "SqlConnection does not support parallel
transactions" using the test code below. Initially started with an
OleDBconnection and had the same err.
Not sure what the issue is...as my simple test was then going to call
several stored procs such that all either were successful or the
transaction was rolledback.

Reviewing this before sending, probably should also add an "Else
rollback" statement to the line:
If ((blnCustomerCreated) And (blnEmpCreated)) Then

Appreciate your help/suggestions on this.

Thank you,
Rey


************************

Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnConnect.Click
' establish connection to MSDE dd, start transaction

' below is an OleDB connection string NOT SQLconnection string
'Dim strConnectionString As String =
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
' "Persist Security
Info=False;Initial Catalog=Galactic;" & _
' "Data Source=REMUS\reyMSDE;"

' sql connection string
Dim strConnectionString As String = "Persist Security
Info=False;Integrated Security=SSPI;" & _

"database=Galactic;server=remus\reyMSDE"

' Dim connDB As New OleDbConnection(strConnectionString)
' err OleDBConnection does not support parallel transactions
' also get same err when using SQLConnection...

Dim connDB As New SqlConnection(strConnectionString)
Dim blnCustomerCreated As Boolean = False
Dim blnEmpCreated As Boolean = False
Dim oleSQLTrans As SqlTransaction


Try
' connect to db
connDB.Open()

' start transaction
'oleSQLTrans =
connDB.BeginTransaction(IsolationLevel.ReadUncommitted)
oleSQLTrans = connDB.BeginTransaction()

' create cust & employee
blnCustomerCreated = CreateCustomer(connDB, oleSQLTrans)
blnEmpCreated = CreateEmployee(connDB, oleSQLTrans)

' commit tranaction changes if both funct were successful
If ((blnCustomerCreated) And (blnEmpCreated)) Then
connDB.BeginTransaction.Commit()
txtErr.Text = "Transaction successful"
End If

'Else
'' per help robust programming...rollingback transaction
' Try
' connDB.BeginTransaction.Rollback()
' txtErr.Text = "Transaction rolledback"

' Catch excRollback As Exception
' txtErr.Text = "Problem encountered while rolling back
transaction:" & Chr(13) _
' & "Error Type: " &
excRollback.GetType.ToString() & ControlChars.Cr _
' & excRollback.Message
' End Try


' catch problem w/functs
Catch exc As Exception
txtErr.Text = "Problem inserting customer or employee:" & Chr(10)
_
& "Error Type: " & exc.GetType.ToString() & Chr(10)
_
& exc.Message

Try
If connDB.State = ConnectionState.Open Then
connDB.BeginTransaction.Rollback()
txtErr.Text = "Transaction rolledback"
End If

Catch excRollback As Exception
txtErr.Text = "Problem encountered while rolling back
transaction:" & Chr(13) _
& "Error Type: " & excRollback.GetType.ToString()
& ControlChars.Cr _
& excRollback.Message
End Try




' last actions to take before exiting transaction
Finally
If connDB.State.Open Then
connDB.Close()
' setting to nothing
connDB.Dispose()
End If

End Try

End Sub


Private Function CreateCustomer(ByVal connDB As SqlConnection, ByVal
oleSQLTrans As SqlTransaction) As Boolean
' test of transaction process running several functions
' this function returns true if customer successfully created,
false if not

Dim strInsert As String
Dim cmdInsert As New SqlCommand
Dim intRowsAffected As Integer

Try
strInsert = "INSERT INTO Customer (CustomerNumber, Name,
BillParentFlag, BillingAddress1, BillingCity,BillingState," _
& " BillingZipCode, BillingPlanetAbbrv, CreditLimit,
ProblemUseBillingInfoFlag, NoDeliveriesFlag)" _
& "Values(1001, 'ReyBob', 0, 'test address',
'testCity', 'AZ', '85383', 'EAR', 1000, 0, 0)"

cmdInsert.CommandText = strInsert
cmdInsert.Connection = connDB
cmdInsert.Transaction = oleSQLTrans
intRowsAffected = cmdInsert.ExecuteNonQuery()
' got here
Return True

' catch err and return false
Catch exc As Exception
Throw New Exception("Problem encountered while creating
customer transaction:" & Chr(13) _
& "Error Type: " & exc.GetType.ToString() &
ControlChars.Cr _
& exc.Message)
Return False

End Try

End Function


Private Function CreateEmployee(ByVal connDB As SqlConnection, ByVal
oleSQLTrans As SqlTransaction) As Boolean

' creates employee, returns true if successful, false if not

Dim strInsert As String
Dim cmdInsert As New SqlCommand
Dim intRowsAffected As Integer

Try
strInsert = "INSERT INTO Employee (FirstName, LastName, Address1,
City, State," _
& " ZipCode, Planet, Phone, Extension, HireDate)" _
& "Values('ReyBob', 'Collazo', 'test address',
'testCity', 'AZ', '85383'," _
& "'EAR', 6231236667, 123, 7/14/2005)"

cmdInsert.CommandText = strInsert
cmdInsert.Connection = connDB
cmdInsert.Transaction = oleSQLTrans
intRowsAffected = cmdInsert.ExecuteNonQuery()
' got here
Return True

' catch err and return false
Catch exc As Exception
Throw New Exception("Problem encountered while back
transaction:" & Chr(13) _
& "Error Type: " & exc.GetType.ToString() &
ControlChars.Cr _
& exc.Message)
Return False

End Try


End Function

 

[Miha Markic [MVP C#]]

Hi Rey,

You are creating another transaction here:
connDB.BeginTransaction.Commit()

Instead you should commit on original transaction instance, like:
oleSQLTrans.Commit();

 

[Sahil Malik [MVP]]

Rather than putting DbTransaction.Commit or Rollback in procedural if
statements as below, you should do that within a try catch finally
construct - ensuring

a) You call commit at the end of the try block if all goes well.
b) You always call atleast rollback - put this in catch.

This way your code will be a lot cleaner, and you won't get such errors.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Rey]

Thank you, Miha and Sahil for your replies.
Made the change to oleSQLTrans.Commit() and it worked.

Sahil - looked at reviews of your books - both the ADO.Net 1.1 and
2...will be checking them out at the local book store.

Thanks again,
Rey