A web site just isn't a web site these days unless it is dynamic, data driven, and user focused. We expect the web sites we visit, and develop, to always be current, the data to be fresh and accurate, and we want the ability to ask for specific information. Active Server Pages, and ADO are two Microsoft technologies that provide developers with the tools to meet those demands head on, and to do so in an easy yet scalable way.

Active Server Pages (ASP) is Microsoft's offering for server side scripting in a web environment. ASP makes it possible to provide dynamic, user driven web sites. Web sites can show users a completely different set of content every time the user refreshes a page. Results can be formatted to meet the capabilities of the web browser. Two of the big three requirements of web sites can be met with ASP alone. The data driven demand though is left unanswered by ASP itself. ASP instead relies on other solutions to gain access to data.

Developers, of any kind, are really in the business of manipulating data. All applications eventually end up accepting, analyzing or archiving data. This global need for data has led to innumerable approaches to provide access to databases. Microsoft's latest solution for a universal means of accessing data is OLEDB. OLEDB gives developers an open, powerful set of tools to both provide as well as consume data of virtually any kind. OLEDB by itself though can be cumbersome or impossible to work with from higher-level languages such as Visual Basic, or VBScript. Microsoft therefore provides us with ADO (Active Data Objects) as a wrapper object model to provide access to OLEDB from those languages.

This document serves as a primer for developers that are interested in using ADO to gain access to databases from Active Server Pages. It assumes your familiarity with ASP, VBScript, and general database terminology. If this is not the case, you might be better served if you reviewed the documentation on those topics available in the MSDN library. You can gain access to this information via the web at http://msdn.microsoft.com/

The Plan: top

This tutorial covers the code that you would need to put into the global.asa file of an ASP application, as well as the code to use in individual pages to access a database. The samples deal with an Access database, but the concepts are identical no matter what type of database you are accessing.

To start, there will be a brief (very brief) overview of the ADO object model, then the major ADO objects will be discussed

Once the use of the ADO objects have been outlined, there is an actual sample of pulling data from a database. As an added feature, you can view all of the ASP code for this tutorial. There are links to the code pages at the bottom of this page. Finally, to top it all off, there is another sample page that allows you to enter your own query to run against the database. The customquery.asp page takes the concepts outlined in this document and puts them to good use. It will give you a more compact example to reference when you are trying to figure out this ASP stuff.

The sample database is a collection of flashcard information. This is an actual database of real questions and resources that I created while studying for various tests. There are two tables in the database. The first is the FlashCards table with the individual cards in them. The second table is the Resources table. It has the bibliographic information stored in it. The FlashCards table has a foreign key relationship to the Resources table on the ResourceID column.

Now that you've got the lay of the land, let's get started.

The ADO Object Model: top

ADO provides three basic objects to control your access to databases. You do not need to create objects of all three types, you can create them independently. For example, you never NEED to specifically create a Connection object or a Command object to open a recordset. However, if you take the time to understand ADO's structure, and implement the objects as they are intended, you arrive at a more structured solution, as well as bypass some pitfalls to declaring all objects outside of the hierarchy.

When dealing with an ASP application you have various levels that you can scope your database objects at. The Application level, the session level, or the page level. Objects and variables stored at the application level are visible to all visitors to an ASP web site. Objects and variables stored at the session level are visible only to the current session (visitor). Finally objects stored at the page level visible only to the current page. Where it makes sense I like to store my objects at the session level. This gives all of your pages access to the same data resources, provides an easy way to keep your place in recordsets between pages, and finally provides some performance increase because you do not need to re-query the data every time you load a page.

It is also important to note that there are many ways to use the objects in conjunction with each other that are not outlined in this document. The Connection's Execute method provides another convenient means to create recordsets, or execute update queries. This tutorial is only an overview, and describes one method to retrieve data from a database into an HTML page. It is up to the reader to research the other features and functions of ASP and ADO to exploit all of their capabilities.

The ADO Object Model

The Connection Object: top

The code in this tutorial will use the three main objects in ADO. The Connection object, the Command object, and the Recordset object. I will start by defining an ADO 'Connection' object. The connection object represents a connection between your ASP application, and the database. Again, this can be created at the application, session, or page level. To define a connection at the session level, we use the global.asa file in the ASP applications root directory.

A connection object basically tells your ASP code where the database is, how to connect to it, and provides a means of actually initiating that connection. The advantages to specifically creating a connection object are many. The basic benefits are that it provides a structured means to connect to your database, a way to persist that connection throughout the lifetime of a session, and finally it allows gives some performance increase by reducing the number of subsequent connections that must be made to the database.

One other capability of connections that is not covered in this tutorial is 'Connection Pooling'. Connection Pooling is a feature of the database provider that will keep discarded connections open for a specified amount of time. If a new connection request is made before the idle connection object is destroyed and it is for the same database with the same username and password, the idle connection object will be used. This saves the time and overhead of re-connecting to the database, etc. Your database provider may not support connection pooling. Review the documentation for your database provider to find out about connection pooling and how to configure it.

The code to create, and persist the connection at the session level is put into the Session_OnStart event handler in the global.asa file. When a user first visits the web site (or ASP application) a Session object is started for them. When the session object is first started, the 'OnStart' even will be fired and the code in the 'Session_OnStart' event handler in the global.asa file will be executed. Remember, this is only done when the session is first created. Sessions remain active on the server as long as the user continues to load pages from within the same web site (or ASP application). If the user leaves the site, and the session will remain active until it exceeds the timeout limit. The default timeout is 20 minutes, but that can be modified with the Session.Timeout property.

