Sorting, Filtering, and Finding | Ado Examples and Best Practices

Team-Fly

When you have a Recordset open, you can manipulate its contents. For example, if you populate a grid control and want to manipulate the rows, you can:

Set the Sort property to one of the Recordset Field names to reorder the rows in either ascending or descending sequence.
Set the Filter property to a valid criteria string to hide all rows not meeting the criteria.
Execute the Find method to locate rows based on a criteria string.

To help you check out the Sort and Filter properties and the Find method, I wrote a small sample application. The full source is on the CD, and we'll walk through the code here, pointing out some critical points along the way.

Before we get started, though, I want to point out that you can sort, filter, and find quite easily on the server. That is, you can make things smoother and more efficient for everyone if you leave the rows on the database where they belong. Passing rows in bulk to the client to manipulate can be expensive. However, that said, these client-side techniques can be a great way to reduce the dependency on the server for sorting, filtering, and finding once the Recordset is constructed and transmitted to the client.

Now, let's step through our sample application. First, we set up the variables. Notice that we'll save a few bookmarks to help find our way around the Recordset.

 Option Explicit Dim cn As Connection Dim cmd As Command Dim rs As Recordset Dim i As Inte Dim intWidth As Integer Dim varBookMarkStart As Variant Dim varBookmarkTop As Variant

Next, we open the connection and run a query to return selected columns from the data provider. We help the process by sorting the data before it arrives. The sample sets the ActiveConnection to Nothing, but you don't have to. However, this is not a bad idea if you expect to spend some time tinkering with the Recordset. We close the Connection, because we don't expect to need it again. There's a little more code used to make the grid a tad more presentable, but you can check that out on the CD.

 … rs.Open "SELECT Name, City, State, Zip, " _     & " (SELECT COUNT(*) From titles WHERE_ &titles.pubid = publishers.pubID) AS Titles " _     & " From Publishers Where zip Is Not Null ORDER BY Name", cn rs.ActiveConnection = Nothing cn.Close varBookMarkStart = rs.Bookmark         'Save first row position varBookmarkTop = rs.Bookmark Set MSHFlexGrid1.Recordset = rs ' Pass in the initial Recordset …

When the user clicks one of the grid control headings, we figure out which column was clicked and determine whether the user has already chosen this column to sort before. If so, we assume they want the opposite sort order—descending or ascending. Otherwise, we set the Recordset object's Sort property to the selected Field name.

 Private Sub MSHFlexGrid1_Click() Dim strCol As String With MSHFlexGrid1     If .Row > 1 Then Exit Sub          ' Only worry about clicks on the headings     strCol = rs.Fields(.ColSel).Name     Select Case rs.Sort         Case "", strCol & " DESC"             rs.Sort = strCol         Case strCol             rs.Sort = strCol & " DESC"         Case Else             rs.Sort = strCol     End Select     Set .Recordset = rs End With End Sub

The form, shown in Figure 7-5, has a few TextBox controls to capture the Filter and Find criteria strings, as well as a couple of Command buttons to start the Filter and Find routines.

click to expand
Figure 7-5: The Sort, Filter, and Find example user interface

The following code gets run when you click on the Filter Command button. It simply passes the filter criteria in the TextBox to the Filter property.

 Private Sub cmdFilter_Click() On Error GoTo cmdFEH rs.Filter = txtFilterCriteria.Text ' Note txtFilterCriteria alone did not work Set MSHFlexGrid1.Recordset = rs

When the user clicks on Filter, we take the criteria string and apply it to the Filter property, as seen in the following code segment. When we set the grid to the filtered Recordset, only rows that meet the Filter criteria are shown. The rest of the rows are still in the Recordset—they're just hidden.

Tip

You can't set the Filter property from a TextBox control without fully qualifying the Text property. That is, you have to code Rs.Filter = txtFilter.Text. If you leave off the .Text property qualifier, ADO gets confused as to how it's supposed to use the property.

 Private Sub cmdFind_Click() On Error GoTo cmdFEH rs.Find txtFindCriteria.Text, 1, adSearchForward, varBookMarkStart If rs.EOF Then     MsgBox "Record not found"     varBookMarkStart = varBookmarkTop     rs.MoveFirst     Exit Sub End If varBookMarkStart = rs.Bookmark          ' New Starting position

To locate specific rows in the Recordset that meet the criteria, we have to use the Find method. In this case, we have to pass a number of arguments to the method. These set the criteria, the number of rows to skip, the direction, and a bookmark indicating the starting point. I coded the routine to keep looking until EOF is True and then start again at the top of the Recordset.

Tip

As with the Filter property, you can't simply set the Find property to a TextBox control's default property. You must fully qualify the Text property; otherwise, ADO gets confused.

If no records qualify, based on the criteria, the Recordset is positioned past the valid rows and EOF is set to True. Otherwise, the current row pointer is reset to the first matching row. In this example, I used the AbsolutePosition property to pass the current row number to the MSHFlexGrid control's Row property. We then reset the starting position bookmark so that subsequent searches will begin at the "found" row plus one. Note that all searches begin one row past the starting bookmark position. This initial bookmark is set to point to the first Recordset row.

Constructing Filter Strings

It seems there are a few difugelties (aka bugs) in the ADO string parser when it comes to dealing with Filter (and Find) criteria strings. While these issues are addressed in ADO 2.6, they might cause you a degree of grief before it arrives or on systems that don't have it installed. If you have problems with embedded quotes (single or double), pound signs (#), or exclamation points (!) characters, you may have found the bug.

Note

To include single quotation marks (') in the Filter Value, try to use two single quotation marks to represent one. For example, to filter on O'Malley, the criteria string should be 'O''Malley'. To include single quotation marks at both the beginning and the end of the Filter Value, enclose the string with pound signs (#). For example, to filter on '1', the criteria string should be '#'1'#'. ADO has a number of bugs logged against the criteria string parser. Don't be surprised when various combinations of embedded single quotes, double quotes, commas, and pound signs confuse ADO.

There is no precedence between AND and OR operators in the criteria strings, but Boolean clauses can be grouped within parentheses. However, you cannotgroup clauses joined by an OR and then join the group to another clause with an AND, like this:

 (LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Instead, you would construct this filter as:

 (LastName = 'Smith' AND FirstName = 'John') OR (LastName = 'Jones' AND FirstName = 'John')

ADO uses either the asterisk (*) or the percent symbol (%) as its wildcard characters. In a LIKE expression, you can use a wildcard at the beginning and end of the expression (LastName Like '*mit*'), or only at the end of the pattern (LastName Like 'Smit*'). However, in a regular expression, the wildcard character can only be placed at the end of the expression.

Team-Fly