Chapter 11. Searching


11.1 Grid.Find method

If your table contains many records, paging down to a record you are looking for may be too time-consuming. To facilitate the record-searching task, AspGrid 3.0 offers a method, Find, which accepts a search criteria as a parameter. The Find method is usually called right before Display and causes the latter to show your grid starting from a record that met the specified criteria.

The Find method in action is demonstrated by the code sample http://localhost/aspgrid/11_find/cities.asp which is based on the previously unseen cities table from our sample database. This table contains a list of major US and Canadian cities and their states/provinces copied from the National Geographic Road Atlas mileage chart.

11.2 Code Analysis

Here is the file cities.asp:

<FORM ACTION="cities.asp">
Enter search word or word part:<BR>
<INPUT TYPE="TEXT" NAME="CRITERIA" VALUE="<% = Request("Criteria") %>">

' Build connection string to aspgrid.mdb
strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("..\db\aspgrid.mdb")

' Create instance 2 of AspGrid for the Employees table
Set Grid = Server.CreateObject("Persits.Grid")

' Connect
Grid.Connect strConnect, "", ""

' Specify SQL statement
Grid.SQL = "select id, name, state from cities"

' Hide identity column
Grid("id").Hidden = True

' Set max number of rows per page
Grid.MaxRows = 6

' Specify location of button images
Grid.ImagePath = "../images/"

' Set Table widths
Grid.Table.Width = 300
Grid.Cols(2).Header.Width = 220
Grid.Cols(3).Header.Width = 80

' Set sorting
Grid.ColRange(2, 3).CanSort = True

' Find a record if a search criteria is set
Criteria = Request("CRITERIA")
If Criteria <> "" Then
  Grid.Find "name like '" & Criteria & "%'"
End If


This code is very similar to scripts we have seen so far, but it has two noticeably new elements. The first one is a form enabling a user to specify a search criteria on top of the page. The other is a call to the Grid.Find method:

Criteria = Request("CRITERIA")
If Criteria <> "" Then
  Grid.Find "name like '" & Criteria & "%'"
End If

Here, we construct our search criteria in the following form:

"name like 'xxx%'"

where xxx is a user-specified string. The usage of the like keyword and a % sign allows us to perform wild-card searches.

The Find method returns the absolute position of the found record in the recordset, or -1 if no records were found that meet the criteria. Our sample script ignores the returned value, but it may come in handy in some situations.

Data-bound Forms Using COM+ Role-Based Security