RSS
| << 13.3.4- Finding Records | Chapter13 | 13.3.6- The Fields Collection >> |
Filtering Records
Filtering involves identifying all the records in the recordset that meet a certain specified criterion. While you'd expect filtering to be quite similar to finding, ADO considers these two tasks to be very different. Accordingly, ADO implements these two concepts very differently:
- Finding a record involves searching the recordset until we find one record that matches what we're looking for – then pointing the cursor to that record. Therefore, Find is a method, whose job is to point the cursor at a specific record
- Filtering involves identifying every record that matches what we're looking for. Therefore, Filter is a property, which specifies exactly which records are visible to our code and which are invisible
That's right – by setting the Recordset object's Filter property, you can hide some of the records in the recordset and ensure that the only visible records are the records that satisfy the criterion.
For example, consider a recordset of movie data from the AllMovies table. By default, the Filter property is set to the ADO constant adFilterNone, which means that the filter is turned off – no records are filtered out, and all records in the recordset are visible. Now, we can implement a filter by setting the recordset's Filter property with our criterion. As an example, let's filter out all the records of films that weren't directed by Quentin Tarantino:
objRS.Filter = "Director = 'Quentin Tarantino'"
Now, the only visible records are those that were directed by Mr. Tarantino. Now, when you use methods like MoveNext and MovePrevious, you'll be moving between the records that aren't hidden by the filter.
Note that the other records in the objRS recordset haven't been deleted from the recordset – they're still there. It's just that they're hidden. You can reveal them again by removing the filter:
objRS.Filter = adFilterNone
The filter can also take the form of an array of bookmarks, or one of the following ADO constants:
|
Constant |
Meaning |
|
adFilterNone |
Removes the current filter and restores all records to view. |
|
adFilterPendingRecords |
Shows only those records that have changed but whose changes have yet to be sent to the data store. This is only applicable in batch update mode (more on this in the next chapter ). |
|
adFilterAffectedRecords |
Shows only records affected by the last Delete, Resync, UpdateBatch or CancelBatch call. (We'll discuss these calls in the next chapter.) |
|
adFilterFetchedRecords |
Shows the records from the last call to retrieve records from the data store. |
Try It Out – Filtering Records
Let's have a look at an example. We're going to break down our list of films, categorizing them by the first letter of the film title. In order to do this, we're going to query the data store just once. Then, for each letter in the alphabet, we'll use the appropriate filter to hide all records except those whose film title begins with that letter. If we do that 26 times, we'll have covered every letter in the alphabet!
1. Once again, we're going to use the Movies database, so make sure you've set it up using the Movie2000.mdb or Movie2000.mdf database file as described in Chapter 12 .
2. And again, we'll need the connection string details, so ensure that the DataStore.asp file (from Chapter 12) is saved into the folder \inetpub\wwwroot\BegASPFiles.
3. Create a new file, called Filter.asp, and add the following code to it:
<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>Filtering the Recordset</TITLE>
</HEAD>
<BODY>
<%
Dim objRS, intLetter, strChar, strCriteria
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "AllMovies", strConnect, adOpenStatic, adLockReadOnly, adCmdTable
For intLetter = 1 To 26
strChar = Chr(intLetter+64)
strCriteria = "Title LIKE '" & strChar & "*'"
objRS.Filter = strCriteria
If Not objRS.EOF Then
Response.Write "<H2>" & strChar & "</H2>" & _
"<TABLE BORDER=1><TR><TD><B>Film</B></TD><TD><B>Director</B></TD></TR>"
While Not objRS.EOF
Response.Write "<TR><TD>" & objRS("Title") & "</TD>" & _
"<TD>" & objRS("Director") & "</TD></TR>"
objRS.MoveNext
Wend
Response.Write "</TABLE>"
End If
Next
objRS.Close
Set objRS = Nothing
%>
</BODY>
</HTML>
4. Save the Filter.asp file into the \inetpub\wwwroot\BegASPFiles folder. Now browse to that page, to see the categorization of films.
|
|
As you can see, we get a number of tables – but they're all populated from the same recordset! For each table, all we've done is hide the records that we don't want, and put the rest into the table. Let's have a look at how easy it is.
You can see that the filter has categorized the records in the recordset.
How It Works
We won't go through the connection string and recordset creation code again – you should be familiar with that by now. Suffice it to say that we only use one Recordset object in this example, and it only queries the database once. That one query gives us enough data to populate all of these tables.
All the interesting action occurs within the For … Next loop. We're going to loop through the same procedure 26 times, once for each letter of the alphabet. We use a variant called strChar to hold one of the letters of the alphabet and then build the filter criterion from that letter:
For intLetter = 1 To 26
strChar = Chr(intLetter+64)
strCriteria = "Title LIKE '" & strChar & "*'"
Like the last example, we're using a variant called strCriteria to store the string containing our filter criteria. In this example, in each case the criteria will look something like this:
Title LIKE 'A*'
Note that, once again, we're using our wildcard character (*). This criterion will make visible all the records whose film titles begin with the letter A, and will hide all other records.
Now we apply the filter to the recordset:
objRS.Filter = strCriteria
Now we can test the results of the filter. When you apply a filter, the recordset automatically points the cursor at the first visible record in the filtered recordset. If there are no visible records in the filtered recordset, then the EOF property will be True – in that case we don't display anything. Otherwise, we display the visible contents of the recordset, by using MoveNext to step through the records as we've done in previous examples:
If Not objRS.EOF Then
Response.Write "<H2>" & strChar & "</H2>" & _
"<TABLE BORDER=1><TR><TD><B>Film<B></TD><TD>Director</TD></TR>"
While Not objRS.EOF
Response.Write "<TR><TD>" & objRS("Title") & "</TD>" & _
"<TD>" & objRS("Director") & "</TD></TR>"
objRS.MoveNext
Wend
Response.Write "</TABLE>"
End If
Having written the table, we move onto the next letter in the alphabet:
Next
Notice that we don't need to reset the filter before applying the next filter – we can just replace the old value of the Filter property with the new value.
| << 13.3.4- Finding Records | Chapter13 | 13.3.6- The Fields Collection >> |
