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