ADO - What is RollbackTrans?

What is RollbackTrans?

The RollbackTrans method of the Connection object is used to undo all changes made during the current transaction.

  • It is the counterpart of CommitTrans.

  • Any operations executed after BeginTrans are reverted, restoring the database to its previous state.

  • Essential for maintaining data integrity when errors occur.

Syntax

Connection.RollbackTrans

  • No arguments are needed.

  • Rolls back all operations since the most recent BeginTrans.

Example: Using RollbackTrans

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 a transaction
conn.BeginTrans

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

' Simulate error
If False Then conn.Execute "INVALID SQL COMMAND"

' Commit if everything succeeds
conn.CommitTrans
Response.Write "Transaction committed successfully."

ExitHere:
conn.Close
Set conn = Nothing
Exit Sub

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

Key Points

  1. Must follow BeginTrans — calling RollbackTrans without a transaction may throw an error.

  2. Reverts all operations executed after BeginTrans, keeping the database consistent.

  3. Use with error handling (On Error) to catch exceptions during a transaction.

  4. Supports nested transactions depending on the provider: each rollback undoes operations in the current transaction level.

  5. Always combine with CommitTrans and proper error handling for safe database operations.

In short:

  • RollbackTrans cancels all uncommitted changes in a transaction.

  • It ensures that if something goes wrong, your database remains consistent and no partial updates occur.