Here we look at a sample of using SQL to query the Users table and return search results.

Using SQL Select for User Authentication Run Sample | View Source

In this exercise, enter me@asp.com for the user email and ado for the password.

Notice I have included an onsubmit handler to test if the fields are left blank. This is to prevent the user from sending the form if blank, so as not to waste needless round trips to the server.

When the form is sent ( to the same page actually ), we open a Recordset using SQL to select the record whose values in the lookup fields ( UserEmail and Password ) equally match the given strings.

strQuery="SELECT UserEmail, Password FROM users 
   WHERE UserEmail='" & Request.Form( "UserEmail" ) & "' 
   AND Password='" & Request.Form( "Password" ) & "'"

NOTE: An SQL statement must be written as a single line without any line breaks; the examples shown here are done so only for readability.

If the Recordset returns EOF ( end-of-file ) upon opening, it means the Recordset is empty and the authentication has failed.

<% . . .
if rsUser.EOF then %>
   <h5>Oops. Sorry, no authentication, no entry!</h5>
<% else %>
   ' redirect code here
<% end if

' close record set and flush object from memory
rsUser.Close
set rsUser=Nothing
. . .
%>

Note that any entries other than me@asp.com for the user email and ado for the password returns EOF. This method is highly accurate, though is case-insensitive. Yet it works for all Microsoft data source providers and most others.

You can, however, define a custom function to do case-sensitive comparison for MS SQL Server™ and MS Access data, but that is beyond the scope of this exercise. In MS Visual FoxPro, you can simply use the exactly equal ( == ) operator for case-sensitive matching.

Whilst in the sample, the user data is shown to confirm that the query was indeed successful, in actual applications, the method typically redirects the user to another page when so.

Now let's see how SQL can be used in a "power search" to retrieve a matching set of records.

Wild card characters act as placeholders for other characters when you are specifying a value you want to find. You can use wild cards in an SQL query to search for specific records in a table where a given field contains certain characters and you:

• know only part of the value.
• want to find values that start or end with a specific letter or match a certain pattern.

In general, SQL uses the percent character ( % ) as a wild card character.

• To search for a match anywhere within the lookup field, you can include a wild card at the beginning and end of the search string. For example,

strQuery="SELECT ProductDescription FROM Products 
   WHERE ProductDescription LIKE '%blue%'"

which searches for all entries in the ProductDescription field with the string "blue" in them, and returns the records with the value found anywhere in the field.

• To look for a match from the start of the lookup field, or to return all entries with the search string as the FIRST letters of the field's value, include a wild card only at the end of the search string.

strQuery="SELECT ProductDescription FROM Products 
   WHERE ProductDescription LIKE 'blue%'"

• To return all entries with the search string as the LAST letters of the field's value, you format the query as follows:

strQuery="SELECT ProductDescription FROM Products 
   WHERE ProductDescription LIKE '%blue'"

Now let's see that in action, using any given string from a search form.

Because here we are providing a "universal" search, a little ploy I do in these cases is to include the wild card at both ends of the search string when defining the query.

strQuery="SELECT ProductType, ProductName, ProductDescription, 
   UnitPrice FROM Products 
   WHERE ProductDescription LIKE '%" & strSearch & "%'"

This way, I do not have to rely on the user having to set it. Of course, this is not a fail-safe method, but it does the job remarkably, and can search for any given string anywhere within any given field.

Searching for Records in a Database Run Sample | View Source

To see is to believe, so to test, type a string or any portion thereof that describes the product you want to find ( ex. beige, dura, light, etc. )

You can include as many conditions as needed in a query, connecting them with the AND or OR operator. This greatly expands the search functionality by allowing, for example, to search for the given string in two fields.

strQuery="SELECT ProductType, ProductName, ProductDescription, 
   UnitPrice FROM Products 
   WHERE ProductDescription LIKE '%" & strSearch & "%' 
   OR ProductType LIKE '%" & strSearch & "%'"

AND trims the search criteria as both conditions must be met, while OR broadens the possibilities further.

Keep in mind the logical placement of quotes when using multiple conditions or the query won't work. The general format is

strQuery="SELECT * FROM tableName
   WHERE fieldName1='strValue1' 
   OR fieldName2='strValue2'"