Professional ADO RDS Programming with ASP by Matt Brown, Charles Caison, John Papa, Peter DeBetta, Eric Wilson Published by Wrox Press 1999; $49.99US; ISBN: 1861001649 500 pages Sample Chapter 6: Building on the Recordset Foundation Copyright 1999. Wrox Press, reprinted with permission. The text format was modified to fit the format of this web site.

Related Links
• In-depth review of the book

Filtering

One of the coolest features of the ADO recordset is the Filter property. We use the Filter property to narrow down the information the we can see in the recordset – according to the criteria we specify.

This is especially nice when there is a lot of data being returned. For instance, you could filter on all Last Names with the name of 'Smith', or all records with a State = 'NC'. You can filter your recordset to view just the information you want, without destroying the rest of the information. All the information is still there in the recordset – it's just hidden from view until you clear the filter.

By applying the filter, you get a subset of all the rows – only the filtered data (the subset) is displayed. So you can walk through the recordset from the first record to the last record and see only the records that match your criteria. If no rows match the filter then EOF and BOF will be true; otherwise you should be positioned at the first row in the record. You can use the MoveNext, MovePrevious, MoveFirst and MoveLast methods to navigate through the filter recordset. Once you're are done viewing your records, you need to set the filter to adFilterNone. This will clear the filter and display all the records. We'll have a look at an example in a moment.

The syntax of the Filter property is as follows:

objRS.Filter = Value

Value can take the form of a database query expression, or it can be one of the predefined constants. Let's take a closer look. Value can be any of the following:

• A SQL WHERE statement. You can supply what is essentially a SQL WHERE clause (contained within a pair of double-quotes), using field names from the recordset, logical operators (<, <=, >, >=, =, <>, LIKE), and values (dates, strings, wildcards (* and % ), etc. when used with LIKE). We don't actually use the word WHERE – as you'll see in the code sample below.
• A bookmark array: You can supply an array of bookmarks – only the bookmarked records will be available in the recordset.
• A FilterGroupEnum value. These constants are described in the chart below:

An Example

This example takes the contents of the authors table of the pubs database, and filters the contents by the first letter of the author's last name. We start by displaying links that will eventually filter our recordset by initial of last name:

'Write out the alphabet links
for intLetter = 65 to 90
  Response.Write "<a href=""Filter.asp?letter=" & _
    Chr(intLetter) & """>" & _
    Chr(intLetter) & "</A>&nbsp;&nbsp;"
next
Response.Write "<BR>"

Now we'll open the connection to the pubs database, and query the recordset objRS against the authors table:

if not isObject(Session("rs")) then
  set objRS = CreateObject("ADODB.Recordset")
  strSQL = "select * from authors" 'Initialize the string
  objRS.ActiveConnection = "Provider=MSDASQL;" & _
    "User ID=sa;" & _
    "Data Source=winbook;" & _
    "Initial Catalog=pubs" 'Connect to database

objRS.CursorType = 3 'adOpenStatic 'Create the record set object
objRS.Open strSQL 'Open and filter

Notice that the script only needs to go to the database once. The first time the user uses the page, he'll see the filtered contents of the recordset, showing only authors whose surnames begin with "A":

Display("A") 'Display the A names first
set Session("rs") = objRS

We'll have a look at the Display procedure in a moment. On subsequent queries (when the session object Session("rs") is non-null) we display the recordset filtered against a letter of the user's choosing – contained in the variable letter:

else
  set objRS = Session("rs")
  strLetter = Request("letter")
  Display(strLetter)
end if

OK. Let's have a look at the Display procedure. All we do is set the filter (using a WHERE-type SQL query), and display the contents of the filtered recordset:

sub Display( strLetterToFilter )
  'Set the filter
  objRS.Filter = "au_lname Like '" & strLetterToFilter & "%'"
  Response.Write "<H2>" & strLetterToFilter & "<H2>"
  Response.Write "<TABLE Border=1><THEAD>" & _
    "<TH>Name</TH><TH>Phone Number</TH><TH>Address</TH>" & _
    "<TBODY>"

  do while not objRS.EOF
    Response.Write "<TR><TD>" & _
      objRS("au_lname") & ",&nbsp;" & objRS("au_fname") & _
      "</TD><TD>" & _
      objRS("phone") & _
      "</TD><TD>" & _
      objRS("address") & _
      "</TD><TD></TR>"
    objRS.MoveNext
  loop
  Response.Write "</TABLE>"

Then we tidy up, ready for the next query: we turn off the filter using the line objRS.Filter = 0 (note that 0 = adFilterNone) and move the record pointer back to the first record of the un-filtered recordset:

  'Turn off the filter
  objRS.Filter = 0 'Turn Off Filter
  'Move to the first record
  objRS.MoveFirst
end sub
%>
</BODY></HTML>

Here's a sample of what it all looks like. As you can see, filtering is perfect for an employee directory page, or a business directory: