Sun Chili!Soft ASP Sun Chili!Soft
ASP Sun Microsystems

 

ADO Connection Object BeginTrans, CommitTrans, and RollbackTrans Methods

The transaction methods manage transaction processing within a Connection object.

These transaction methods are summarized as follows:

Method

Description

BeginTrans

Begins a new transaction

CommitTrans

Saves any changes and ends the current transaction. It may also start a new transaction.

RollbackTrans

Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.

BeginTrans, CommitTrans, and RollbackTrans Methods Syntax

level = connection.BeginTrans()

connection.BeginTrans

connection.CommitTrans

connection.RollbackTrans

BeginTrans, CommitTrans, and RollbackTrans Methods Remarks

Use these methods with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit. For example, to transfer money between accounts, you subtract an amount from one and add the same amount to the other. If either update fails, the accounts no longer balance. Making these changes within an open transaction ensures either all or none of the changes goes through.

Not all providers support transactions. Check that the provider-defined property "Transaction DDL" appears in the Connection object's ADO Properties Collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.

Once you call the BeginTrans method, the provider will no longer instantaneously commit any changes you make until you call CommitTrans or RollbackTrans to end the transaction.

For providers that support nested transactions, calling the BeginTrans method within an open transaction starts a new, nested transaction. The return value indicates the level of nesting: a return value of "1" indicates you have opened a top-level transaction (that is, the transaction is not nested within another transaction), "2" indicates that you have opened a second-level transaction (a transaction nested within a top-level transaction), and so forth. Calling CommitTrans or RollbackTrans affects only the most recently opened transaction; you must close or rollback the current transaction before you can resolve any higher-level transactions.

Calling the CommitTrans method saves changes made within an open transaction on the connection and ends the transaction. Calling the RollbackTrans method reverses any changes made within an open transaction and ends the transaction. Calling either method when there is no open transaction generates an error.

Depending on the Connection object's ADO Connection Object Attributes Property, calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction. If the Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call. If the Attributes property is set to adXactAbortRetaining, the provider automatically starts a new transaction after a RollbackTrans call.

BeginTrans, CommitTrans, and RollbackTrans Methods Return Value

BeginTrans can be called as a function that returns a Long variable indicating the nesting level of the transaction.

BeginTrans, CommitTrans, and RollbackTrans Methods Examples

This Visual Basic example changes the book type of all psychology books in the Titles table of the database. After the BeginTrans method starts a transaction that isolates all the changes made to the Titles table, the CommitTrans method saves the changes. Notice that you can use the RollbackTrans method to undo changes that you saved using the ADO Recordset Object Update Method.

Public Sub BeginTransX()

Dim cnn1 As ADODB.Connection

Dim rstTitles As ADODB.Recordset

Dim strCnn As String

Dim strTitle As String

Dim strMessage As String

` Open connection.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

` Open titles table.

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = adOpenDynamic

rstTitles.LockType = adLockPessimistic

rstTitles.Open "titles", cnn1, , , adCmdTable

rstTitles.MoveFirst

cnn1.BeginTrans

` Loop through recordset and ask user if she wants

` to change the type for a specified title.

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "psychology" Then

strTitle = rstTitles!Title

strMessage = "Title: " & strTitle & vbCr & _

"Change type to self help?"

` Change the title for the specified employee.

If MsgBox(strMessage, vbYesNo) = vbYes Then

rstTitles!Type = "self_help"

rstTitles.Update

End If

End If

rstTitles.MoveNext

Loop

` Ask if the user wants to commit to all the

` changes made above.

If MsgBox("Save all changes?", vbYesNo) = vbYes Then

cnn1.CommitTrans

Else

cnn1.RollbackTrans

End If

` Print current data in recordset.

rstTitles.Requery

rstTitles.MoveFirst

Do While Not rstTitles.EOF

Debug.Print rstTitles!Title & " - " & rstTitles!Type

rstTitles.MoveNext

Loop

' Restore original data

rstTitles.MoveFirst

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "self_help" Then

rstTitles!Type = "psychology"

rstTitles.Update

End If

rstTitles.MoveNext

Loop

rstTitles.Close

cnn1.Close

End Sub

Copyright 2002 Sun Microsystems, Inc. All rights reserved. Legal Notice.