Home | I.T. Management | Business Analysis & Project Management | VB.Net | SQL Server | About |

Database Transactions

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.


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

'Start Database Transaction on Database Connection
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)"

'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
bConnectionTransactionInProgress = False
ElseIf iRecordsUpdated <> 1 Then
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 = Nothing
iReturnValue = 1
mAddOrder = iReturnValue

Exit Function
'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
End If
oConnection = Nothing
End If
mAddOrder = 0
Msgbox(Err.Number & " - " & Err.Source & " - " & Err.Description)
End Function

Copyright © Devon Manelski