Transactions Using ADO from ASP | Programming Microsoft Visual InterDev 6.0

In this section, we'll see how an ASP Web page can participate in a true database transaction. To start simply, we'll only use ActiveX Data Objects (ADO) to access the data—we won't employ MTS components. The ASP code will therefore go directly against the database. The same functionality in the code could also be written by using the Data Environment and data command objects. In this particular example, we'll simply use straight ADO so that you can more easily see the code in the listing and thus focus on the transactional nature of the page.

Figure 19-7 shows the code for this example. The code is included on the companion CD-ROM as the ado_trans.asp file within the VI-Bank/VIntDev98 folder. The example selects information from the customer table in the Microsoft SQL Server 6.5 "Bank" database, which is part of the VI-Bank sample Internet Banking application. It then updates the customer's e-mail address in the customer table as well as the customer's checking account in the account_balance table by a debit of $0.50 for this service. Of course, in real life you might not want to charge anything for this service or your customers might go elsewhere! There are two phases to this operation. Since they are both contained within this transactional ASP Web page, they must both succeed or the transaction will be rolled back.

Figure 19-7. Sample code showing how to transactionally update a SQL Server 6.5 database using ADO and transactional ASP.

<%@ TRANSACTION=Required LANGUAGE="VBScript" %> <!--#include file="adovbs.inc"--> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <TITLE>Transactional Database Update</TITLE> </HEAD> <BODY BGCOLOR="White" topmargin="10" leftmargin="10"> <h2><font color="navy">Transactional Database Update</font></h2> <hr> This sample code shows how to transactionally update a SQL 6.5  database using ADO and Transacted ASP. The example SELECTS information  from the CUSTOMER table in the SQL 6.5 "Bank" database which is part of the VI-BANK sample Internet Banking application.  It then UPDATES the customer's e-mail address in the CUSTOMER  table, as well as UPDATING the customer's checking account in the  ACCOUNT_BALANCE table by a debit of $.50 for this service.  <p> 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 the VI-Bank file DSN oConn.Open "FILEDSN=VI-Bank" Set oRs.ActiveConnection = oConn Set oRs2.ActiveConnection = oConn ' Get the customer record based on the ssn oRs.Source = "SELECT * FROM customer WHERE ssn = 123456789" oRs.CursorType = adOpenStatic ' use a cursor other than Forward Only oRs.LockType = adLockOptimistic ' use a locktype permitting insertions oRs.Open ' Change e-mail address If (Not oRs.EOF) Then     oRs("e_mail").Value = "nick.evans@us.pwcglobal.com"     oRs.Update End If                  ' Find the customer's account balance information oRs2.Source = "SELECT * FROM account_balance " & _     "where ssn = 123456789 and account_id = 1" oRs2.CursorType = adOpenStatic   ' use a cursor other than                                   ' Forward Only oRs2.LockType = adLockOptimistic ' use a locktype                                   ' permitting insertions oRs2.Open ' Debit the checking account balance by $0.50 If (Not oRs2.EOF) Then     oRs2("balance").Value = _         CDbl(oRs2("balance").Value) - CDbl(0.5)     NewBalance = oRs2("balance").Value     oRs2.Update End If ' Clean up oRs.Close oRs2.Close oConn.Close %> </BODY> </HTML> <% ' The Transacted Script Commit Handler. This subroutine ' will be called if the transacted script commits.      Sub OnTransactionCommit()     Response.Write "<p><table border=1 width=350><tr><td>"     Response.Write "<b>New account balance is " + _         FormatCurrency(NewBalance)     Response.Write "<p>The update was successful.</b>"      Response.Write "</td></tr></table>" End Sub ' The Transacted Script Abort Handler. This subroutine ' will be called if the transacted script aborts.   Sub OnTransactionAbort()     Response.Write "<p><b>The update was not successful</b>."  End Sub %>

Once again we start the ASP script with the @TRANSACTION directive. The script then proceeds to create one Connection object and two Recordset objects. It then uses a SELECT statement to obtain profile information from the customer table. The specific customer social security number is hard-coded in this example. Using the first Recordset object, the code changes the e-mail address for the customer and performs an update. Then, using the second Recordset object, the code uses a SELECT statement to obtain information about the customer's checking balance from the account_balance table. Finally, the customer's checking account balance is debited by $0.50 using the second Recordset object.

At the end of the ASP script, the event handlers are used to write a message to the browser to indicate whether the update was successful. If either the first or the second update failed (or both), the entire transaction is aborted automatically by MTS. This relieves the developer from having to write code to handle these scenarios. Because all the database operations have been placed into a single ASP Web page, they are made part of a single transaction. If both updates are successful, the transaction commits automatically when the script finishes execution. Figure 19-8 shows the resulting browser output after running the code.

click to view at full size.

Figure 19-8. Resulting browser output after running the code shown in Figure 19-7. This ASP Web page participates in a transaction using ADO against a SQL Server 6.5 database.