By: Kevin Yank

October 11th, 2001

Reader Rating: 8.5

All things considered, ASP is a fairly easy technology to use. Compared to some of the other products out there that let you develop dynamic Web sites with server-side scripting, there is relatively little to daunt the ASP newbie. It's easy to set up, the commands are simple, and the concepts involved in developing a run-of-the-mill ASP Website are relatively simple to grasp. Nevertheless, it has always been frustratingly difficult for ASP beginners to get the information they need to build a database-driven Website using ASP unless they could afford to buy (and spend a month reading) a great, big book on the subject. With this article, I aim to change all that!

Although not ideal for Websites with more than a little traffic, Microsoft Access is a nice database for learning the basics of database-driven Web development with ASP. In this article, I'll demonstrate how to access, retrieve, and update information stored in an Access database by using ASP to build a simple Web database application. The example we shall study will be a Web-based Frequently Asked Questions (FAQ) list. Through this case study, I'll show not only the immense convenience that comes with using a database to build a Web app like this, but also a few nifty tricks that having a database backend makes possible.

Before we get up to our necks in code, let me tell you what I expect from you. This article will assume that you have an understanding of the basics of both ASP and Microsoft Access. If you're new to ASP, you're in luck; I've already written a great series of articles that will bring you up to speed in no time. Begin with Getting Started with ASP [1], then read ASP Language Basics [2], Handling Submitted Data with ASP [3], and finally ASP Sessions and Applications [4], then you'll be ready to tackle the material presented here. For those of you who have never worked work MS Access, a couple of good tutorials to get you up and running quickly are First Steps with Access [5] and Relational databases [6], both by Helen Bradley. With those feathers securely in your cap, we're ready to get started. Let's go!

The FAQ Database

First thing's first. Before we build an ASP-based page to display the contents of a FAQ database, we need to build that database. For this application, we'll only need a simple one-table database. Open MS Access and create a new, blank database called faq.mdb. Create a single table called FAQ, with the columns shown here:

Once you've created the table, fill in a few entries so that you have something for your ASP scripts to work with:

Notice that the Views column should always be set to zero for new entries, to indicate that they have yet to be viewed. To make this easy, be sure to set that column to be Required with a Default value of 0. You can also change the Question column from Text to the Memo data type if you find you need questions longer than the 255 character limit imposed by Text (Memo allows up to 65,535).

That's all there really is to the database behind our simple FAQ system! If you plan to update the database often, you'll probably want to set up a form in Access for editing your FAQs, but that is beyond the scope of this article, and there's plenty of information in the MS Access help file on how to create forms.

