Submit Tutorials

Rather than retrieving the entire table of accounts and passwords and looking through them one at a time, we'll use an SQL SELECT statement to attempt to retrieve a matching record. That is, we'll look for a record in which the Account value from the form matches the Account value in the table, and where the Password value from the form matches the Password value in the table. We'll compose an SQL statement to select this record.

If there is a matching record in the table, then this single record will be retrieved; if there is not a matching record in the table, then NO records will be retrieved. Thus, after issuing the SELECT statement, the mere existence of a retreived record indicates that the user entered a matching account and password; on the other hand, an EOF property condition for the recordset indicates that the user did NOT enter a correct account and password.

Coding of a SELECT statement in which variables, rather than literal values, are used for matching can get a little messy, so we'll take it slowly. In general, our statement must end up being in the following format in order to satisfy the syntactical rules of SQL:

That is, we want to SELECT all (*) fields (the Account field and the Password field ) from the record in the Accounts table WHERE the value in the Account field of the table matches the Account value entered on the form, and the value in the Password field of the table matches the Password value entered on the form. Both the Account and Password criterion values must be enclosed in apostrophes since these are defined as text fields in the table.

From a coding standpoint, then, we need to insert references inside the single quotes to the corresponding form values. In order to accomplish this, we can piece together an SQL statement from both the "literal" text strings that represent the fixed, unchanging code of the SELECT statement along with the variable values taken from the Request.Form Collection that represent the visitor's account and password submission. The various pieces of the SELECT statement are

When these pieces are strung together, we will have a valid SQL SELECT statement that attempts to retrieve a matching record from the Accounts table. Let's concatenate these five elements and assign them to a variable named SQL. We can either compose one long assignment statement,

or we can piece the statement together through separate concatenations:

Assuming, for instance, that the visitor submitted the Account aaaaa and the Password 11111, then the concatenations would produce the following SELECT statement assigned to variable SQL:

SELECT * FROM Accounts WHERE Account='aaaaa' AND Password='11111'

This is exactly the statement we need to perform the search. If, on the other hand, the visitor submits the Account bbbbb and the Password 2222, then the SQL variable would end up containing

SELECT * FROM Accounts WHERE Account='bbbbb' AND Password='22222'

As you can see, any Account and Password value submitted by the visitor gets plugged into the SELECT statement. The statement is assigned to variable SQL, which, in turn, is issued through the recordset Open statement.

Finally, let's put this technique to work in our logon script. Here is the complete code:

The SELECT statement is composed with the Account and Password entered by the visitor, it is assigned to variable SQL, and is issued to the database management system through the Recordset Object's Open statement.

One of two results happen: (1) the DBMS finds the matching record and returns it as a recordset; or (2) the DBMS does not find a matching record and no records are returned. In the first case the EOF property of the recordset is False (the cursor is pointing to the record); in the second case the EOF property of the recordset is True (the cursor is pointing beyond the end of the recordset). This is all we need to know.

If there is NOT an EOF property associated with the recordset (If Not RSObj.EOF), we know that a matching account and password were found. In this case we set the Session("PassCheck") flag and redirect the visitor to the welcome.asp page. Otherwise, we format an error message and the page is sent back to the browser to redisplay the form.

Notice there is no need to set up a loop to iterate through the complete set of records in the Accounts table. The DBMS either finds a matching record and returns it to our script as a single-record recordset; or it doesn't find a match and returns an empty recordset.

Testing the Application

The following link implements the logon.asp page using a SELECT statement to locate an Accounts record with matching Account and Password values. You can use the example accounts:passwords to check the script: aaaaa:11111, bbbbb:22222, or ccccc:33333.

Run Logon Application