This article is the third of a series on how to retrieve and update information stored in databases. It covers the following references:

• Letting users search for information
• Using SQL to query and return search results
• Using a Filter to screen out search results
• ADO Find Method

The simplest way to find data is to provide a full table-view where the user can browse thru the entire set of records, and simply use the client browser's Find dialog box to look for and navigate to whatever item to search for.

Whilst that may be adequate for small data sets, that method is barely efficient in real-world applications.

In this chapter, we look at the different ways to let users search a database for information, whether to look for a particular value, one record, or a group of records.

Basically, we create a search form to find just the specific records that match certain criteria, and display the results of the search beneath.

Though the outcome may seem the same, the samples shown herein use different methods to return the search results:

• with a query using SQL
• with a filter using ADO

When the user submits the form, the value of the form field is substituted into either a query or filter string.

Both methods can be used to do either:

• an "exact seek" to isolate a specific record
• or a "broad search" to retrieve a set of records that meet criteria the user specifies.

The result of each method depends on how the search comparison is done, or how the match to look for is evaluated, based on the following operators:

NOTE: NOT ALL data source providers support ALL the comparison operators. Always test to ensure whether a specific provider supports the comparator.

Basically, equal ( = ) and exactly equal ( == ) are best used for a strict search, such as when you need the user to provide an exact search string to match.

To succeed, the input string must match an exact value in the lookup field.

This type of search is mainly used in user authentication routines, where both the user's name and password are required to strictly match their database entries.

The rest of the operators are better suited to retrieving groups of records that match a given criteria.

The LIKE operator, in particular, is best used for "power search" functionality, where we need to look up some value anywhere within the lookup field, like to find, for example, any product with the words "blue" or "leather" or whatever in the product's description.

This is made possible by using the LIKE operator with "wild cards", or characters that the string value may contain, to specify one or more occurrences of ANY character.

SQL supports the percent sign ( % ), while the ADO Filter supports both the asterisk ( * ) as well as the percent sign ( % ) for wild cards. The use of wild cards is covered in the following sections.