sqlstransaction problem

[Guest]

Hi all,
I have placeOrder function which look like the following:
Protected Function PlaceOrder() As Boolean
Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
myConnection.Open()
Dim myCommand As SqlCommand = myConnection.CreateCommand()
Dim myTrans As SqlTransaction
myTrans = myConnection.BeginTransaction
'Must assign both transaction object and connection to command
object for a pending local trxn
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
If Request.Cookies("Subway_StoreID").Value <> "" And
Session("FirstName") <> "" And Session("LastName") <> "" And Session("SSN")
<> "" Then
Try

Dim myOrder As Order = New Order

myOrder.AddEmployee(StoreID, Session("FirstName"),
Session("MI"), Session("LastName"), Session("Sex"), _
Session("SSN"), Session("Address"), Session("City"),
Session("State"), Session("Zip"), _
Session("DayPhone"), Session("EvePhone"),
Session("depenFlag"))
If Session("depenFlag") And Not Session("ctrlarr") Is
Nothing Then
Dim ctrlarr(5) As PeopleInfo
Dim i As Integer
ctrlarr = Session("ctrlarr")
For i = 0 To 5
If ctrlarr(i).FirstName <> "" And
ctrlarr(i).LastName <> "" And ctrlarr(i).SSN <> "" Then
'add employee's dependent
myOrder.AddDependent(ctrlarr(i).FirstName,
ctrlarr(i).MI, ctrlarr(i).LastName, Session("SSN"), "N", ctrlarr(i).SSN)
End If
Next
End If
myOrder.PlaceEmployeeOrder(PeriodCovered, Session("Plan"),
OrderTotal, PaymentReceived, Session("depenFlag"), PaymentMethod, _
Session("SSN"), MonthlyPayment)
myTrans.Commit()

Catch e As Exception
Try
myTrans.Rollback()

Catch ex As Exception
If Not myTrans.Connection Is Nothing Then
Label2.Text &= "An exception of type " &
ex.GetType().ToString() & _
" was encountered while attempting to roll back
the transaction. "

End If
End Try
Label2.Text &= "An exception of type " &
e.GetType().ToString() & _
" was encountered while inserting the data. "
Finally
myConnection.Close()

End Try
End If

End Function


After I call this function, I found that even I have problem with data
insertion for the dependents, and the statement myTrans.Rollback() is
executed and somehow the employee data is in the database already, it seems
not rolling back. I think is because in the method
myOrder.AddEmployee, I called a store procedure which seperately opens
another connection, that's why. Can you tell me in this case, do I have to
directly write insert statement in the application layer instead of using
data access layer store procedure?
here is the data access layer code:
Public Function AddEmployee(ByVal StoreID As String, ByVal FName As String,
ByVal MI As Char, ByVal LName As String, _
ByVal sex As String, ByVal ssn As String, ByVal Address As String,
ByVal City As String, ByVal State As String, ByVal Zip As String, _
ByVal Dayphone As String, ByVal Evephone As String, ByVal DFlag As
Boolean)
Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("Add_employee",
myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim pStoreID As SqlParameter = New SqlParameter("@storeID",
SqlDbType.VarChar, 50)
pStoreID.Value = StoreID
myCommand.Parameters.Add(pStoreID)

Dim pFName As SqlParameter = New SqlParameter("@FName",
SqlDbType.VarChar, 50)
pFName.Value = FName
myCommand.Parameters.Add(pFName)
...

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()


End Function

 

[Steven Cheng[MSFT]]

Hi Betty,

Welcome.

I think the problem here is just as you mentioned, the insert store
procedure call is executing on a different connection from other sql
statements in your main function. And ADO.NET SqlTransaction is connection
based, so all the SqlCommands which want to participate in the same
Transaction need to assign their Transaction property to a certain
SqlTransaction instance of a single SqlConnection.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)