16.3.3- Building the Script Component

Created by Brendan Doss.
Last Updated by Jim Minatel.

Public Categorized as 16. Building Script Components for ASP.

Not yet tagged

</script>

I've missed out the function implementation code here – we'll come back to that in a moment. The first thing that's done in this block is to declare two variables called AccountID and Balance – these will hold the values of the BankAccount object's AccountID and Balance properties (which we declared in Step 3 of the wizard). You'll recall that we told the wizard to set the default values of these properties equal to 0 – so the value of 0 is assigned to each of these variables within the first few lines of the code above.

Next, we declare some more variables – dcBank, rsBank, strSQL, and strDataConn. These are all variables that we'll use during the course of the component's existence, so it makes sense for them to be declared here. In fact, strDataConn will contain the connection string that is used on various occasions by the component, so we also assign the appropriate value to strDataConn immediately.

Then there are definitions of four functions – get_AccountID(), put_AccountID(), get_Balance() and AddMoney(). Here's what they are for:

Whenever we use the BankAccount object's AccountID property in our ASP code, the component calls get_AccountID(). This function goes and fetches the current value of the component's AccountID variable, and returns it to the calling application.
When we write a new value to the BankAccount object's AccountID property in our ASP code, the component calls its put_AccountID(), passing the desired new value as the only parameter to the function. Then, the put_AccountID() function assigns the parameter value to the component's AccountID variable.
Whenever we use the BankAccount object's Balance property in our ASP code, the component calls get_Balance(). This function works just like the get_AccountID() function described above: it fetches the current value of the component's Balance variable, and returns it to the calling application.
Note that the component doesn't have a put_Balance() function. That's because the BankAccount component's Balance property is a read-only property (as we specified in Step 3 of the wizard).
When we call the BankAccount object's AddMoney method in our ASP code, we want to update the database to reflect the change that's being made; so the component calls its AddMoney() function.

We've provided our own code for those last two functions, so let's take a look at that next.

The get_Balance() Function

The get_Balance() function simply queries the data store for the latest balance assigned to this particular account, and returns the value to the calling application. To do this, we use an ADO Recordset object. We've already declared the variable rsBank in the global declarations for the component, so the first thing we need to do is set rsBank to be a Recordset object:

function get_Balance()

Set rsBank = Server.CreateObject("ADODB.Recordset")

Now, we'll write the SQL command that will be used to populate the Recordset object. We've already declared the variable strSQL in the global declarations for the object, so it is ready for us to assign a SQL string to it:

strSQL = "SELECT ResultingBalance FROM Register " & _

"WHERE AccountID='" & AccountID & "' ORDER BY SeqID DESC"

Here, we're requesting all of the records in the Register table that correspond to cash transfers relating to this particular back account. We identify the account in question by using the component's AccountID variable (remember that an instance of the BankAccount object represents one particular bank account in our system, and is identified by its AccountID).

Now we open the recordset. Because we've requested the records to be ordered in descending order by SeqID, the first record in the recordset will be the record relating to the most recent transfer (and hence will contain the current balance for the account). Since the cursor is automatically placed on the first record in the recordset after opening, we can immediately assign the value of that record's ResultingBalance field to the component's Balance variable:

rsBank.Open strSQL, strDataConn

Balance = rsBank("ResultingBalance")

Now we can assign the return value of the function to the same value:

get_Balance = Balance

Finally, we don't need the Recordset object anymore, so we Close it and clean up:

rsBank.Close

Set rsBank = Nothing

end function

The AddMoney() Function

The AddMoney() function writes a new record to the Register table, each time we add an amount to (or subtract an amount from) the account's balance. It takes two parameters – the amount to be added and the check number.

Each cash transfer requires that we call the AddMoney method twice – once to deduct the amount from the source account, and once to add the money to the destination account.

Also, remember that in the next chapter we'll identify why this transfer would be more robust if we also employed transaction processing; and we'll build an improved version that uses transactional processing to complete the transfer.

After declaring a few necessary variables that are local to this function, we get hold of the current balance for this account by using the get_Balance() method:

function AddMoney(amount, checkNum)

Dim curBalance, strWorD, strTransNote

curBalance = get_Balance()

Remember that we can only call the get_Balance() function explicitly within the code for the component itself. Later on, in our ASP pages, we'll be able to request the value of an account's balance by using the BankAccount object's Balance property (which itself calls the get_Balance() function).

Next we quickly check to see whether the account's balance will go overdrawn after the transfer. If so, we don't want to make the transfer at all so we just write a message to the screen and set the return value for the function to 0:

If CDbl(curBalance) + CDbl(amount) < 0 Then

Response.Write "<BR>ABORT: Insufficient balance in Source Account."

AddMoney = "0"

Otherwise, we go ahead with the addition or subtraction of cash. First, we check the value of the amount parameter to determine whether we're adding or subtracting an amount:

Else

If amount < 0 Then

strWorD = "W"

strTransNote = "Cash withdrawal"

Else

strWorD = "D"

strTransNote = "Cash credit"

End If

Now we use an ADO Connection object to add the new record to the Register table. First we Open the Connection object using the connection string specified in the component's strDataConn variable. Then we write the SQL INSERT command required to add the record, assigning the string to the strSQL variable. Then we use the Connection object's Execute method to execute the command and add the record:

Set dcBank = Server.CreateObject("ADODB.Connection")

dcBank.Open strDataConn

' move the money into or out of the account

strSQL = "INSERT INTO Register(AccountID, CheckNumber, TransDate, " & _

"TransType, TransNote, Amount, " & _

"PriorBalance, ResultingBalance) " & _

"VALUES ('" & AccountID & "', " & checkNum & ", '" & _

Date & "', '" & strWorD & "', '" & strTransNote & "', " & _

amount & ", " & curBalance & ", " & curBalance + amount & ")"

dcBank.Execute strSQL

Note that the SQL command writes values to eight of the nine fields contained in the Register table. The remaining field, SeqID, is an 'autogenerating' field – which means that the database will generate a value for this field at the time the record is created.

Now we've finished with the ADO Connection object, so we tidy it up:

dcBank.Close

Set dcBank = Nothing

And finally we write a short message to the browser, and set the return value of the function to 1:

If amount < 0 Then

Response.Write "<BR>COMPLETE: Withdrew $" & -amount & " successfully."

Else

Response.Write "<BR>COMPLETE: Credited $" & amount & " successfully."

End If

AddMoney = "1"

End If

end function

OK, let's build some pages that we can use to test this component.

Try It Out – Build the Front Page of our Bank Application

In order to test the BankAccount.wsc component, we'll build three ASP pages. The first page, Bank.asp, will allow the bank teller to select the appropriate details for the cash transfer: the debit account, the credit account, and amount to be transferred. The second page, ExecuteTransfer.asp, will use the component to process the information submitted by the user. A third page, BankConn.asp, is an SSI that contains the connection string details for accessing the data store.

So let's build these pages and test the component, and then we explain how it all works.

