home / articles / frontpage

Database Connectivity with ASP and ADO

I have received a lot of requests for information on accessing databases with Active Server Pages (ASP), so I'm going to give you a tour of the basics, and hopefully you will be able to take it from there.

ASP uses a technology called ActiveX Data Objects (ADO) to work with databases. ADO is ActiveX technology which is built into the Internet Information Server (IIS). It is comprised of a number of objects which you can use. I will begin by explaining the "major players" briefly.

There are 3 major objects which you should be aware of in ADO: the Command Object, the Connection Object, and the Recordset object. Most of the time, you will only have to work with the RecordSet object (arguably), but in certain cases, you may want to set properties in (or use) one or both of the other 2 objects. Whenever you perform a database operation, these 3 objects are present, but it isn't necessary often to explicitly create all 3, as when one is used, the other 2 are implicitly created, even though you may not assign a variable to access them. The syntax for creating a variable to access these objects is (in VBScript) "[variable name] = Server.CreateObject("ADODB.[object name]")"

The RecordSet Object

The RecordSet object is basically a cursor (a temporary table which exists in memory) with some built-in functions and properties for working with the records contained in it. You can create a RecordSet object explicitly, or by executing a command through the Command Object.

Some of the properties which you may use frequently are:

• BOF - Beginning of file

• EOF - End of file

• MaxRecords - Maximum number of records returned in a query

• RecordCount - The number of records in the RecordSet

• CursorType - Forward-only, static,dynamic, and keyset

Some of the methods (functions) of the RecordSet object which you may be most likely to use are:

• Open - Open the Recordset

• Close - Close the RecordSet

• Move - Move to a specific record

• MoveFirst - Move to the first record

• MoveNext - Move forward one record

• Move Previous - Move backwards one record

• MoveLast - Move to the last record

• AddNew - Add a new record to the RecordSet

• Update - Update the current record

The Command Object

The Command Object is the workhorse of  ADO. It is the object which commands the database, whether it's issuing an SQL statement, or executing a stored procedure contained in the database itself. using the Command Object, you can create parameterized queries, which can be altered on the fly. You can change the query being issued, and change the properties of the Command Object itself.

There are far less properties of a Command Object than a Recordset object. In fact, you're only likely to use the following , if any:

• CommandText - The actual text of the command, such as a SQL statement

• ActiveConnection - The associated Connection Object.

There are only 2 methods for a Command Object. They are:

• CreateParameter - Create a parameter for a query in the Parameters Collection

• Execute - Execute the Command

The Command Object also has a collection called the Parameters Collection. This is where parameters are organized for parameterized queries. Parameters have properties as well, and can be accessed either by their position in the Parameters Collection (index), or by name.

You can use parameters in your SQL statements by inserting question marks for each parameter used. The parameters must first be defined, and their values will be substituted in the query in the order they appear in the collection. If you wish to use parameterized queries, you might want to read up some more on this subject, but I don't want to take all day.

The Connection Object

The  Connection Object is the object which actually does the "talking" with the database. It defines the connection type, and properties too numerous to mention. It may contain a System DSN name which is used to identify the ODBC driver and path to the database. I may contain the physical path to the database. It can also store user name and password information which is used to gain access to a secure database. And it can fine-tune the connection in other ways as well. The Command Object and RecordSet object actually access the database through the Connection Object. However, it isn't often necessary to use this object, as it can be implicitly created by the RecordSet Object (my favorite method), and the default values for the many properties are usually fine to work with.

The single most important and most-often used property of the Connection Object is the ConnectionString property. If you have used IDC (Internet Database Connector) before, you're familiar with identifying the System DSN. This is handled in the ConnectionString Property, as well as some other properties as well. The ConnectionString property is defined by a string of property definitions (name/value pairs) separated by semicolons. Here is an example:

"DSN=jobs;DBQ=C:\AOL30A\download\San Diego Jobs\jobs.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;"

You'll notice that the System DSN is the first item identified, followed by the "DBQ" (the physical location of the database). Other properties are defined as well. This Connection String was "borrowed" from a global.asa file created automatically by Microsoft Visual InterDev. However, you'll find that in the vast majority of cases, it isn't necessary to define all of these (and/or more) properties of the Connection object.

In fact, I'm going to show you a simple way to use ADO to do most of your routine database stuff, and you can forget most of what you've read here (unless you need to do something fancy). The following is a demonstration of some simple VBScript to execute a SELECT statement. You can even copy and paste this into your own ASP pages if you wish.

We'll begin with the assumption that the user has filled out a form containing a single field called "lastname." They are looking up personnel records in a table called "personnel," and want to find all records with a "lastname" field containing the name they've typed in in the form. A System DSN has been set up using the 32-Bit ODBC Driver Administrator, by the name of "mydsn." If you've read my earlier articles about ASP, you remember that to obtain the value of a form field, you use the ASP Request.Form Object, and name the field. So, without any further ado, let us proceed with the code:

A Simple ASP ADO SQL Query

Set rs = Server.CreateObject("ADODB.RecordSet")
param = Request.Form("lastname")
q = "SELECT * FROM personnel WHERE lastname LIKE '" & param & "'"
rs.Open q, "DSN=mydsn;"

if NOT rs.EOF then
     while NOT rs.EOF
          Response.Write rs("firstname") & " " & rs("lastname") & "<BR>"
          rs.MoveNext
     wend
end if

A Simple Explanation of A Simple ASP ADO SQL Query

Line 1. Create Recordset object
Line 2. Place form field value in a variable named "param"
Line 3. Define query by concatenating strings and variable value
Line 4. Open RecordSet Object. Note that the first parameter is the Command Text. The second parameter is the Connection String. The Command Object and Connection Object are created implicitly.
Line 5. Make sure the RecordSet isn't empty
Line 6. Begin executing a loop which goes through all records in the RecordSet.
Line 7. Write each record's "firstname" and "lastname" fields to the page on a separate line.
Line 8. Move to Next Record.

Next week I'll take you a step further by showing you how to use FrontPage 98 to visually design your ASP page, and integrate your ASP/ADO code into it.

Author: Kevin Spencer
Date: 12/26/97

More articles about Microsoft FrontPage
More articles by Kevin Spencer
Author Biography