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

Indexing, Finding and Sorting

There are times when you might want to have your fields indexed. If your recordset becomes very large, having an index will decrease the amount of time it takes to search for a record, or sort a recordset. We will look into searching for a record and sorting a recordset (the Find method and Sort property respectively) later on this section. For now, it's worth noting that indexing will reduce processing time when performing either of these tasks.

Unfortunately, finding how to set the indexing property is not as straightforward as one might hope. Someday you might see an indexing property (scheduled for later releases of ADO), but for now you need to access it through the Field object's Properties collection.

Each Field object has a collection of its own, called Properties – it contains the settings of all the Field object's properties. The Field object's properties are dynamic in nature – by which I mean that they are created at runtime and they only exist under certain conditions. For example: if you have a server-side cursor, then you will not have a property for indexing a recordset. And this brings us to our point: the dynamic property that controls indexing is the Optimize property. To index a field, you need to set the property to True (and to remove an index on the Field object, you set the property to False). When the Optimize property is available, the default setting is False (no indexing).

Your code for creating an index might look something like this:

objRS.Fields(0).Properties("Optimize") = True

Alternatively (because the Fields collection is the default collection of the Recordset object):

objRS(0).Properties("Optimize") = True

The index itself resides in the client-side cursor that ADO provides. That means you are not indexing the database itself – rather, you are indexing the Recordset object. These are not database indexes. This only works with client-side cursors because most data providers do not support your attempts at indexing their data through ADO. When you use Sort or Find within a Recordset object, the fields are not indexed – they get indexed temporarily (by ADO) to get the job done. But we'll get more into these methods later in this chapter.

Using the Find Method

The Find method will return one record at a time that matches your criteria. This method will search a specific column in the recordset for a specific value or criteria. The syntax for the Find method is as follows:

objRS.Find Criteria, [SkipRows], [SearchDirection], [Start]

The arguments are summarized in the table below:

Here's an example. Suppose you need a list of everyone in the authors table of the pubs database, whose last name begins with the letters Wil. You could do it using the following:

Const adSearchForward = 1 '-- Search Constant
Const adSearchBackward = -1 '-- Search Constant

objRS.MoveFirst
Do Until objRS.EOF
  ' Search Forward through the recordset
  objRS.Find "au_lname Like Like 'Wil%'",, adSearchForward
 
  If Not objRS.EOF And Not objRS.BOF Then
    Response.Write objRS("au_fname") & " " & objRS("au_lname")
    Response.Write "<BR>"
    ' Move to the Next record
    objRS.MoveNext
  End If
Loop

In this fragment, we start at the top of the recordset and search forward. (Incidentally, we could also use a filter to do this job.) Note that when the Find method hits EOF, it doesn't wrap back to the top. For a complete search, it's wise to start at the top – by calling MoveFirst, as we've done here. Searching backwards is just as simple, but requires that you start at the other end:

objRS.MoveLast
Do Until objRS.BOF
  ' Search Forward through the recordset
  objRS.Find "LName Like 'wil%'",, adSearchBackward
 
  If Not objRS.EOF And Not objRS.BOF Then
    Response.Write objRS("au_fname") & " " & objRS("au_lname")
    Response.Write "<BR>"
    ' Move to the Previous recordobjRS.MovePrevious
  End If
Loop

Again, when the Find method hits BOF, it doesn't wrap back to the bottom – so we called MoveLast before running the search. If you do start at the bottom (objRS.MoveLast) then make sure you pass in the SearchDirection constant of adSearchBackward (= –1).

Sorting Data

To sort a recordset's data you must use its Sort property. The Sort property specifies one or more field names for the recordset to sorted on, and which direction the sort should be. The default sorting direction is ascending, which means you don't need to specify it if that is the direction you are planning on sorting.

The syntax for the Sort Property is as follows:

objRS.Sort = Value

Here, Value is a string of comma-separated field names, naming the fields to be sorted on. Additionally you may specify after each field name a blank space and the keyword ASCENDING or DESCENDING, which specifies the sort order. For example:

objRS.Sort = "LName ASCENDING, FName DESCENDING"

The default is ASCENDING, so the following two statements are functionally the same:

objRS.Sort = "LNAME"
objRS.Sort = "LNAME ASCENDING"

Note that the Sort property does not physically change the data – it simply allows you to access the data in a different order.

How does it work? A temporary index is placed on each Field object indicated in the Sort statement (provided one does not already exist on that particular field). If there is an existing index – created with the Optimize property – then that index is used.

To release the sort order, simply set the Sort property to an empty string. (Note that there's no enumerated constant for this, and that zero and Nothing don't do the job: you have to use an empty string.) For example:

objRS.Sort = ""

Any temporary indexes will also be released at this time.