Hi all,
I have placeOrder function which look like the following:
Protected Function PlaceOrder() As Boolean
Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("C onnectionString"))
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("C onnectionString"))
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
--
Betty
