So far we've looked at the discrete pieces of an ADO query, including how to create a Connection object, how to use the Connection to execute a query, and how to iterate through the records in a Recordset. As yet, though, we haven't looked at an integrated sample.
The sample that follows is intended to illustrate how to use the Connection and Recordset objects to execute a query and produce a result set that spans across multiple pages. For an example that illustrates the same effect using the Query and Utility objects, see the samples that accompany the IIS.
Here's the HTML page for our example, ado_sample.htm:
Here's the HTML source of the Paged ADO search page, containing a <FORM> on which the query will based:
<HTML>
<HEAD><TITLE>Paged ADO Search Sample</TITLE></HEAD>
<BODY>
<H2>Paged ADO Search Sample</H2>
<P>This sample illustrates how to execute a query against
Index Server using ADO and return the results as separate pages.</P>
<FORM method="POST" action="ado_sample.asp">
<TABLE border="0" width="100%">
<TR>
<TD width="15%">Field:</TD>
<TD width="85%"><P>
<SELECT name="Where" size="1">
<OPTION selected value="Content">Characterization</OPTION>
<OPTION value="Create">Created</OPTION>
<OPTION value="DocAuthor">Document Author</OPTION>
<OPTION value="Directory">Directory</OPTION>
<OPTION value="FileName">Filename</OPTION>
<OPTION value="Path">Filepath</OPTION>
<OPTION value="Size">Size</OPTION>
<OPTION value="vPath">Virtual Path</OPTION>
</SELECT></P>
</TD>
</TR>
<TR>
<TD width="15%">Operator</TD>
<TD width="85%">
<SELECT name="Operator" size="1">
<OPTION value="=">=</OPTION>
<OPTION value=">">></OPTION>
<OPTION value="<"><</OPTION>
<OPTION value="!=">!=</OPTION>
<OPTION value="<="><=</OPTION>
<option value=">=">>=</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TD width="15%">Value:</TD>
<TD width="85%"><input type="text" name="Criteria" size="41"></TD>
</TR>
</TABLE>
<TABLE border="0" width="100%">
<TR>
<TD width="21%">Scope:</TD>
<TD width="79%"><input type="text" name="Scope" size="37"></TD>
</TR>
<TR>
<TD width="21%">Search Type:</TD>
<TD width="79%"><input type="radio" value="Shallow" checked name="Depth">Shallow</TD>
</TR>
<TR>
<TD width="21%"></TD>
<TD width="79%"><input type="radio" value="Deep" name="Depth">Deep</TD>
</TR>
</TABLE>
<TABLE border="0">
<TR>
<TD align="center"><input type="submit" value="Execute Query" name="Action"></TD>
<TD align="center"><input type="reset" value="Reset" name="Action"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
The page to which the <FORM> in the HTML file we just looked at is posted is somewhat complex. We'll go through it step by step. The first part of the target page defines a few ADO constants required by our code that will call the Recordset object's Open method later in the page:
<%
Const adOpenKeyset = 1
Const adLockReadOnly = 1
Dim strQuery 'to hold our query string
...Next, we determine whether the user clicked the "New Query" button. You won't find this button anywhere in the HTML page we looked at earlier. That's because we define it later within this page. That's right. After the HTML page above posts its data to this page, the user will get back a page that contains a "New Query" button. A click of this button will cause this page to post to itself. And when they do finally get here from clicking the "New Query" button, we simply redirect them to the original HTML page:
...
If Request("Action") = "New Query" Then
Response.Redirect("ado_sample.htm")
Response.End
End If
...
Otherwise, we can proceed to build a query that reflects what the user entered into the HTML file. This input can come from one of two places: the .HTM file, or this file itself. This file takes the form fields in the .HTM file and reproduces them as a group of hidden fields. This ensures that regardless of whether we're on the first or last page of our result set, we'll always be able to reconstitute the query with which the search originated. We've included a function called BuildQuery() (defined at the end of our ASP), which parses user input into an SQL query. Here's the code of that function:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Function BuildQuery()
SQL = "SELECT Filename, Size, Vpath, Path, Write, Characterization FROM "
If Request("Scope") = "" Then
SQL = SQL & "SCOPE() "
Else
SQL = SQL & "SCOPE('"
If Request("Depth") = "Shallow" Then
SQL = SQL & "SHALLOW TRAVERSAL OF " & """" & Request("Scope")
SQL = SQL & """" & "'" & ")"
Else
SQL = SQL & "DEEP TRAVERSAL OF " & """" & Request("Scope")
SQL = SQL & """" & "'" & ")"
End If
End if
If Request("WHERE") = "Content" Then
SQL = SQL & " WHERE CONTAINS(" & "'" & Request("Criteria") & "'" & ") > 0"
BuildQuery = SQL
ElseIf Request("WHERE") = "Size" Then
SQL = SQL & " WHERE " & Request("Where") & Request("Operator")
SQL = SQL & Request("Criteria")
BuildQuery = SQL
Else
SQL = SQL & " WHERE " & Request("Where") & Request("Operator")
SQL = SQL & " '" & Request("Criteria") & "'"
BuildQuery = SQL
End If
End Function
</SCRIPT> Back in the main body of the page, our code can use this function, and check that it was successful:
...
'Build up the query string
strQuery = BuildQuery()
If strQuery = "" Then
Response.Redirect("ado_sample.htm")
Response.End
End If
...
If we successfully built a query, it's time to create a Connection object, and to execute the query against that Connection using the Recordset object's Open method:
...
'Create a connection object to execute the query
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "provider=msidxs"
objConn.Open
Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open Query, objConn, adOpenKeyset,adLockReadOnly
If objRS.EOF Then
Response.Write("No records found!")
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Response.End
End If
...
If the Recordset we get back is not empty, the next step is to initialize the Recordset object's PageSize property. This essentially dictates that only five records from this Recordset can be examined at a time. When you reach the fifth record, the Recordset's EOF property will evaluate to True.
And here's where things get tricky, because this is where the paging mechanism comes into play. In the ASP, we determine whether there exists, in the page that posted to this one, a Request field named Scroll. If it does not exist, we can safely conclude that the user posted to this page from our HTML file, and that we should begin with Page 1 of our query. If such a field does exist, however, it means that this page posted to itself—and that we're in the middle of our search results. You'll see where it is defined later in our page.
So we use the value of Scroll to keep track of our position in the query results set. Later in the page we'll define buttons named Scroll, which we'll initialize to contain the word "Page" followed by a number that indicates which page we're on. Identifying that page is just a matter of using the Mid function to parse the value of Scroll:
...
'Set the page number - each page holds five records
objRS.PageSize = 5
Scroll = Request("Scroll")
If Scroll <> "" Then
Page = mid(Scroll, 5)
If Page < 1 Then Page = 1
Else
Page = 1
End If
objRS.AbsolutePage = Page
%>
...
Having determined what page we're on, the last line of the code above sets the Recordset object's AbsolutePage property to contain that page number value. When we set this property, we're effectively instructing the Recordset as to where to begin counting records in the Recordset. It does this by multiplying the value of AbsolutePage by the value of PageSize that we set previously.
Now, it's time to fire a page at the user. This is the HTML that creates the visible part, which consists primarily of a table displaying the search results:
...
<HTML>
<HEAD><TITLE>Paged ADO Example</TITLE></HEAD>
<BODY>
<H3>Your query returned the following results:</H3>
<TABLE border="0" width="100%" height="66">
<% RowCount = objRS.PageSize %>
<% Do While Not objRS.EOF And RowCount > 0 %>
<TR>
<TD width="20%" align="right"><B>Virtual Path:</B></TD>
<TD width="80%"><%= objRS("vPath")%></TD>
</TR>
<TR>
<TD width="20%" align="right"><b><strong>Physical Path:</B></TD>
<TD width="80%"><%= objRS("Path")%></TD>
</TR>
<TR>
<TD width="20%" align="right"><B><strong>Filename:</B></TD>
<TD width="80%"><%= objRS("Filename")%></TD>
</TR>
<TR>
<TD width="20%" align="right"><B><strong>Size:</B></TD>
<TD width="80%"><%= objRS("Size") & " bytes"%></TD>
</TR>
<TR>
<TD width="20%" align="right"><B><strong>Last Modified:</B></tD>
<TD width="80%"><%= objRS("Write")%></TD>
</TR>
<TR>
<TD width="20%" align="right"><B><strong>Excerpt:</B></TD>
<TD width="80%"><%= objRS("Characterization")%></TD>
</TR>
<% RowCount = RowCount - 1 %>
<% objRS.MoveNext %>
<% Loop %>
</TABLE>
<% Set objRS = Nothing %>
<% objConn.Close %>
<% Set objConn = Nothing %>
Before we go on, let's just reinforce what you just read. First, within a Do While loop, we initialized a RowCount variable to the Recordset's PageSize property, and with each iteration through the Recordset, we decremented this variable. The sole purpose of doing this is so that we know whether we've reached the end of our scrolling results. Think of it this way: given that RowCount is originally initialized to the Recordset's PageSize, if we run out of data before RowCount reaches 0 that means we've reached the end, not just of one of the Recordset's pages, but the end of the entire Recordset itself.
Now, notice how we use the value of RowCount below. If the Page variable is greater than 1, we conclude we need a "previous page" button. If RowCount is equal to zero, we conclude that we haven't reached the end of our Recordset, and that we need a "next page" button. Whether there's a "next page" button, a "previous page" button or both, we give these buttons the name Scroll, and we assign a value to them. The value consists of the word "Page", followed by the number that designates the previous or next page, respectively. When the user clicks one of these buttons to post this page to itself we parse the value of Scroll (as we saw above) to determine where we are in the Recordset, and to determine what the new value of these buttons should be:
...
<FORM METHOD="POST" ACTION="ADO_SAMPLE.ASP">
<INPUT TYPE="SUBMIT" NAME="ACTION" VALUE="New Query">
<INPUT TYPE="HIDDEN" NAME="Scope" VALUE="<%=Request("Scope")%>">
<INPUT TYPE="HIDDEN" NAME="Depth" VALUE="<%=Request("Depth")%>">
<INPUT TYPE="HIDDEN" NAME="Criteria" VALUE="<%=Request("Criteria")%>">
<INPUT TYPE="HIDDEN" NAME="Operator" VALUE="<%=Request("Operator")%>">
<INPUT TYPE="HIDDEN" NAME="Where" VALUE="<%=Request("Where")%>">
<% If Page > 1 Then %>
<INPUT TYPE="SUBMIT" NAME="Scroll" VALUE="<%="Page " & Page - 1 %>">
<% End If %>
<% If RowCount = 0 Then %>
<INPUT TYPE="SUBMIT" NAME="Scroll" VALUE="<%="Page " & Page + 1 %>">
<% End If %>
</FORM>
</BODY>
</HTML>Here's the results of the page you see above in action. With the values for the query we entered into the HTML form page earlier in this section, we get the following results:
At the bottom of this page are two buttons—the "New Query" button we mentioned earlier, and the "next page" button:
Clicking the "Page 2" button displays the next five matching records, then adds both "next page" and "previous page" buttons:
Finally, clicking the "Page 3" button takes us to the end of the recordset, and only the "New Query" and "Page 2" buttons are shown. And clicking the "New Query" page button at any point simply takes us back to the original HTML query page:
One final note: You may wonder why we store the elements of our query as distinct hidden HTML field controls in the page, instead of generating the appropriate SQL statement once and then passing the entire SQL statement in a hidden field from page to page. The answer lies in the nature of HTML and of the SQL itself.
When you design SQL queries to be executed against Index Server, they're likely to contain a large and varied combination of single and double quotation marks, as well as parameters. When you store such a statement in a hidden field, the browser often doesn't know what to make of the use of quotation marks in the statement. Consequently, you're likely to end up with a stray bracket or quotation mark displayed on your page.