1. Create a new file (which we'll call Bank.asp), and enter the following code into it:

<HTML>

<HEAD>

Dim dcBank, rsBank

Set dcBank = Server.CreateObject("ADODB.Connection")

Set rsBank = Server.CreateObject("ADODB.Recordset")

dcBank.Open strConn

rsBank.Open "SELECT * FROM Account", dcBank

Dim strOptionString

strOptionString = ""

Do Until rsBank.EOF

strOptionString = strOptionString & _

"<OPTION VALUE='" & rsBank("AccountID") & "'>" & _

rsBank("HolderName") & " [" & rsBank("AccountID") & "]" & _

"</OPTION>"

rsBank.MoveNext

Loop

rsBank.Close

dcBank.Close

Set rsBank = Nothing

Set dcBank = Nothing

<TITLE>A First Banking Example</TITLE>

</HEAD>

<BODY>

<H2>Elementary Banking for Very Small Banks</H2>

<H3>Cash Transfer Request - Please enter the following details:</H3>

Select source account:

Select destination account:

Enter check number: <INPUT TYPE="TEXT" NAME="CheckNum"><BR>

Enter check amount: $<INPUT TYPE="TEXT" NAME="AmtToTransfer"><BR><BR>

</FORM>

</BODY>

</HTML>

2. Save Bank.asp into your \inetpub\wwwroot\BegASPFiles directory.

3. Now start a new text file – this one is going to be called ExecuteTransfer.asp. Insert the following code:

<HTML>

<HEAD><TITLE>A First Banking Example</TITLE>

</HEAD>

<BODY>

<H2>Elementary Banking for Very Small Banks</H2>

<H3>This page will execute your Cash Transfer Request</H3>

Dim srcAccountID, destAccountID, intCheckNum, dblAmtToTransfer

Dim srcBalance, destBalance

Dim strAbortReason, blnSuccess

intCheckNum = Request.Form("CheckNum")

dblAmtToTransfer = Request.Form("AmtToTransfer")

srcAccountID = Request.Form("SourceAccount")

destAccountID = Request.Form("TargetAccount")

If srcAccountID = destAccountID Then

Response.Write "Can't transfer funds when " & _

"the source and destination account are the same"

Else

Set objSourceAcct = Server.CreateObject("BankAccount.WSC")

Set objDestAcct = Server.CreateObject("BankAccount.WSC")

objSourceAcct.AccountID = srcAccountID

objDestAcct.AccountID = destAccountID

Response.Write "<B>Prior Balances:</B><BR>" & _

"Source Account ID: <B>" & objSourceAcct.AccountID & "</B> " & _

"Balance: <B>$" & objSourceAcct.Balance & "</B><BR>" & _

"Destination Account ID: <B>" & objDestAcct.AccountID & "</B> " & _

"Balance: <B>$" & objDestAcct.Balance & "</B>" & _

"<HR><BR>" & _

"We are about to transfer <B>" & FormatCurrency(dblAmtToTransfer) & _

"</B> from the source account to the destination account...<BR><HR>"

blnSuccess = objSourceAcct.AddMoney (-dblAmtToTransfer, intCheckNum)

If blnSuccess <> 0 Then

objDestAcct.AddMoney dblAmtToTransfer, intCheckNum

Response.Write "<BR><BR>Cash transferred!"

End If

Response.Write "<BR><BR>Cash transferred!<BR><BR>" & _

"<B>Final Balances:</B><BR>" & _

"Source Account ID: <B>" & objSourceAcct.AccountID & "</B> " & _

"Balance: <B>$" & objSourceAcct.Balance & "</B><BR>" & _

"Destination Account ID: <B>" & objDestAcct.AccountID & "</B> " & _

"Balance: <B>$" & objDestAcct.Balance & "</B>" & _

"<HR><BR>"

End If

</BODY>

</HTML>

4. Save ExecuteTransfer.asp into your \inetpub\wwwroot\BegASPFiles directory. You probably noticed the the Bank.asp page needs some connection details, which we'll provide in the form of an SSI file called BankConn.asp. So start one more new text file, and insert the following code:

Dim strConn, strDatabaseType

' Use one of these lines, and comment out the other

strDatabaseType = "Access"

'strDatabaseType = "MSDE"

' Now use that to set the connection string

If strDatabaseType = "Access" Then

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=E:\datastores\Bank.mdb;" & _

"Persist Security Info=False"

Else

strConn = "Provider=SQLOLEDB;Persist Security Info=False;" & _

"User ID=sa;Initial Catalog=Movie;" & _

"Initial File Name=C:\MSSQL7\Data\Bank_Data.mdf"

End If

5. Select the appropriate database type for your setup, by setting the value of strDataBaseType equal to either "Access" or "MSDE", and then save BankConn.asp into your \inetpub\wwwroot\BegASPFiles directory.

6. Now you can test the pages. Using your browser, browse to the Bank.asp page:

Chapter16_image021

Select the source and destination accounts from the drop-down lists, type in a check number (it can be any arbitrary integer, but not an arbitrary string of characters) and the cash transfer value (as shown in the screenshot above). Then click the Process This Check button:

Chapter16_image022

How It Works

Let's have a quick look at Bank.asp first – this page contains the HTML form that prompts the user to submit information for processing. The form is quite simple: on submission of the form, the submitted information will be passed to the ExecuteTransfer.asp page using the POST method:

Select source account:

Select destination account:

Enter check number: <INPUT TYPE="TEXT" NAME="CheckNum"><BR>

Enter check amount: $<INPUT TYE="TEXT" NAME="AmtToTransfer"><BR><BR>

</FORM>

There are four fields – the source account, the destination (or target) account, the check number and the amount that is to be transferred.

In order to facilitate the user's choice of account ID (and to reduce the possibility of errors), the first two fields offer the user a choice of only those accounts that exist in our data store. So we use an ADO Recordset object to query the database for all of the account IDs that are contained there, and we use that information in the form.

To do this, we need to create the list of <OPTION> tags for the form dynamically, based on information contained in the data store – we'll use ADO and ASP to generate a string of HTML. We first create an ADO Connection and Recordset object, open the connection and execute a SQL command against the database to populate the Recordset object:

Set dcBank = Server.CreateObject("ADODB.Connection")

Set rsBank = Server.CreateObject("ADODB.Recordset")

dcBank.Open strConn

rsBank.Open "SELECT * FROM Account", dcBank

Now we declare the variable that will contain our string, and use the contents of the Recordset object build the string of HTML <OPTION> tags:

Dim strOptionString

strOptionString = ""

Do Until rsBank.EOF

strOptionString = strOptionString & _

"<OPTION VALUE='" & rsBank("AccountID") & "'>" & _

rsBank("HolderName") & " [" & rsBank("AccountID") & "]" & _

"</OPTION>"

rsBank.MoveNext

Loop

Here, there is one record in the Account table for each account in our bank system. So we loop through all the records, obtaining the AccountID field and creating a string of HTML output around it. For each account, we'll add a string that looks something like this:

<OPTION VALUE='4839-1532'>Montague L. Donovan [4839-1532]"</OPTION>"

These consecutive strings of HTML will be used as output to the browser, later in the page. Having created our HTML string, we don't need the Recordset and Connection objects so we Close them and clean up:

rsBank.Close

dcBank.Close

Set rsBank = Nothing

Set dcBank = Nothing

And as we saw a moment ago, we use the strOptionString string within the form to create all the options in the drop-down listbox, like this:

Select source account:

Select destination account:

We're just using Response.Write to output the list of <OPTION> tags to the browser. In fact, there are two drop-down list-boxes, containing the same set of options, so we use the same string twice.

OK, now let's move on to the ExecuteTransfer.asp page. The first thing we do in this page is capture the information submitted by the user, which arrived at this page via the POST method and is therefore found in the Request.Form collection:

intCheckNum = Request.Form("CheckNum")

dblAmtToTransfer = Request.Form("AmtToTransfer")

srcAccountID = Request.Form("SourceAccount")

destAccountID = Request.Form("TargetAccount")

If the source and destination accounts are the same, we save ourselves the trouble of doing the transfer:

If srcAccountID = destAccountID Then

Response.Write "Can't transfer funds when " & _

"the source and destination account are the same"

Otherwise, we get on with the job of transferring the cash. The logic for transferring the cash is all contained within the methods of the BankAccount object, so we'll use that functionality here. First, we create two instances of the BankAccount object – one for the source account and one for the destination account. We identify them as such by setting the AccountID property of each instance:

Else

Set objSourceAcct = Server.CreateObject("BankAccount.WSC")

Set objDestAcct = Server.CreateObject("BankAccount.WSC")

objSourceAcct.AccountID = srcAccountID

objDestAcct.AccountID = destAccountID

Next, we write to the browser the balance details of each of these accounts in advance of the transfer:

Response.Write "<B>Prior Balances:</B><BR>" & _

"Source Account ID: <B>" & objSourceAcct.AccountID & "</B> " & _

"Balance: <B>$" & objSourceAcct.Balance & "</B><BR>" & _

"Destination Account ID: <B>" & objDestAcct.AccountID & "</B> " & _

"Balance: <B>$" & objDestAcct.Balance & "</B>" & _

"<HR><BR>" & _

"We are about to transfer <B>" & FormatCurrency(dblAmtToTransfer) & _

"</B> from the source account to the destination account...<BR><HR>"

Then we perform the transfer itself. The transfer itself is a two-step process: first, we call the AddMoney method of the source account, passing the amount as a negative number (so that the amount is deducted from the account's balance). Second, we call the AddMoney method of the destination account, passing the amount as a positive number (so that the amount is added to the account's balance):

blnSuccess = objSourceAcct.AddMoney (-dblAmtToTransfer, intCheckNum)

If blnSuccess <> 0 Then

objDestAcct.AddMoney dblAmtToTransfer, intCheckNum

Response.Write "<BR><BR>Cash transfered!"

End If

Note that we check the success of the deduction before executing the credit part. If the transfer of cash will cause the source account to go overdrawn, then the first AddMoney method does not deduct the money (it does not allow the account's balance to go overdrawn), and it returns a 'success' report of 0 (denoting failure). We use that to ensure that the credit doesn't take place either.

Finally, we report the final balances of the two accounts:

Response.Write "<BR><BR>Cash transfered!<BR><BR>" & _

"<B>Final Balances:</B><BR>" & _

"Source Account ID: <B>" & objSourceAcct.AccountID & "</B> " & _

"Balance: <B>$" & objSourceAcct.Balance & "</B><BR>" & _

"Destination Account ID: <B>" & objDestAcct.AccountID & "</B> " & _

"Balance: <B>$" & objDestAcct.Balance & "</B>" & _

"<HR><BR>"

End If

</HTML>

And that's it. In the next chapter, we'll explain how this example, functional as it is, could still cause problems in a multi-user situation or if there is a failure of some kind – and how we can use transactions to fix that.

<< 16.3.2- Setting up the Database

Chapter16

16.4.0- Summary >>