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