Throughout this chapter, we've seen several short examples of how to create transactional components and how to call them from within ASP Web pages. In this section, we'll consolidate what we've learned in this and earlier chapters by taking a look at an example that combines all of these techniques in a practical and fully functioning application.
We'll again use the VI-Bank sample application, which is located on the CD-ROM under the VI-Bank folder. Remember that the Visual Basic files are located under the VI-Bank/VB98 folder and the Visual InterDev files are located under the VI-Bank/VIntDev98 folder. The necessary Microsoft SQL Server 6.5 database for this example is also on the CD-ROM under the VI-Bank/SQL folder. Complete installation instructions are included on the CD-ROM.
The example that we'll look at is the part of the VI-Bank application that allows for the transfer of funds. A customer at the bank is able to transfer funds between any of his or her existing accounts. Figure 20-11 shows the initial screen.
Figure 20-11. The Transfer Funds page of the VI-Bank sample application.
There are two parts to this application:
The source code for the transfer.cls file appears below. This file is part of the VI-Bank.vbp project and has a method named Transfer that is called from within the ASP code.
' Filename: VI-Bank.vbp (Transfer.cls) ' ' Description: Transfer Class ' ' This file is provided as part of the "Programming Visual ' InterDev 6.0" CD-ROM. ' ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT ' WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, ' INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES ' OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR ' PURPOSE. ' ' Copyright (C) 1998 Microsoft Corporation. All rights reserved Option Explicit Private Const ERROR_NUMBER = vbObjectError + 0 Private Const APP_ERROR = -2147467008 Public Function Transfer(ByVal lngAmount As Long, _ ByVal lngAccountTo As Long, ByVal lngAccountFrom As Long) _ As String Dim strResult As String On Error GoTo ErrorHandler ' Check for security. If (lngAmount > 500 Or lngAmount < -500) Then If Not GetObjectContext.IsCallerInRole("Administrator") Then Err.Raise Number:=APP_ERROR, Description:= _ "Need 'Administrator' role for amounts over $500" End If End If ' Create the account object using our context. Dim objAccount As VIBank.Account Set objAccount = GetObjectContext.CreateInstance("VIBank.Account") If objAccount Is Nothing Then Err.Raise ERROR_NUMBER, _ Description:="Could not create account object" End If ' Call the Transaction function. Dim strCredit As String, strDebit As String ' Apply the credit to the "To" account. strCredit = objAccount.Transaction(lngAccountTo, lngAmount) If strCredit = "" Then Err.Raise ERROR_NUMBER, Description:=strCredit Else ' Apply the debit to the "From" account. strDebit = objAccount.Transaction(lngAccountFrom, _ 0 - lngAmount) If strDebit = "" Then Err.Raise ERROR_NUMBER, _ Description:=strDebit ' Debit failed Else strResult = strCredit + " " + strDebit End If End If GetObjectContext.SetComplete ' Return the results of the transfer. Transfer = strResult Exit Function ErrorHandler: GetObjectContext.SetAbort Transfer = "" ' Indicate that an error occurred. Err.Raise Err.Number, "VIBank.Transfer.Transfer", _ Err.Description End Function
You can see from the above code that the Transfer method is passed the amount for the transfer and the relevant account numbers to credit and debit. The first thing the code does is ensure that the caller of the method is in the correct role. Only callers in the Administrator role are allowed to perform transfers of more than $500. This code is a great way to secure your server-side components and to make sure that only authorized callers can execute their methods. You'll remember that the roles can be set up from within the Transaction Server Explorer.
The next step for the code is to create an instance of the Account object. This object is used to perform the actual database access, as we shall see later in the chapter. After instantiating the Account object, the code calls its Transaction method twice: once for the credit and once for the debit. If all is successful, the code calls the SetComplete method to commit the transaction and returns a string to the calling application.
The source code for the account.cls file begins beneath the note below. This class contains the Transaction function. You'll notice within the code that the File DSN, VI-Bank, of the SQL Server database is set in the strConnect constant. The Transaction function takes an account number and a dollar amount as input. It again checks to see that the caller is in the appropriate role by using the IsCallerInRole method of the context object.
NOTE
If you want to make your code a little more flexible, you should externalize the role names so they are not hard-coded in your components. One way to externalize the role names is to place them in the database and have your code perform a query. This way you can make changes to the security of your components without having to rewrite and recompile your code.
' Filename: Account.cls (VI-Bank.vbp) ' ' Description: Account Class ' ' This file is provided as part of the "Programming Visual ' InterDev 6.0" CD-ROM. ' ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT ' WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, ' INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES ' OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR ' PURPOSE. ' ' Copyright (C) 1998 Microsoft Corporation. All rights reserved Option Explicit Private Const ERROR_NUMBER = vbObjectError + 0 Private Const APP_ERROR = -2147467008 Private Const strConnect = "FILEDSN=VI-Bank" Public Function Transaction(ByVal lngAccountNo As Long, _ ByVal lngAmount As Long) As String Dim strResult As String On Error GoTo ErrorHandler ' Check for security. If (lngAmount > 500 Or lngAmount < -500) Then If Not GetObjectContext.IsCallerInRole("Administrator") Then Err.Raise Number:=APP_ERROR, Description:= _ "Need 'Administrator' role for amounts over $500" End If End If ' Obtain the ADO environment and connection. Dim adoConn As New ADODB.Connection Dim varRows As Variant adoConn.Open strConnect ' Update the Account_Balance table given the Account Number. Dim strSQL As String strSQL = "UPDATE Account_Balance SET Balance = Balance + " _ + Str$(lngAmount) + " WHERE Account_No = " + _ Str$(lngAccountNo) TryAgain: adoConn.Execute strSQL, varRows ' If anything else happens, On Error GoTo ErrorHandler ' Get resulting balance. strSQL = "SELECT Balance FROM Account_Balance WHERE " + _ "Account_No = " + Str$(lngAccountNo) Dim adoRS As ADODB.Recordset Set adoRS = adoConn.Execute(strSQL) If adoRS.EOF Then Err.Raise Number:=APP_ERROR, Description:= _ "Error. Account " + Str$(lngAccountNo) + _ " not on file." End If Dim lngBalance As Long lngBalance = adoRS.Fields("Balance").Value ' Check if account is overdrawn. If (lngBalance) < 0 Then Err.Raise Number:=APP_ERROR, Description:= _ "Error. Account " + Str$(lngAccountNo) + _ " would be overdrawn by " + _ Str$(lngBalance) + ". Balance is still " + _ Str$(lngBalance - lngAmount) + "." Else If lngAmount < 0 Then strResult = strResult & "Debit from account " & _ lngAccountNo & ", " Else strResult = strResult & "Credit to account " & _ lngAccountNo & ", " End If ' Now insert the transaction into the Account_Transaction ' table. Use the strResult variable as the entry for the ' transaction description. strSQL = "INSERT INTO Account_Transaction " + _ " (account_no, description, check_no, " + _ "transaction_code_id, amount, date) VALUES (" + _ Str$(lngAccountNo) + ", '" + strResult + _ "','T', 3," + Str$(lngAmount) + ", GETDATE())" Set adoRS = adoConn.Execute(strSQL) strResult = strResult + "balance is $" & _ Str$(lngBalance) & "." End If ' Clean up. Set adoRS = Nothing Set adoConn = Nothing GetObjectContext.SetComplete Transaction = strResult Exit Function ErrorHandler: ' Clean up. If Not adoRS Is Nothing Then Set adoRS = Nothing End If If Not adoConn Is Nothing Then Set adoConn = Nothing End If GetObjectContext.SetAbort Transaction = "" ' Indicate that an error occurred. Err.Raise Err.Number, "VIBank.Account.Transaction", _ Err.Description End Function
Using ADO, the code first performs a SQL update to the Account_Balance table and then checks to ensure that the account is not overdrawn. Finally, it performs a SQL insert into the Account_Transaction table, which keeps a log of all account activity. It then calls the SetComplete method to commit the transaction and returns a result string to the calling component—in this case, the Transfer function in the Transfer object.
In a real-life banking application, you'd probably want to issue a receipt number to the end user to provide a way of tracing the transaction. For the sake of brevity, we omitted this step in our example.
We'll now turn our attention to the Visual InterDev portion of this application. We'll look at the ASP Web page that drives the entire transfer of funds process. You saw the initial screen in Figure 20-11. This ASP Web page is self-posting and has two states. The first state occurs when the page is first loaded into the browser. The second state occurs after the user has entered data and submitted the funds transfer. The state of the page is determined by using the REQUEST_METHOD environment variable. The entire source code for the ASP Web page named transfer.asp appears below. This page is part of the VI-Bank Web project.
<%@ TRANSACTION=Required Language=VBScript%> <% ' VI 6.0 Scripting Object Model Enabled %> <!--#include file="_ScriptLibrary/pm.asp"--> <% if StartPageProcessing() Then Response.End() %> <FORM name=thisForm METHOD=post> <html> <head> <meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <title>VI-Bank - Transfer Funds</title> <LINK REL="stylesheet" TYPE="text/css" HREF="_Themes/blueprnt/THEME.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css" HREF="_Themes/blueprnt/GRAPH0.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css" HREF="_Themes/blueprnt/COLOR0.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css" HREF="_Themes/blueprnt/CUSTOM.CSS" VI6.0THEME="Blueprint"> </head> <body> <% RequestMethod = Request.ServerVariables("REQUEST_METHOD") %> <!-- Recordset DTC Control (SQL shown below) --> <!--METADATA TYPE="DesignerControl" startspan <OBJECT classid="clsid:9CF5D7C2-EC10-11D0-9862-0000F8027CA0" id=rsAccountCode style="LEFT: 0px; TOP: 0px"> ... </OBJECT> --> ... cmdTmp.CommandText = 'SELECT Account_Code.Account_Description, Account_Balance.account_no, Customer.ssn FROM Account_Code INNER JOIN Account_Balance ON Account_Code.Account_Id = Account_Balance.account_id INNER JOIN Customer ON Account_Balance.ssn = Customer.ssn WHERE (Customer.ssn = 123456789)'; ... <!--METADATA TYPE="DesignerControl" endspan--> <!-- Table used for general page formatting --> <table> <tr valign=top> <td width=125> <!--#INCLUDE FILE="menu.htm"--> </td> <td> <h2><FONT COLOR="navy"><I>VI-Bank - Transfer Funds</I></FONT></h2> <HR color=navy> <p> <form METHOD="post" ACTION="transfer.asp"> <table border="1" cellPadding="3" cellSpacing="1" width="350"> <tr> <td><font face ="" size="2">From:</font></td> <td> <!-- Data-Bound ListBox DTC Control (AccountFrom) --> <!--METADATA TYPE="DesignerControl" startspan <OBJECT classid="clsid:B5F0E450-DC5F-11D0-9846-0000F8027CA0" height=21 id=AccountFrom style="HEIGHT: 21px; LEFT: 0px; TOP: 0px; WIDTH: 96px" width=96> ... </OBJECT> --> ... <!--METADATA TYPE="DesignerControl" endspan--> </td></tr> <tr> <td><font face ="" size="2">To:</font></td> <td> <!-- Data-Bound ListBox DTC Control (AccountTo) --> <!--METADATA TYPE="DesignerControl" startspan <OBJECT classid="clsid:B5F0E450-DC5F-11D0-9846-0000F8027CA0" height=21 id=AccountTo style= "HEIGHT: 21px; LEFT: 0px; TOP: 0px; WIDTH: 96px" width=96> </OBJECT> --> ... <!--METADATA TYPE="DesignerControl" endspan--> </td></tr> <tr> <td><font face ="" size="2">Amount:</font></td> <td> <% If RequestMethod = "POST" Then %> <!-- Show the previously entered amount. --> <input id="Amount" name="Amount" value= <%=Request.Form("Amount")%> size="15"></td></tr> <% Else %> <input id="Amount" name="Amount" size="15"></td></tr> <% End If %> </table> <p> <table width="350"> <tr><td> <div align="center"><input id="submit" name="submit" type="submit" value="Submit"></div> </td></tr> </table> </form> <hr> <% ' Ensure that this page has been submitted. If RequestMethod = "POST" THEN %> <P> <table BORDER="1" width="350"> <tr></tr> <tr><td> <I> <font face ="" size="2"> <% ' Check that the From and the To accounts are different. If AccountFrom.getText() = AccountTo.getText() Then Response.Write ("<P>") Response.Write ("Please choose different accounts " + _ "to transfer money between.") Response.Write ("<P>") Else ' Check that the user has entered a value in the Amount field. If Request.Form("Amount") <> "" Then ' Set a variable to indicate the transaction ' is taking place. Transaction_Flag = "Y" ' Write out some summary information about the transfer. Response.Write "<P>" Response.Write "Transfer " + _ FormatCurrency(Request.Form("Amount")) Response.Write " from " + AccountFrom.getText() + _ "(" + CStr(AccountFrom.getValue()) + ")" Response.Write " to " + AccountTo.getText() + _ "(" + CStr(AccountTo.getValue()) + ")<P>" %> <OBJECT id=oTransfer PROGID =VIBank.Transfer RUNAT=Server> </OBJECT> <% ' Call the Transfer method of the VIBank.Transfer component. strResult = _ oTransfer.Transfer(CLng(Request.Form("Amount")), _ CLng(AccountTo.getValue()), _ CLng(AccountFrom.getValue()) ) Response.Write strResult Else Response.Write ("<P>") Response.Write _ ("Please enter a value for the amount of the transfer.") Response.Write ("<P>") End If End If %> </I></FONT> </td></tr> <tr></tr> </table> <% End If %> </td></td> </tr> </table> </body> <% ' VI 6.0 Scripting Object Model Enabled %> <% EndPageProcessing() %> </FORM> </html> <% ' The Transacted Script Commit Handler. This subroutine ' will be called if the transacted script commits. Sub OnTransactionCommit() If RequestMethod = "POST" Then If Transaction_Flag = "Y" Then Response.Write "<p><center>Your transaction " + _ was successfully completed.</center>" Else Response.Write "<p><center>Your transaction " + _ was not completed.</center>" End If End If End Sub ' The Transacted Script Abort Handler. This subroutine ' will be called if the transacted script aborts. Sub OnTransactionAbort() If RequestMethod = "POST" Then Response.Write "<p><center>Your transaction was " + _ "not completed.</center>" End If End Sub %>
The transfer.asp page is interesting because there's so much going on. Not only does the page maintain its state, it also has to query the database, display the users' account descriptions in data-bound design-time controls (DTCs), call the Transfer function within MTS, and display the results of the transaction on screen. Note that the code for the DTCs has been trimmed down for clarity's sake. There's a Recordset DTC that performs the initial SQL select statement to get the users' account descriptions. There are also two data-bound Listbox DTCs that are used to display these account descriptions on screen. The dollar amount that the user wants to transfer is captured using a standard text box.
After the user presses the Submit button to submit the form, the selected account information is determined by using the getValue and getText methods of the list box DTCs. This information is then passed as parameters into the Transfer method to perform the transfer. The ASP code also includes some error checking to ensure that the user has entered a value in the Amount field and that the "From" and "To" accounts are different. In a production system, you might want to move some of this error checking to the client side and also trap for non-numeric input in the Amount field.
Finally, the transfer.asp page uses the familiar OnTransactionCommit and OnTransactionAbort event handlers to capture the success or failure of the transaction. This will be determined by the server-side components and by whether the database has been updated correctly. Figure 20-12 shows the results of a successful transaction, while Figure 20-13 shows the results of an unsuccessful transaction. In Figure 20-13, the transaction was rolled back because the transfer would have overdrawn the user's savings account.
Figure 20-12. The Transfer Funds page of the VI-Bank sample application after a successful transfer.
Figure 20-13. The Transfer Funds page of the VI-Bank sample application after an unsuccessful transfer.