<%@ TRANSACTION = Required LANGUAGE = "VBScript" %> <% Option Explicit %> 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 price from the SQL 6.5 "Pubs" database. It will mark up 10% of the first book price and mark donw 10% of the second book price.

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 objConn ' object for ADODB.Connection obj Dim objRs ' object for recordset object Dim strTitleID ' Create Connection and Recordset components. Set objConn = Server.CreateObject("ADODB.Connection") Set objRs = Server.CreateObject("ADODB.Recordset") 'Connection string is OLEDB type instead of ODBC ' Open ADO Connection using account "sa" ' and blank password. objConn.Open "Provider=SQLOLEDB;User ID=sa;Initial Catalog=pubs;Data Source="& Request.ServerVariables("SERVER_NAME") Set objRs.ActiveConnection = objConn Set objRs2.ActiveConnection = objConn ' query title id objRs.Source = "SELECT title_id FROM Titles" objRs.CursorType = adOpenForwardOnly ' use a cursor Forward Only. objRs.LockType = adLockReadOnly ' use a locktype ReadOnly. objRs.Open 'Get first title, mark up price by 10% If (Not objRs.EOF) Then strTitleID=objRs("title_id").Value objConn.Execute "Update titles Set price =price+ price*(0.1) where title_id = "& strTitleID End If 'Get first title, mark up price by 10% objRs.MoveNext If (Not objRs.EOF) Then strTitleID=objRs("title_id").Value objConn.Execute "Update titles Set price =price- price*(0.1) where title_id = "& strTitleID 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 %>