ADO - What is BeginTrans?

What is BeginTrans?

The BeginTrans method of the Connection object is used to start a database transaction.

A transaction is a sequence of operations (like INSERT, UPDATE, DELETE) that are executed as a single unit.

  • Either all operations succeed (commit), or

  • All operations fail (rollback), ensuring data integrity.

Why use transactions?

  • To prevent partial updates to the database.

  • To maintain consistency during complex operations (e.g., transferring money between accounts).

  • To handle multiple related changes safely.

Syntax

Connection.BeginTrans

  • Starts a transaction on the current connection.

  • After BeginTrans, any changes made are pending until you either commit or roll back.

Related Methods

  • CommitTrans → Saves all changes made during the transaction.

  • RollbackTrans → Reverts all changes made during the transaction.

Example: Using BeginTrans with Commit/Rollback

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User Id=sa;Password=pass;"

On Error GoTo TransactionError

' Start the transaction
conn.BeginTrans

' Execute multiple commands
conn.Execute "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1"
conn.Execute "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2"

' Commit transaction if all commands succeed
conn.CommitTrans
Response.Write "Transaction completed successfully."

ExitHere:
conn.Close
Set conn = Nothing
Exit Sub

TransactionError:
' Rollback if any error occurs
conn.RollbackTrans
Response.Write "Transaction failed. All changes rolled back."
Resume ExitHere

Key Points

  1. Transaction applies only to the connection where BeginTrans was called.

  2. Use CommitTrans to save changes, RollbackTrans to undo changes.

  3. Always pair BeginTrans with proper error handling.

  4. Multiple BeginTrans calls are nested transactions, depending on provider support.

In short:

  • BeginTrans starts a safe “transaction block” where database changes can be committed or rolled back as a single unit.

  • It’s essential for data integrity in multi-step operations.