没那么麻烦的,用代码吧,你参考一下
cnall是全局连接
cnAll.BeginTrans
'保存主表
cnAll.Execute "INSERT INTO CPRKD_MAIN VALUES('" & txtDH.Text & "','" & txtBM.ToolTipText & "','" & txtBM.Text & "','" & txtCK.ToolTipText & "','" & txtCK.Text & "','" & txtRQ.Text & "','" & CDate(txtTime.Text) & "','" & txtZhiDan.Text & "','" & txtJINGBAN.Text & "','" & txtShenHe.Text & "'," & txtZSL.Text & "," & IIf(lblYZUOFEI.Visible, 1, 0) & "," & IIf(lblYSHENHE.Visible, 1, 0) & ")"
'保存细表
For I = 1 To MyRows
With VS7
cnAll.Execute "INSERT INTO CPRKD_DETAIL VALUES('" & txtDH.Text & "','" & .TextMatrix(I, 2) & "-" & .TextMatrix(I, 3) & "-" & .TextMatrix(I, 4) & "','" & .TextMatrix(I, 1) & "','" & .TextMatrix(I, 2) & "','" & .TextMatrix(I, 3) & "','" & .TextMatrix(I, 4) & "','" & .TextMatrix(I, 5) & "'," & .TextMatrix(I, 6) & ",'" & .TextMatrix(I, 7) & "')"
End With
Next I
'调整库存
For I = 1 To MyRows
With VS7
cnAll.Execute "UPDATE CP SET XCL=XCL+" & .TextMatrix(I, 6) & " WHERE CODE='" & .TextMatrix(I, 2) & "-" & .TextMatrix(I, 3) & "-" & .TextMatrix(I, 4) & "'"
End With
Next I
If cnAll.Errors.Count > 0 Then
cnAll.RollbackTrans
MsgBox "保存操作不成功,可能是其他操作锁定了相关数据" & vbCrLf & "请稍侯再试一次,如果仍不成功,请联系管理员!", vbOKOnly, "提醒"
cnAll.Errors.Clear
Exit Sub
End If
cnAll.CommitTrans
cnall是全局连接
cnAll.BeginTrans
'保存主表
cnAll.Execute "INSERT INTO CPRKD_MAIN VALUES('" & txtDH.Text & "','" & txtBM.ToolTipText & "','" & txtBM.Text & "','" & txtCK.ToolTipText & "','" & txtCK.Text & "','" & txtRQ.Text & "','" & CDate(txtTime.Text) & "','" & txtZhiDan.Text & "','" & txtJINGBAN.Text & "','" & txtShenHe.Text & "'," & txtZSL.Text & "," & IIf(lblYZUOFEI.Visible, 1, 0) & "," & IIf(lblYSHENHE.Visible, 1, 0) & ")"
'保存细表
For I = 1 To MyRows
With VS7
cnAll.Execute "INSERT INTO CPRKD_DETAIL VALUES('" & txtDH.Text & "','" & .TextMatrix(I, 2) & "-" & .TextMatrix(I, 3) & "-" & .TextMatrix(I, 4) & "','" & .TextMatrix(I, 1) & "','" & .TextMatrix(I, 2) & "','" & .TextMatrix(I, 3) & "','" & .TextMatrix(I, 4) & "','" & .TextMatrix(I, 5) & "'," & .TextMatrix(I, 6) & ",'" & .TextMatrix(I, 7) & "')"
End With
Next I
'调整库存
For I = 1 To MyRows
With VS7
cnAll.Execute "UPDATE CP SET XCL=XCL+" & .TextMatrix(I, 6) & " WHERE CODE='" & .TextMatrix(I, 2) & "-" & .TextMatrix(I, 3) & "-" & .TextMatrix(I, 4) & "'"
End With
Next I
If cnAll.Errors.Count > 0 Then
cnAll.RollbackTrans
MsgBox "保存操作不成功,可能是其他操作锁定了相关数据" & vbCrLf & "请稍侯再试一次,如果仍不成功,请联系管理员!", vbOKOnly, "提醒"
cnAll.Errors.Clear
Exit Sub
End If
cnAll.CommitTrans
在是事务处理开始之前,要首先调用BeginTrans.
千万注意,所有的分支别遗漏执行commit/rollback.特别是错误处理。
要不,很容易发生死锁。
ADO supports transaction management in Microsoft® SQL Server™ 2000, allowing an application to perform explicitly and implicitly started transactions on a single connection to an instance of SQL Server. After the connection is established, a recordset is opened on the result set of a select query, using a dynamic cursor and pessimistic locking (properties of a Recordset object). After you edit or update the data, you select whether to commit the changes or cancel them. The data changed in the transaction can then be committed or rolled back.To perform an explicit transaction in an application Open a new connection to an instance of SQL Server.
Retrieve a recordset from an instance of SQL Server.
Call the BeginTrans method of the Connection object to begin the transaction.
Make changes to the recordset.
Call the CommitTrans method of the Connection object to save changes to the recordset
OrCall the RollbackTrans method of the Connection object to discard changes to the recordset.Managing a Transaction
This example shows how to use the ADO transaction methods BeginTrans, CommitTrans, and RollbackTrans to manage a transaction. Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset. . .
' Open connection.
cn.Open' Open titles table.
rs.Open "SELECT * FROM titles", Cn, adOpenDynamic, adLockPessimistic
. . .
' Begin the transaction.
rs.MoveFirst
cn.BeginTrans' User loops through the recordset making changes.
. . .
' Ask if the user wants to commit all the changes made.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
cn.CommitTrans
Else
cn.RollbackTrans
End If