The code to create our connection object looks like this:

For the above code segment, remember again that it is in the global.asa file, in the Session_OnStart subroutine.

You could open the connection object with a System DSN rather than an explicit connection string if you wanted to. This does require the ability to create DSNs on the server. Also note, that it must be a system DSN that you create. To use a DSN rather than an explicit connection string, the code to open the connection would look more like this:

That looks much easier than the first method. Remember though, to open connections to your databases in this manner requires that you have access to the ODBC configuration utilities on the server.

Now that our connection object has been created, lets look at the properties for it. The table below shows the actual properties for the Session("CON") object in this tutorial's ASP application. Remember, all we specified was the connection string. All of the rest of theses attributes are set to their default values:

The Command Object: top

You may decide that a command object is a little unnecessary. It does, however, provide you with a couple of benefits over opening recordsets directly. When using the command object, you can request that the provider 'prepare' or compile the query if the provider supports it. One other advantage to using the command object is that you can override the Connection object's CommandTimeout property. If you know that a particular query will take a long time you can specify a longer CommandTimeout property for the command object than is set for the connection object. This gives you some control over slow queries.

The command object basically gives you a way to pre-define a query, and the attributes for it. There is another feature of the Command object that we do not cover here, the Parameters collection. If the command object will be calling a stored procedure that requires parameters, or a parameterized query, you can set the parameters by creating parameter objects and adding them to the parameters collection of the command object. It is up to the reader to research parameters and their use.

The code to create, and persist the command at the session level is put into the Session_OnStart event handler in the global.asa file. The code to create our command object looks like this:

For the above code segment, remember again that it is in the global.asa file, in the Session_OnStart subroutine.

You may see now, how multiple command objects could be created for various frequently used queries. Then when you needed to execute a given pre-defined query, you could just open a recordset object using the command object for that query.

Now that our command object has been created, lets look at the properties for it. The table below shows the actual properties for the Session("CMD") object in this tutorial's ASP application. As with the connection object, we have specified only a few properties ourselves. The rest of theses attributes are set to their default values, or they were populated by ADO based on the information in the connection object that we specified with the ActiveConnection property:

The Recordset Object: top

Now we get down to the meat of the matter. The recordset is where you will do most of your interaction with the data. The recordset is very similar to the recordset object from the Microsoft Data Access Objects (DAO) model. If you are familiar with DAO recordsets, you will be right at home with the ADO recordset object.

There are some differences though depending on how you create the recordset. Two options that can have a great deal to do with your success are the CursorType and LockType properties. In the code segment below, we specify these properties in the Open method of the recordset. You could also specify them using the actual properties before you used the Open method. I will leave it up to you to review the ADO documentation for the various options. I generally use a static cursor type (adOpenStatic, or 3) and an optimistic immediate lock type (adLockOptimistic, or 3).

The recordset is basically a 'cursor'. You can use standard move type methods (MovePrevious, MoveNext, MoveFirst, MoveLast) as well as absolute positioning by a records ordinal value or by 'pages'. A page is a determined number of records in the recordset. The 'page' size (in number of records per page) is set using the 'PageSize' property of the record set. 'PageCount' will return the number of pages (that are 'PageSize' records long) that exist in the recordset. Then you can use the AbsolutePage property to set the current page of the recordset.

You should review the ADO documentation for the recordset object, or any of the above objects for that matter, to gain a better understanding of the objects properties, methods, and capabilities.

The code to create, and persist the recordset at the session level is put into the Session_OnStart event handler in the global.asa file. The code to create our recordset object looks like this:

For the above code segment, remember again that it is in the global.asa file, in the Session_OnStart subroutine.

Now that our recordset object has been created, lets look at the properties for it. The table below shows the actual properties for the Session("DATA") object in this tutorial's ASP application. As with the connection and command objects, we have specified only a few properties ourselves. The rest of theses attributes are set to their default values, or they were populated by ADO based on the information in the command object that we specified when we opened the recordset.

Putting it All Together: top

I know you are anxious to actually work with the data, but before we can do that, there is one more thing we have to do. We have been storing all of our objects at the session level. When the user requests an ASP page, we need to retrieve those objects from the session so that we can use them within a page. We do this by declaring some local variables, and then setting them equal to the session level objects. Here is the code that we use in this page.

I think we have had enough of creating objects, lets actually talk to the database! The steps that we took above have left us with a recordset that contains the contents of the query. We can use some quick VBScript code to show the contents of that query in an HTML table.

I have already developed an ASP function to do this. The function is stored in the datautils.asp include file.

The table that is produced by the Data2HTML routine is below. To see the actual code, click here.:
Note: If you have been playing with customquery.asp you should reset the query now

ERROR DISPLAYING DATA TABLE A valid ADO recordset must be passed to the Data2HTML routine.

To wrap it all up in a pretty little package, I have created a separate ASP page that allows you to enter a custom query to run against the flash card database. The customquery.asp page takes the concepts that we have outlined in this page, and puts them to a better use. Check it out, I am sure you will get a kick out of it!

CLICK HERE TO RUN THE CUSTOMQUERY.ASP PAGE

Developed By

View code for....
this page - global.asa - ncheader.asp - adovbs.asp - datautils.asp