ADO - CommitTrans

What is CommitTrans?

The CommitTrans method of the Connection object is used to save all changes made during a transaction.

  • It is always called after BeginTrans once all database operations are complete and successful.

  • Once committed, the changes become permanent in the database.

Syntax

Connection.CommitTrans

  • No arguments are needed.

  • It affects all operations executed after the most recent BeginTrans.

How it Works

  1. You call BeginTrans to start a transaction.

  2. Execute one or more database commands (INSERT, UPDATE, DELETE).

  3. If all commands succeed, call CommitTrans → all changes are permanently written.

  4. If any command fails, call RollbackTrans → all changes are undone.

Example: Using CommitTrans

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 the transaction if all commands succeed
conn.CommitTrans
Response.Write "Transaction completed and committed successfully."

ExitHere:
conn.Close
Set conn = Nothing
Exit Sub

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

Key Points

  1. CommitTrans only works after BeginTrans — calling it without starting a transaction may cause an error.

  2. Once committed, changes are permanent — they cannot be rolled back.

  3. Always pair with error handling: if something goes wrong, use RollbackTrans.

  4. Multiple BeginTrans calls can create nested transactions; CommitTrans commits the current level.

In short:

  • CommitTrans finalizes all operations in the transaction and makes them permanent.

  • Use it after successful execution of all commands in a transaction block.