RSS
| << 13.3.3- Bookmarks | Chapter13 | 13.3.5- Filtering Records >> |
Finding Records
We've seen how we can move the cursor around the recordset using the Move, MoveNext, MoveLast, MovePrevious and MoveFirst methods. But what happens if we're stepping through a big recordset, with thousands of records, and the record we're looking for is right in the middle? Instead of iterating through using MoveNext or MovePrevious hundreds of times, wouldn't it be much easier if we could use a single instruction to take the cursor straight there?
That's exactly what the Recordset object's Find method can do for us. The Find method is a full table scan, so with large recordsets it can be rather slow – but it is functional. Its job is to move the cursor from its current position to a record that fits our description.
We'll see in the next chapter an even more effective way of retrieving information, using SQL.
The syntax for the Find method is:
objRecordset.Find Criteria, SkipRecords, SearchDirection, Start
We'll see the Find method in action shortly, but first we should explain what the parameters are for. In fact, they're fairly straightforward:
- Criteria is a string that contains a set of comparisons, which describe the record that we're looking for
- SkipRecords is the offset from the start position where the search itself should start (see also Start, below). Default is 0
- SearchDirection can be set either to adSearchForward or adSearchbackward, to specify the search direction. Default is adSearchForward
- Start is a bookmark that specifies the start position of the search. Then the parameter SkipRecords specifies the offset from this position. Default is adBookmarkCurrent (to start the search at the current record)
The first parameter, Criteria, is the most important one here, and if you've ever worked with SQL (Structured Query Language) then you'll be familiar with the way we use it. The general form of a criterion is shown below:
<recordset_field> <comparison_operator> <value>
Here, recordset_field is the name of the field that we want ADO to search against, comparison_operator is the type of search, and value is the value that we're looking for.
The comparison_operator can be one of the following:
- > to search for records greater than the value
- < for records less than the value
- = for records equal to the value
- LIKE for matching records
For example, if we want to search for a film whose title is Pulp Fiction, we could use the criterion Title = 'Pulp Fiction'. Thus, if we search the recordset objRS for this film, searching forwards from the current record, we could use a line like this:
objRS.Find "Title = 'Pulp Fiction'"
Notice that we've left out the last three arguments, since they are optional – this causes the default parameter values to be used. The default behavior is to start the search at the current record, and search forwards through the recordset.
In the example above, we're searching for a string, 'Pulp Fiction' (the comparison isn't case sensitive). In this case, we must enclose the search string in single quotation marks. You don't need to do this for fields whose data type is numeric:
objRS.Find "Price = 34.95"
And when searching for dates in an Access database, you should surround the date with # marks (single quotes for MSDE):
objRS.Find "Birthday = #10/23/98#" ' Note that you need to use single quotes
' in places of hashes in MSDE
When searching for strings you can also use the SQL keyword LIKE to specify matching records. For example, the following will find the next film in the database whose title begins with the word Pulp:
objRS.Find "Title LIKE 'Pulp*'"
We're looking for any string that begins with the characters Pulp, and then continues with any number of any other characters at all. The * character indicates that we don't mind what comes after those first specified characters – the * is known as a wildcard. If you are using a database other than Access, MSDE (or full SQL Server) then the syntax may vary slightly. For example, Oracle uses a % character as the wildcard (but other than that the method should work in the manner described).
Successful and Unsuccessful Searches
How do we know whether the search was successful? Easy – we just check the position of the cursor:
- If the search is successful then the cursor is placed at the specified record.
- If you're searching forwards and no record is found, then EOF is set to True.
- If you're searching backwards and no record is found, then BOF is set to True.
Consequently, you must be aware of the fact that the position of the cursor will change – even if the search is unsuccessful. So you might want to use a bookmark to keep a note of the current record, like this:
varTempBkMk = objRs.Bookmark ' original position
objRS.Find "Title LIKE 'Pulp*'"
If objRS.EOF Then
Response.Write "No records found - " & _
"now moving the cursor back to its original position"
objRS.Bookmark = varTempBkMk
End If
Try It Out – Finding Records
OK, let's have an example that demonstrates the Find method. We'll use a two-page example. In the first page, the user is asked to type in the name of a film director. In the second page, we'll use this information to find a film made by that director.
1. Once again, we're going to use the Movies database again, 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. Open your editor, create a new file called PromptForDirector.htm and enter the following code:
<HTML>
<HEAD>
<TITLE>Using the Find Method to find a Director's Films</TITLE>
</HEAD>
<BODY>
<H1>Who Directed What?</H1>
<FORM ACTION="FindDirector.asp" METHOD="POST" id=form1 name=form1>
Type in the name of a film director: <BR>
<INPUT TYPE="TEXT" NAME="director" SIZE=50><BR><BR>
<INPUT TYPE="submit" NAME="send" VALUE="What films did this director direct?">
</FORM>
</BODY>
</HTML>
4. Save the code for PromptForDirector.htm in your BegASPFiles folder.
5. Now create a second new file, and call it FindDirector.asp. Type in the following:
<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>Using the Find Method to find a Director's Films</TITLE>
</HEAD>
<BODY>
<%
Dim strDirector, strCriteria
strDirector = Request.Form("director")
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "AllMovies", strConnect, adOpenStatic, adLockReadOnly, adCmdTable
strCriteria = "Director='" & strDirector & "'"
objRS.Find strCriteria ' show the first relevant record
If objRS.EOF Then
Response.Write "The database does not contain any films by the director " & _
strDirector
Else
Response.Write "<H2>Directed by " & strDirector & ":</H2>" & _
"<B>Title:</B> " & objRS("Title") & "<BR>" & _
"<B>Summary:</B> " & objRS("Summary") & "<BR>" & _
"<B>Genre:</B> " & objRS("Genres") & "<BR><BR><BR>"
End If
objRS.Close
Set objRS = Nothing
%>
</BODY>
</HTML>
In this database there are no director names that contain apostrophes, such as O'Neill. If there was then this would cause an error in this script. To get around this, you would have to add a replace method to look for the single quotes and replace them with char(39) – the code for a single quote.
|
6. Save FindDirector.asp into the BegASPFiles folder. Now open PromptForDirector.htm in your browser, type in the name of a well-known film director from the 1990s, and hit the big gray button at the bottom:
|
|
|
7. The page will search for the first appropriate record, and stop.
|
|
Alternatively, if the director isn't found in the database, then you'll get the appropriate message.
|
|
How It Works
The first page, PromptForDirector.htm, is a pure HTML page that contains a form. It collects the name of a director that the user types in:
<FORM ACTION="FindDirector.asp" METHOD="POST" id=form1 name=form1>
Type in the name of a film director: <BR>
<INPUT TYPE="TEXT" NAME="director" SIZE=50><BR><BR>
<INPUT TYPE="submit" NAME="send" VALUE="What films did this director direct?">
</FORM>
We've specified the POST method in the form. So when the user clicks the submit button, the requested director's name is sent as part of the request for the FindDirector.asp page, where it finishes up in the director variable of the Request.Form collection. We capture this value in a local variable, strDirector:
strDirector = Request.Form("director")
In FindDirector.asp, we specify the connection string (within the SSI, DataStore.asp) and then use it to create and open a recordset:
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "AllMovies", strConnect, adOpenStatic, adLockReadOnly, adCmdTable
This is the same recordset, cursor type and lock type that we used in the Moving.asp example earlier in the chapter. We're using a static cursor, instead of a forward-only cursor, because some data providers don't support the Find method for recordsets with a forward-only cursor.
The next two lines are key lines:
strCriteria = "Director='" & strDirector & "'"
objRS.Find strCriteria ' show the first relevant record
The purpose of the first line is to create the criteria that the Find method will use to search for a matching record. We're using a string, strCriteria, to store these criteria. After this line, the strCriteria variant should contain a string such as Director='Quentin Tarantino'.
The second line is the Find method call. For the search criteria, we specify the strCriteria string in the first parameter. We've left the remaining parameters blank. This means that the search will begin at the current record (which is the first record in the recordset, since we've only just opened it) and the search direction is forward.
Now we can examine the results of the search. By checking the value of the EOF property, we can establish whether the search was successful. If no matching records were found, then the Find method places the cursor at the end-of-recordset, so EOF is True:
If objRS.EOF Then
Response.Write "The database does not contain any films by the director " & _
strDirector
Otherwise, the search was successful – which means that the Find method found a matching record, changed the cursor to point to that record and then stopped searching. We can use this to write the data in that record to the browser:
Else
Response.Write "<H2>Directed by " & strDirector & ":</H2>" & _
"<B>Title:</B> " & objRS("Title") & "<BR>" & _
"<B>Summary:</B> " & objRS("Summary") & "<BR>" & _
"<B>Genre:</B> " & objRS("Genres") & "<BR><BR><BR>"
End If
Then we just close up and tidy up the recordset:
objRS.Close
Set objRS = Nothing
And that's it. Note that the Find method is just a tool for pointing the cursor at a particular record. If you want to use the Find method to find lots of records, you have to call the Find method lots of times. For example, if you wanted to extend this example to find two relevant records, you'd need to call the Find method twice:
objRS.MoveNext
objRS.Find strCriteria
Or equivalently:
objRS.Find strCriteria, 1
The MoveNext is important. Having found the first successful record, you'd need to point the cursor away from it before searching for the next suitable record. That's because the default behavior of the Find method is to start searching at the current record (which you wouldn't want, because you would already have found the current record!). So, the sensible thing to do is move the cursor on one.
If you want to point the cursor at a record that satisfies a specific criterion, then the Find method is a good way to do it. But if you want to find lots of records that match the same criterion, then the Find method isn't always ideal. Instead, you can use your search criterion in conjunction with the Recordset object's Filter property, as we'll see next.
| << 13.3.3- Bookmarks | Chapter13 | 13.3.5- Filtering Records >> |
