AspGrid.com -- Chapter 11. Searching

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.

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") %>">
<INPUT TYPE="SUBMIT" NAME="Search" VALUE="Go">
</FORM>

<%
' 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

Grid.Display
%>

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.

Chapter 11. Searching

Contents

11.1 Grid.Find method

11.2 Code Analysis