Article
Build a FAQ with ASP and MS Access
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 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...