To put your database online, you need to copy the faq.mdb file to your Web server (unless of course you're using your own computer as your ASP server), and put it someplace where your ASP scripts will be able to access it. Do not put the file in the same directory structure as your Web site's files, however, or visitors to your site will be able to download the entire database just as they would any other file on your site. In this example, it wouldn't matter much since we'll be making all of the database's contents visible through ASP, but if you used a database like this to store usernames and passwords, or private information about visitors to your site (e.g. names, email addresses, etc.), you would not want your database file to be downloadable! If you need help deciding where to put your database file on your server, ask your Web host for advice.

Now that you've created your database and placed it online, you're ready to write some ASP scripts to use it!

Retrieving Access Data with ASP

ASP is a framework designed to combine the simplicity of scripting languages such as VBScript and the power of object libraries normally used by full-fledged Windows applications to build powerful, dynamic Web applications. The ActiveX Data Objects (ADO) library provides ASP with the functionality required to interact with most database servers, Microsoft Access being one of them.

In previous articles of this series, we have seen that ASP provides several objects (Request, Response, Server, Application, and Session) to accomplish the basic tasks required of dynamic Web pages, such as form processing and session tracking. The ADO library, which comes with all current ASP servers, adds to that selection of objects to provide support for database access functionality in ASP. Learning how to use databases with ASP basically means learning about the objects in the ADO library.

The first and most basic thing we need to be able to do if we want to make our FAQ database available on the Web is to retrieve the contents of the database for display. Whenever you retrieve database records with ADO, you need a Recordset. Recordset is one of those new objects that ADO provides, and our first task is to create one. ASP makes this quite simple:

Dim rsFAQ  
Set rsFAQ = Server.CreateObject("ADODB.Recordset")

The first line above creates a variable (rsFAQ) to store our new Recordset object. The second line creates a new Recordset using the Server object's CreateObject method, then sets the rsFAQ variable so that it refers to this new object. Server.CreateObject is the standard method to create any object supported by ASP or its libraries. In this case, we want to create an instance of the Recordset object in the ADO library. All ADO objects must be preceded by the ADODB prefix to let ASP know which library you are referring to, so the argument "ADODB.Recordset" simply indicates that we wish to create an ADO Recordset object. The Set keyword is necessary to assign an object reference to the rsFAQ variable, as opposed to assigning it a simple VBScript value.

Once you've created a Recordset, you can fill it with records from the database with its Open method. In this basic case, Open takes two parameters:

• the table name we want to fetch the records from, and
• the connection string for the database.

Now, the name of the table is simply "FAQ", the name we gave to the table in Access. The connection string is a slightly more complex matter. Since the ADO library is capable of connecting to a great many database servers and a number of other data sources, the string must tell our Recordset not only where to find the database (the path and file name) but also how to read the database, by giving the name of its database provider.

Jet OLE DB is the database provider for Microsoft Access, and as of this writing the current version is 4.0. To connect to an Access database, therefore, the connection string must specify the provider as Microsoft.Jet.OLEDB.4.0. Thus, if the database file (faq.mdb) is stored in D:\faq on your Web server, your connection string should be:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\faq\faq.mdb

To give you an idea, here's what a connection string for the more advanced MS SQL Server looks like:

Provider=SQLOLEDB; Data Source=servername; Initial Catalog=databasename;  
User Id=username; Password=password

With all this in mind, here is the complete code to fetch the contents of the FAQ table into an ADO Resultset:

Dim rsFAQ   ' A Resultset for our FAQ  
Dim strConn ' The database connection string  
 
rsFAQ = Server.CreateObject("ADODB.Recordset")  
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\faq\faq.mdb"  
 
rsFAQ.Open "FAQ", strConn

Since it's more that likely that you'll need to use the same connection string in several ASP pages on your site, it is common practice to place your connection string in an application variable in your global.asa file as follows (see ASP Sessions and Applications [7] for more on application variables):

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">  
 
Sub Application_OnStart()  
 
 Dim strConn  
 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\faq\faq.mdb"  
 Application("strConn") = strConn  
 
End Sub  
 
</SCRIPT>

The code to retrieve the contents of the FAQ table is thus greatly simplified:

Dim rsFAQ   ' A Resultset for our FAQ  
rsFAQ = Server.CreateObject("ADODB.Recordset")  
 
rsFAQ.Open "FAQ", Application("strConn")

Now that we've learned how to fetch information from an Access database into ASP, we need to learn how to use it.

Displaying a Resultset

Once filled with records from a database, a Resultset object's job is to provide access to those records. Like a database table, result sets may be thought of as tables, with each row containing the data for one database record. In our example, we have just filled the rsFAQ Recordset with the contents of the FAQ table. Thus, if our table had, say, four entries, rsFAQ should now contain four rows. Similarly, since our FAQ table had four fields (ID, Question, Answer, and Views), our Recordset will have those same four fields.

A Recordset, once opened, keeps track of the current record. To begin with, the current record is the first record in the set. By calling the MoveNext method of the Recordset object, you can move forward to the next record in the set, if any. If you call MoveNext when the current record is the last record in the set, the EOF property of the Recordset will become true (it's false the rest of the time). Thus, to display the full contents of a Recordset, you can simply use a Do-While loop as follows (see ASP Language Basics [8] for more on Do-While loops):

Do While Not rsFAQ.EOF  
 ' ... display the current record ...  
 rsFAQ.MoveNext  
Loop

To allow for the possibility that the record set may be empty, which will happen if your FAQ table happens to be empty, you can also use the BOF property of the Recordset. Where EOF is true when you reach the end of the record set, BOF is true when you're at the beginning of the record set. If, following the Do-While loop above, BOF is true, then you know that you're at both the beginning and end of the result set, which can only happen if the result set is empty:

Do While Not rsFAQ.EOF  
 ' ... display the current record ...  
 rsFAQ.MoveNext  
Loop  
If rsFAQ.BOF Then  
 Response.Write "<p>No FAQs in the database!</p>"  
End If

To display each of the fields of the current record is very simple. Simply treat the Recordset object as a collection. For example, the Question field of the current record is accessible as rsFAQ("Question"). Thus, the finished code to display all of the FAQs in the result set:

Do While Not rsFAQ.EOF  
 Response.Write "<li><b>" & rsFAQ("Question") & "</b>"  
 Response.Write "<p>" & rsFAQ("Answer") & "</p></li>"  
 rsFAQ.MoveNext  
Loop  
If rsFAQ.BOF Then  
 Response.Write "<p>No FAQs in the database!</p>"  
End If

As soon as you're done with a Resultset object, you should always Close it:

rsFAQ.Close

This frees up the connection to the database for use by another script. Since these connections, especially with Access as a database, may be in short supply, you should not Open a Resultset until you need it, and Close it as soon as you're done.

The code for our completed FAQ listing page is as follows (don't forget to set the strConn application variable in your global.asa!):

1   <% Option Explicit %>  
2   <html>  
3   <head>  
4   <title>Frequently Asked Questions</title>  
5   </head>    
6   <body>  
7   <h2>FAQ</h2>  
8   <ol>  
9   <%  
10  Dim rsFAQ  
11  Set rsFAQ = Server.CreateObject("ADODB.Recordset")  
12  rsFAQ.Open "FAQ", Application("strConn")  
13  
14  Do While Not rsFAQ.EOF  
15    Response.Write "<li><b>" & rsFAQ("Question") & "</b>"  
16    Response.Write "<p>" & rsFAQ("Answer") & "</p></li>"  
17    rsFAQ.MoveNext  
18  Loop  
19  If rsFAQ.BOF Then  
20    Response.Write "<p>No FAQs in the database!</p>" & vbNewLine  
21  End If  
22  
23  rsFAQ.Close  
24  %></ol>  
25  </body>  
26  </html>

Save this file on your ASP-equipped Web server and view it in your browser. You should see something like this:

Open MS Access, make a couple of changes, upload the new faq.mdb file and refresh your browser to see the changes appear! Now anyone who can use Microsoft Access can update your FAQ page! Now if you think that's pretty neat, watch what we do next...

SQL Queries

In the previous example, we basically read an entire MS Access table into an ADO Resultset and displayed it in a Web page. This was perfectly acceptable for that example, although it may have occurred to you that we were needlessly fetching the ID and Views columns from the database. I say 'needlessly' because that information was never used in the display of the page. This represented a small, though negligible waste of memory in the execution of the ASP script.

Now, over time, it's quite likely that your FAQ page may begin to grow quite large, and you may no longer want your users to have to scroll through all the answers to questions they're not interested in to get to the one they are after. Instead, it would be nice to only display the questions on a single page, and have the text of each question link to a page that contains the answer to the particular question selected.

Now, set aside for the moment the obvious challenge of displaying a page with the correct answer in it, and think of the list of questions. The code for this page won't be all that different from our previous example, except that this time we won't be using the Answer field in our Resultset! Now, when we weren't using ID or Views it wasn't that big a deal, since those fields contained relatively short values, but the Answer field is the largest in our database! Before we go any further, we need to find a way to make our result sets more efficient.

In the language of database connoisseurs, a database request is called a query. The query we used in our previous example was as simple as they come: "FAQ" -- a request for the complete contents of a table. In most cases, however, we'll need more complex queries to get the information we need from a database.

Complex database queries in most modern databases are issued in Structured Query Language (SQL). All the cool database programmers pronounce it 'sequel', but 'ess-cue-ell' works just as well. The SQL version of our simple "FAQ" query is in fact "SELECT * FROM FAQ", which translates to select everything from the FAQ table. Now, for the version of our FAQ page that lists only the questions, we would only need the Question column. The SQL query would therefore be "SELECT Question FROM FAQ", and would produce a result set with only the Question field in each record.

All that's left is to figure out how we'll make each question link to a page with the corresponding answer only. The solution is to pass a variable in the URL for the link. That variable will contain the ID of the question that was clicked, and the ASP script that produces the page with the answer (answer.asp) will use that variable to fetch only the requested answer from the database.

Since we want to include the ID of the questions in the links on the question listing page, we'll need that field from the database as well, so our SQL query becomes "SELECT ID, Question FROM FAQ". Here's the finished code for the questions page:

1   <% Option Explicit %>    
2   <html>    
3   <head>    
4   <title>Frequently Asked Questions</title>    
5   </head>      
6   <body>    
7   <h2>FAQ</h2>    
8   <ol>    
9   <%    
10  Dim rsFAQ    
11  Set rsFAQ = Server.CreateObject("ADODB.Recordset")    
12  rsFAQ.Open "SELECT ID, Question FROM FAQ", Application("strConn")    
13    
14  Do While Not rsFAQ.EOF    
15    Response.Write "<li><b><a href=""answer.asp?ID=" & rsFAQ("ID") & _    
16                   """>"    
17    Response.Write rsFAQ("Question") & "</a></b></li>"    
18    rsFAQ.MoveNext    
19  Loop    
20  If rsFAQ.BOF Then    
21    Response.Write "<p>No FAQs in the database!</p>" & vbNewLine    
22  End If    
23    
24  rsFAQ.Close    
25  %></ol>    
26  </body>    
27  </html>

Here's what the resulting page should look like:

Each of the links on this page points to answer.asp, and passes a query string variable ID to indicate the database ID of the answer to be displayed. That variable will be available in answer.asp as Request("ID"). Using that value, we can build an SQL query that fetches only the Question and Answer fields of the particular database entry that corresponds to that ID. If the value of ID were 4, for example, the SQL query would be: "SELECT Question, Answer FROM FAQ WHERE ID=4". Thus, we can create the SQL query as follows:

Dim strSQL    
strSQL = "SELECT Question, Answer FROM FAQ WHERE ID=" & Request("ID")

Note that the underscore at the end of the second line tells VBScript that the command continues on the next line.

We now have everything we need to write answer.asp. Here it is:

1   <% Option Explicit %>    
2   <html>    
3   <head>    
4   <title>Frequently Asked Questions</title>    
5   </head>      
6   <body>    
7   <%    
8   Dim rsFAQ    
9   Dim strSQL    
10  Set rsFAQ = Server.CreateObject("ADODB.Recordset")    
11  strSQL = "SELECT Question, Answer FROM FAQ WHERE ID=" & Request("ID")    
13  rsFAQ.Open strSQL, Application("strConn")    
14  %>    
15  <p><b>Question:</b> <%=rsFAQ("Question")%></p>    
16  <p><b>Answer:</b><br><%=rsFAQ("Answer")%></p>    
17  <% rsFAQ.Close %>    
18  </body>    
19  </html>

With this script in place, let's see what happens when we click on one of the questions:

Great! Now all that's left is to track the number of times each question is viewed (remember the as yet unused Views column of our FAQ table?), and use that value to present the FAQ list in a sensible order to your visitors.

Action Queries

So far, all the SQL queries we've seen have been SELECT queries. SELECT queries are used for fetching records from the database, and since that's all we've done so far it's no surprise that that's all we've used. The Views column of our FAQ table is going to need a different type of query, however, since every time a visitor views the answer to one of the questions, we want to increment the number in that question's Views field.

The SQL query we need for this task is an UPDATE query. Here's a query that will increment the Views value for the FAQ with ID 4:

UPDATE FAQ SET Views = Views + 1 WHERE ID=4

Now, where a SELECT query will produce a Resultset, so-called action queries such as UPDATE do not. Thus, we don't use an ADO Recordset object to run UPDATE queries. Instead, we use a Connection object as follows:

Dim conFAQ    
Set conFAQ = Server.CreateObject("ADODB.Connection")    
   
conFAQ.Open Application("strConn")    
conFAQ.Execute("UPDATE FAQ SET Views = Views + 1 WHERE ID=4")    
conFAQ.Close

This code first declares the variable conFAQ, then creates a new ADO Connection object (using Server.CreateObject as usual) to store in it. Next, we call the Connection's Open method, supplying the connection string (Application("strConn")) to establish a connection to our FAQ database. Once opened in this way, a Connection object can be used to run any number of queries over a single database connection. On the final line, we use the connection's Execute method to run our UPDATE query. Just like with Resultsets, it's important to Close a Connection as soon as you're done with it, as we do on the last line of the above.

Now, in our answer.asp script, we're already using a Resultset object to run the SELECT query that fetches the question and answer for display:

11  strSQL = "SELECT Question, Answer FROM FAQ WHERE ID=" & Request("ID")    
13  rsFAQ.Open strSQL, Application("strConn")

This Resultset object actually creates its own Connection object behind the scenes to connect to the database. Now, since we'll already have established a Connection to execute our UPDATE query, it doesn't make sense to create a second connection for this query when we could simply reuse the first connection. There are two ways to do this. First, we could pass the Connection object to the Resultset's Open method instead of the connection string:

rsFAQ.Open strSQL, conFAQ

Alternatively, we can just run our SELECT query the same way we did our UPDATE query above: with the Connection's Execute method. When used to run a SELECT query, Execute returns a Resultset object, so this will save us the trouble of creating our own Resultset:

Set rsFAQ = conFAQ.Execute(strSQL)

Here's the finished code for answer.asp, which uses a single database Connection to first UPDATE the Views column of the chosen FAQ, then SELECT the Question and Answer fields for display:

1   <% Option Explicit %>    
2   <html>    
3   <head>    
4   <title>Frequently Asked Questions</title>    
5   </head>      
6   <body>    
7   <%    
8   Dim conFAQ, rsFAQ, strSQL    
9   Set conFAQ = Server.CreateObject("ADODB.Connection")    
10    
11  conFAQ.Open Application("strConn")    
12  conFAQ.Execute("UPDATE FAQ SET Views = Views + 1 WHERE ID=" & _    
13                 Request("ID"))    
14    
15  strSQL = "SELECT Question, Answer FROM FAQ WHERE ID=" & Request("ID")    
16  Set rsFAQ = conFAQ.Execute(strSQL)    
17  %>    
18  <p><b>Question:</b> <%=rsFAQ("Question")%></p>    
19  <p><b>Answer:</b><br><%=rsFAQ("Answer")%></p>    
20  <%    
21  rsFAQ.Close    
22  conFAQ.Close    
23  %>    
24  </body>    
25  </html>

From the user's perspective, this version of answer.asp will behave no differently from the old one; however, behind the scenes it will count every time a FAQ is viewed on your Website by updating the Views field in your database! Go ahead and try it. View a few of your FAQs with this new version of answer.asp, then download the faq.mdb file from your Website. Open it in MS Access and observe that the numbers in the Views column of the FAQ table have changed accordingly!

For our last trick, let's put that Views field to good use. Right now, your questions listing page (questions.asp) lists the FAQs in the same order in which they were added to the database. Instead, wouldn't it make sense to sort them according to the number of views they have received? That way, the most popular and useful questions can appear at the top of the list, so your users can get to them quickly! To do this, all we need to do is change the SELECT query that we use to fetch the questions and answers for display:

SELECT ID, Question FROM FAQ ORDER BY Views DESC

The ORDER BY Views DESC portion that we've added here tells Access to sort the entries in the FAQ table according to their Views field, and to send the results in descending order (from the most views to the least). That's all there is to it! For your reference, here's the updated code for questions.asp:

1   <% Option Explicit %>    
2   <html>    
3   <head>    
4   <title>Frequently Asked Questions</title>    
5   </head>      
6   <body>    
7   <h2>FAQ</h2>    
8   <ol>    
9   <%    
10  Dim rsFAQ    
11  Set rsFAQ = Server.CreateObject("ADODB.Recordset")    
12  rsFAQ.Open "SELECT ID, Question FROM FAQ ORDER BY Views DESC", _    
13             Application("strConn")    
14    
15  Do While Not rsFAQ.EOF    
16    Response.Write "<li><b><a href=""answer.asp?ID=" & rsFAQ("ID") & _    
17                   """>"    
18    Response.Write rsFAQ("Question") & "</a></b></li>"    
19    rsFAQ.MoveNext    
20  Loop    
21  If rsFAQ.BOF Then    
22    Response.Write "<p>No FAQs in the database!</p>" & vbNewLine    
23  End If    
24    
25  rsFAQ.Close    
26  %></ol>    
27  </body>    
28  </html>

What's Wrong with Access?

Before you take your new-found ASP database skills and go implement a system such as the example I presented in this article on your own site, there is an important issue that you need to be aware of. Microsoft Access was never designed to be used a heavy-duty backend for database-driven Websites. The number of simultaneous connections that it can handle is extremely limited, so if your Website receives more than a handful of visitors at a time, Access will likely not hold up under the strain. A good discussion that covers the limitations of MS Access when used in this way is available here: 15 Seconds: The Truth About Access [9].

For the reasons explained in that discussion, you'll need to explore alternatives to Access if you want to deploy a database-driven Web application on anything larger than a personal Website. Microsoft would have you invest in their high-powered MS SQL Server, but ASP can connect to free databases such as MySQL just as easily. Fortunately, since the ADO library can be used to connect to all of these databases and more, and they all use SQL (albeit with slightly different dialects) to describe their queries, so the skills you have learned in this article are equally applicable once you move on to a more advanced database backend.

Summary and Resources for Further Reading

In this article, we explored the most basic concepts of using the ActiveX Data Objects (ADO) library in ASP to build Web pages that use a database to store the content to be displayed. For the sake of example, we used a simple Microsoft Access database to build a Frequently Asked Questions system that tracks the number of views each FAQ receives in order to list the questions according to their popularity. Practical systems like this one are in use all over the Web.

Your next steps in the realm of database-driven programming with ASP should be to gain a more complete understanding of Structured Query Language (SQL). An excellent book on the subject that covers not only the concepts you need to know and all the different query types that are available, but also explores the idiosyncrasies of the SQL dialects supported by most of the popular databases out there is Beginning SQL Programming [10] (2001, WROX Press). In addition to the wealth of knowledge you get with this book, you'll also receive a 120-day evaluation version of MS SQL Server 2000!

There's also a lot still to learn about the ADO library, the objects it contains, and their properties and methods. For a relatively thorough introduction to the ADO library and all that it can do for you, I highly recommend Chapters 8 and 9 of Professional ASP 3.0 [11] (1999, WROX Press) (see my review here [12]). For a more in-depth treatment of the subject, however, you can't go past Professional ADO 2.5 Programming [13] (2000, WROX Press). Covering every aspect of ADO, this book will tell you more about the library than you're ever likely to use. This book is definitely for the hard-core database-driven ASP programmer, but likely overkill for the average ASP developer, for whom Professional ASP 3.0 is more than enough.

[1] http://www.webmasterbase.com/article.php/371
[2] http://www.webmasterbase.com/article.php/387
[3] http://www.webmasterbase.com/article.php/420
[4] http://www.webmasterbase.com/article.php/472
[5] http://www.canadacomputes.com/v3/story/1,1017,3227,00.html
[6] http://www.canadacomputes.com/v3/story/1,1017,7190,00.html
[7] http://www.webmasterbase.com/article/472
[8] http://www.webmasterbase.com/article/387
[9] http://www.15seconds.com/issue/010514.htm
[10] http://amazon.com/exec/obidos/ASIN/1861001800/webmasterresou05/
[11] http://amazon.com/exec/obidos/ASIN/1861002610/webmasterresou05/
[12] http://www.webmasterbase.com/article/419
[13] http://amazon.com/exec/obidos/ASIN/1861002750/webmasterresou05/