Submit Tutorials

All interactions with the database take place on the logon.asp page. This is were we check for valid accounts and passwords. None of the remaining pages of the site need to be changed since they will still check against the Session("PassCheck") global variable for authorization to visit the page.

The script appearing at the top of the logon.asp needs to be changed to work with the database by performing the following steps:

• Make a connection to the database.
• Open the table of accounts
• Search through the table for an account and password matching those entered.
• Redisplay the logon.asp page with an error message if no match is found, or
• Redirect to the welcome.asp page if a match is found.

Creating a Connection Object

The ASP Connection Object contains the properties and methods necessary to make a link between a Web page and a database so that the database can be accessed through scripts appearing on the page. In order to make available these properties and methods to our own page we need to create (instantiate) a Connection Object for our own use.

Connection Objects, and other ASP objects, are created by using the built-in Server Object. In fact, the main purpose of the Server Object is to create other ASP objects for use by scripts. The method for creating a Connection Object is to use the VBScript Set statement, calling upon the Server Object to create a Connection Object for our script. This general format is shown below.

The Server Object uses its CreateObject method to create an ADODB (ActiveX Data Object DataBase) Connection Object. This object is assigned to a ConnectionObject name that we provide and through which we use the properties and methods of the object. The ConnectionObject can be any name of our choosing; for this example, we'll use CNObj as the name of our object.

In creating Connection Objects, then, your only decision is the name to which you want it assigned. All of the other parameters in the Set statement must be coded as shown. Therefore, we create a Connection Object for our logon application by coding:

Let's return to the script at the top of the logon.asp page and begin integrating the statements to perform database access. Our script will still be contingent upon form submission, so we'll use that test as the framework for the new script. We'll just replace all the other existing statements and write the script from scratch. Since making a Connection Object is preliminary to any other processing in the script, the Set statement is the first one to be coded.

Opening a Database

The Connection Object, like most objects provided through ASP, includes properties and methods that we can work with to perform processing tasks. We will not need to know all such properties and methods, but we will introduce those that are useful to the task at hand as we need them.

The primary need for a Connection Object is to open a connection to a database. The Connection Object has an Open method for just this purpose. This method is supplied with a "connection string" identifying the two pieces of information necessary for a script to link to a database: (1) the location of the database and (2) the type of database driver used to access it. Database connections can be made through either of two methods.

ODBC Connections

The older method of accessing a database is directly through the database's ODBC (Open DataBase Connectivity) driver. These drivers provide a standard interface for issuing commands to the database to retrieve or update information. An ODBC connection string identifies the location of the database along with the appropriate database driver by supplying the Open method with DBQ and DRIVER parameters:

The DBQ parameter specifies the physical path on the server to the database, and the DRIVER parameter specifies the ODBC driver to use in accessing the database. The server path begins with the drive letter, followed by the folder hierarchy that needs to be traversed to reach the database. Just to simplify the coding we'll assume that our Database.mdb database is stored on the D: drive inside a folder named "Databases." Thus, the DBQ path we use is d:\Databases\Database.mdb. Different databases use different drivers, and the exact name of the driver is coded in the DRIVER parameter. For Microsoft Access databases the driver name is Microsoft Access Driver (*.mdb).

We can now add the statement to our script to use our Connection Object's Open method to make the link to our database:

This is important! Make sure that the DBQ and DRIVER string is enclosed in a set of quotes. Make sure that your coding does not contain blank spaces other than the ones in the name of the driver. Make sure that the driver name is coded exactly as shown (including the blank spaces). And make sure that the statement is coded on a single line.

The above pair of statements will be valid for all scripts needing access to this particular database, irrespective of the tables to be processed or the type of processing required. Once you have determined the correct coding for these two statements, you can simply copy them into all scripts accessing the database.

Also, keep in mind that you need only one database connection per page, irrespective of the number of different tables you might access from that database. In the current example we are accessing only the single Accounts table. Still, in later applications where two or more tables are processed in a script, we still only need the one connection to the database.

OLE DB Connections

The newer, and preferred, method of connecting to a database is through OLE DB (Object Linking and Embedding, DataBase) technologies. These methods provide exposure to virtually any type of stored data, not just databases, and are the evolving technologies for universal data access. In the case of database access, OLE DB is an intermediate component which, in turn, interfaces with the appropriate ODBC driver.

The OLE DB connection string supplies the same two pieces of information as does the ODBC connection string: (1) the location of the database and (2) the type of database driver used to access it. In this case the Data Source parameter supplies the location and the Provider parameter supplies the driver:

The database path is the physical server path to the named database and "Microsoft.Jet.OLEDB.4.0" references the OLE DB component for linking to the ODBC driver for Access databases. You should probably get in the habit of using this newer format, although the examples in this tutorial use the older form of connection string.

Connection Strings for Other Data Sources

As a point of reference the following tables show the connection strings used to access other representative databases and data stores. In all cases the information in the data store is exposed to the script as a set of rows and columns.

(Note when accessing Excel spreadsheets that names must be assigned to the data range and to the data columns in order to retrieve the cells as a row x column table.)