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, then read ASP Language Basics, Handling Submitted Data with ASP, and finally ASP Sessions and Applications, 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 and Relational databases, 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:
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
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 (
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 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.
A database provider is to ADO as a device driver is to an operating system. It’s a piece of software that allows ADO to communicate with a given type of database in a standard way. ADO comes with built-in providers for Access, SQL Server, Oracle, and ODBC database servers, among others.
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:faqfaq.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
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:faqfaq.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 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:faqfaq.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
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 (
Recordset will have those same four fields.
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 for more on
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
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
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
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
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…
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
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
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
Request("ID"). Using that value, we can build an SQL query that fetches only the
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.
So far, all the SQL queries we’ve seen have been
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
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
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
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
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")
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
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
Execute method. When used to run a
Execute returns a
Resultset object, so this will save us the trouble of creating our own
Set rsFAQ = conFAQ.Execute(strSQL)
Here’s the finished code for answer.asp, which uses a single database
Connection to first
Views column of the chosen FAQ, then
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
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
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.
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 (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 (1999, WROX Press) (see my review here). For a more in-depth treatment of the subject, however, you can’t go past Professional ADO 2.5 Programming (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.