Transactional Database Update

<%@ TRANSACTION=Required LANGUAGE="VBScript" %> Transactional Database Update Transactional Database Update

This is a simple example demonstrating how to transactionally update a SQL 6.5 database using ADO and Transacted ASP. The example will obtain information regarding a book sale from the SQL 6.5 "Pubs" database. It will then increment the quantity of books sold by one, as well as change the zip-code of the store in which the book was sold.

Because the two database operations are wrapped within a shared ASP Transaction, both will be automatically rolled back to their previous state in the event of a failure. <% Dim oConn ' object for ADODB.Connection obj Dim oRs ' object for recordset object Dim oRs2 ' object for recordset object ' Create Connection and Recordset components Set oConn = Server.CreateObject("ADODB.Connection") Set oRs = Server.CreateObject("ADODB.Recordset") Set oRs2 = Server.CreateObject("ADODB.Recordset") ' Open ADO Connection using account "sa" ' and blank password oConn.Open "DSN=LocalServer;UID=sa;PWD=;DATABASE=pubs" Set oRs.ActiveConnection = oConn Set oRs2.ActiveConnection = oConn ' Find a random book sale oRs.Source = "SELECT * FROM sales" oRs.CursorType = adOpenStatic ' use a cursor other than Forward Only oRs.LockType = adLockOptimistic ' use a locktype permitting insertions oRs.Open ' Change quantity sold If (Not oRs.EOF) Then oRs("qty").Value = oRs("qty").Value + 1 oRs.Update End If ' Find the store in which the book was sold oRs2.Source = "SELECT * FROM stores where stor_id='" & oRs("stor_id") & "'" oRs2.CursorType = adOpenStatic ' use a cursor other than Forward Only oRs2.LockType = adLockOptimistic ' use a locktype permitting insertions oRs2.Open ' Change zip code If (Not oRs2.EOF) Then oRs2("Zip").Value = oRs2("Zip").Value + 1 oRs2.Update End If %> <% ' The Transacted Script Commit Handler. This sub-routine ' will be called if the transacted script commits. Sub OnTransactionCommit() Response.Write "

The update was successful." End sub ' The Transacted Script Abort Handler. This sub-routine ' will be called if the script transacted aborts Sub OnTransactionAbort() Response.Write "

The update was not successful." end sub %>