chee68
asked on
commit/rollback ado transaction
Hi,
The following is my vb code using access database. I try to simulate the rollback action but not all the transaction is fully rollbacked. Only transaction in UpdBillDetData and UpdBillHdrData can be rollbacked. The transaction in RunSQL1(strSQL1, cnnPhoneData),RunSQL1(strS QL, cnnPhoneData) and RunSQL1(strSQL3, cnnPhoneData) can not be rollbacked. (fyi,UpdBillDetData and UpdBillHdrData are using recordset.update to update the record) Why? How to solve it?
Sub EditRec()
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim SStr As String
Dim cmd As ADODB.Command
Dim TmpTbl As String
Dim rsTmptbl As ADODB.Recordset
Dim rsBillHdr As ADODB.Recordset
Set cmd = New ADODB.Command
strSQL = "INSERT INTO " _
& "BillDetHist " _
& "Select * " _
& "FROM BillDet in '" & DataMasPath & "'" _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'"
strSQL1 = "INSERT INTO " _
& "BillHdrHist " _
& "Select * " _
& "FROM BillHdr in '" & DataMasPath & "'" _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'" _
& " and ActiveStatusFlag = '1'"
strSQL3 = "Delete from " _
& "BillDet " _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'"
strSQL4 = "Select * from " _
& "BillHdr " _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) "'"
strSQL = "INSERT INTO " _
& "BillDetHist " _
& "Select * FROM BillDet in '" & DataMasPath & "'" _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'"
Set rsBillHdr = RunSQLReturnRSforUpd(strSQ L4, , cnnPhoneData)
cnnPhoneData.BeginTrans
cnnPhoneTmpData.BeginTrans
'Insert into BillHdrHist
Call RunSQL1(strSQL1, cnnPhoneData)
'Insert into BilldetHist
Call RunSQL1(strSQL, cnnPhoneData)
'Del BillDet
Call RunSQL1(strSQL3, cnnPhoneData)
'insert record into BillDet table
'from tmpbilldetaildata
Call UpdBillDetData
Call UpdBillHdrData(rsBillHdr)
cnnPhoneTmpData.RollbackTr ans
cnnPhoneData.RollbackTrans
'simulate the rollback trx
'cnnPhoneData.CommitTrans
Set rsBillHdr = Nothing
Exit Sub
End Sub
Sub RunSQL1(ByVal strSQL As String, Optional ByVal strConn As String)
On Error GoTo Err_RunSQL
' Create the ADO objects
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = strConn
With cmd
.CommandText = strSQL
.CommandType = adCmdText
End With
' Execute the query without returning a recordset
cmd.Execute , , adExecuteNoRecords
' Cleanup
cmd.ActiveConnection = Nothing
Set cmd = Nothing
Exit Sub
End Sub
The following is my vb code using access database. I try to simulate the rollback action but not all the transaction is fully rollbacked. Only transaction in UpdBillDetData and UpdBillHdrData can be rollbacked. The transaction in RunSQL1(strSQL1, cnnPhoneData),RunSQL1(strS
Sub EditRec()
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim SStr As String
Dim cmd As ADODB.Command
Dim TmpTbl As String
Dim rsTmptbl As ADODB.Recordset
Dim rsBillHdr As ADODB.Recordset
Set cmd = New ADODB.Command
strSQL = "INSERT INTO " _
& "BillDetHist " _
& "Select * " _
& "FROM BillDet in '" & DataMasPath & "'" _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'"
strSQL1 = "INSERT INTO " _
& "BillHdrHist " _
& "Select * " _
& "FROM BillHdr in '" & DataMasPath & "'" _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'" _
& " and ActiveStatusFlag = '1'"
strSQL3 = "Delete from " _
& "BillDet " _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'"
strSQL4 = "Select * from " _
& "BillHdr " _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) "'"
strSQL = "INSERT INTO " _
& "BillDetHist " _
& "Select * FROM BillDet in '" & DataMasPath & "'" _
& " where " _
& " BillNbr = '" & Trim(BillNbrTxt.Text) & "'"
Set rsBillHdr = RunSQLReturnRSforUpd(strSQ
cnnPhoneData.BeginTrans
cnnPhoneTmpData.BeginTrans
'Insert into BillHdrHist
Call RunSQL1(strSQL1, cnnPhoneData)
'Insert into BilldetHist
Call RunSQL1(strSQL, cnnPhoneData)
'Del BillDet
Call RunSQL1(strSQL3, cnnPhoneData)
'insert record into BillDet table
'from tmpbilldetaildata
Call UpdBillDetData
Call UpdBillHdrData(rsBillHdr)
cnnPhoneTmpData.RollbackTr
cnnPhoneData.RollbackTrans
'simulate the rollback trx
'cnnPhoneData.CommitTrans
Set rsBillHdr = Nothing
Exit Sub
End Sub
Sub RunSQL1(ByVal strSQL As String, Optional ByVal strConn As String)
On Error GoTo Err_RunSQL
' Create the ADO objects
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = strConn
With cmd
.CommandText = strSQL
.CommandType = adCmdText
End With
' Execute the query without returning a recordset
cmd.Execute , , adExecuteNoRecords
' Cleanup
cmd.ActiveConnection = Nothing
Set cmd = Nothing
Exit Sub
End Sub
maybe because you are using a different connection in RunSQL1 procedure.
use either one of the following connections....
cnnPhoneTmpData
cnnPhoneData
can i take a look at your UpdBilLDetData procedure?
use either one of the following connections....
cnnPhoneTmpData
cnnPhoneData
can i take a look at your UpdBilLDetData procedure?
ASKER
Actually, i need both connection,cnnPhoneData and cnnPhoneTmpData. These 2 connection connected to 2 diferrent database. During this action, both database will be updated if succesfull or else need to rollback.
The following is as per your request
Sub UpdBillDetData()
'TmpBillDetailData is connected to cnnPhoneTmpData
'rsBillDet is connected to cnnPhoneData
TmpBillDetailData.Recordse t.MoveFirs t
Do While Not TmpBillDetailData.Recordse t.EOF
rsBillDet.AddNew
rsBillDet("BillNbr") = BillNbrTxt.Text
rsBillDet("PONbr") = PONbrTxt.Text
rsBillDet("ItemNbr") = TmpBillDetailData.Recordse t("ItemNbr ")
rsBillDet("StockId") = TmpBillDetailData.Recordse t("StockId ")
rsBillDet("SerialNbr") = TmpBillDetailData.Recordse t("SerialN br")
rsBillDet("Qty") = TmpBillDetailData.Recordse t("Qty")
rsBillDet("UnitPrice") = TmpBillDetailData.Recordse t("UnitPri ce")
rsBillDet("AmountAfterTax" ) = TmpBillDetailData.Recordse t("AmountA fterTax")
rsBillDet("Description") = TmpBillDetailData.Recordse t("Descrip tion")
TmpBillDetailData.Recordse t.MoveNext
rsBillDet.Update
Loop
TmpBillDetailData.Refresh
DataGrid1.ReBind
DataGrid1.Refresh
End Sub
The following is as per your request
Sub UpdBillDetData()
'TmpBillDetailData is connected to cnnPhoneTmpData
'rsBillDet is connected to cnnPhoneData
TmpBillDetailData.Recordse
Do While Not TmpBillDetailData.Recordse
rsBillDet.AddNew
rsBillDet("BillNbr") = BillNbrTxt.Text
rsBillDet("PONbr") = PONbrTxt.Text
rsBillDet("ItemNbr") = TmpBillDetailData.Recordse
rsBillDet("StockId") = TmpBillDetailData.Recordse
rsBillDet("SerialNbr") = TmpBillDetailData.Recordse
rsBillDet("Qty") = TmpBillDetailData.Recordse
rsBillDet("UnitPrice") = TmpBillDetailData.Recordse
rsBillDet("AmountAfterTax"
rsBillDet("Description") = TmpBillDetailData.Recordse
TmpBillDetailData.Recordse
rsBillDet.Update
Loop
TmpBillDetailData.Refresh
DataGrid1.ReBind
DataGrid1.Refresh
End Sub
Is this an MTS object? In that case objContext.SetAbort should do the required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent answer!!!!Thank you very much!I should post the question earlier so that i do not need crack my head for so long.Thanks again!
ASKER
excellent answer!!!!Thank you very much!I should post the question earlier so that i do not need crack my head for so long.Thanks again!
use either one of the following connections....
cnnPhoneTmpData
cnnPhoneData
can i take a look at your UpdBilLDetData procedure?