Submit Tutorials

Iterating through a Recordset

An appropriate VBScript iteration structure is the Do While...Loop. Its general format is shown below.

The condition test is formulated to render a true or false result. As long as the result of the condition test is true, the statements inside the loop are executed over and over again. When the test becomes false, the loop is exited and the statement following the Loop statement is executed.

Recall that when a recordset is first opened, the recordset cursor is positioned at the first record. As we continue our account and password checking, we need to advance the cursor through the recordset, examining each record, in turn, until we find one with a matching account and password. If we don't happen to find a matching record, the cursor will eventually be advanced past the end of the recordset.

Recordset Objects have a property setting that indicates whether a recordset cursor has advanced beyond the last record in the recordset. This is the EOF (end-of-file) property. If there IS an EOF property associated with the recordset, then the cursor has advanced beyond its last record; If there is NOT an EOF property associated with the recordset, then the cursor has not yet reached beyond the last record.

We can set up a program loop, then, that advances the recordset cursor from one record to the next, looking for a matching account and password. This loop will continue until we run out of records to check; until the Recordset Object's EOF property is true. By using the VBScript Do While...Loop construct we can create this loop in the syntax: Do While Not RSObj.EOF...Loop; that is, continue the loop so long as there is not an EOF property associated with our RSObj recordset.

We still need a method of advancing the recordset cursor from one record to the next. Recordset Objects provide this ability with the MoveNext method. Therefore, we need to use our RSObj.MoveNext method to advance the cursor each time through the loop.

Let's add these statements to our logon script to create the structure of the loop that iterates through our recordset:

Checking for Matching Records

Inside the Do While loop is where we need to check for a record with a matching account and password. Recall that this script has available to it the Request.Form Collection containing the Account and Password fields transmitted from the form. And it has available the RSObj.Fields Collection containing the Account and Password fields from the database table. Therefore, as the script iterates through its loop, we need to compare

for each record in the recordset, looking for matching values. If both matches are made, then we have found a valid account and password.

If we find matches to the values entered on the logon form, then we'll set the global variable Session("PassCheck")=True, as we did in the previous script. We'll also redirect the visitor to the welcome.asp page, effectively ending the script.

If, on the other hand, no matches are found, then the recordset cursor gets advanced beyond the last record in the recordset, and the EOF property ends the loop. If the loop ends, this is a signal that no matching record was found. In this circumstance we format an error message and the form is redisplayed.

Let's put all of this together in the script and reintroduce the HTML coding for the entire page.

Closing Connections and Recordsets

Both Connection Objects and Recordset Objects have formal Close methods that can be applied when you are finished with either. Standard programming practice normally dictates the you close open items when you are done with them. Under ASP, however, this is not necessary. In fact, ASP automatically closes any open connections and recordsets when it finishes processing a page.

About the only time you need to close a recordset is when you intend to open it again on the same page. We'll have occasion to do this later. Or, you might close a recordset if you wish to use the same recordset name to open a different recordset. However, you should probably use different names for different recordsets just to keep them straight in your scripts. Whether you use the Close method is up to you, and you may choose to do this for your own "closure" purposes.

This is not a suggestion, but here is the logon script with Close methods applied:

Both the RSObj and CNObj are closed prior to redirecting the user to the welcome.asp page. They are both closed when the script ends following the loop.

Testing the Application

The following link implements the logon.asp page using a database to validate accounts and passwords. You can use the example accounts:passwords to check the script: aaaaa:11111, bbbbb:22222, or ccccc:33333.

Run Logon Application