Database Transactions
Summary
When writing a software application, it is sometimes necessary to execute several SQL Statements that either succeed together or fail together. Database Transactions are the most straightforward way to solve this common programming challenge.
Example
Public Function mAddOrder() As Integer
On Error GoTo Errorhandler
'------------------------------------------------
'Declare Variables
'------------------------------------------------
Dim oConnection As ADODB.Connection
Dim iTotalOrderAmount As Integer
Dim iRecordsUpdated As Integer
Dim iReturnValue As Integer
Dim sConnectionString As String
Dim sSQL As String
Dim bConnectionTransactionInProgress As Boolean
'------------------------------------------------
'Initialize Variables
'------------------------------------------------
oConnection = New ADODB.Connection
sConnectionString = "[Connection String Value]"
iTotalOrderAmount = 10000
iReturnValue = 0
'------------------------------------------------
'Open Database Connection
'------------------------------------------------
oConnection.Open(sConnectionString)
'Start Database Transaction on Database Connection
'----------------------------------------------------------------------------------------
oConnection.BeginTrans
bConnectionTransactionInProgress = True
'Insert An Order Record into the Orders Table
'------------------------------------------------------------------------------------------------
sSQL = "INSERT INTO tb_Order (Customer_Company_Name, Customer_Order_Count, Customer_Total_Order_Amount) VALUES ('Sample Company', 1, 500)"
oConnection.Execute(sSQL)
'Update the Customer Table with the Total Amount of the Orders Made
'------------------------------------------------------------------------------------------------
sSQL = "UPDATE tb_Customer SET Total_Order_Amount = " & (iTotalOrderAmount + 500)
sSQL = sSQL & " WHERE Customer_Name = 'Sample Company'"
oConnection.Execute(sSQL, iRecordsUpdated)
'This application searches by customer name for the customer record. If 1 customer record is going to be updated, commit both transactions to the database. If more or less than 1 customer record is going to be updated, rollback both transactions and alert the user to the error.
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If iRecordsUpdated = 1 Then
oConnection.CommitTrans
bConnectionTransactionInProgress = False
ElseIf iRecordsUpdated <> 1 Then
oConnection.RollbackTrans
bConnectionTransactionInProgress = False
Msgbox("mAddOrder: Customer data error in the tb_Customer table. Number of matching customer records is not equal to 1.")
End If
'Close the Database Connection and Set the oConnection Object to Nothing
'--------------------------------------------------------------------------------------------
oConnection.Close
oConnection = Nothing
iReturnValue = 1
mAddOrder = iReturnValue
Exit Function
Errorhandler:
'If an error occurs during the mAddOrder Function and the oConnection Object has been created, rollback the transaction and set the oConnection Object to Nothing.
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If IsNothing(oConnection) = False Then
If bConnectionTransactionInProgress = True Then
oConnection.RollbackTrans
End If
oConnection = Nothing
End If
mAddOrder = 0
Msgbox(Err.Number & " - " & Err.Source & " - " & Err.Description)
End Function
Copyright © Devon Manelski