TrainingClose Window
ASP Tutorial


Searching Databases

This section looks at how we can use ASP to search databases by using a HTML form to generate a query. The ASP we have developed so far for querying the database does not offer much flexibility; every record in the database is returned. We need to use HTML forms to give the user options to refine the search.

Using a text box

This first example allows the user to generate a simple query based upon the value of some text entered into a form.

The following HTML page, artist.html, displays the search form:

<html>
<body>
<form method="get" action="artist.asp">
<h3>CD Search</h3>
Enter an artist:<br>
<input type="text" name="artist">
<p><input type="submit" value="Search">
</form>
</body>
</html>

This HTML form calls the ASP, artist.asp:

<%
Query = "SELECT Title,Artist from CDs WHERE Artist LIKE '%"
Query = Query & Request("artist") & "%'"
Query = Query & " ORDER BY Artist"
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "records"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,1,2
%>
<html>
<body>
Your search returned <b><%=RSlist.RecordCount%></b> records.<br>
<table border=1>
<tr><td><b>Title</b></td><td><b>Artist</b></td></tr>
<%
Do While Not RSlist.EOF%>
   <tr>
   <td><%=RSlist("Title")%></td>
   <td><%=RSlist("Artist")%></td>
   </tr>
   <%
   RSlist.Movenext
Loop
%>
</table>
<a href="artist.html">Return to form</a>
<hr><small><%=Query%></small>
</body>
</html>

The script will display the following:

Your search returned 2 records.

Title Artist
Abbey Road Beatles, The
Revolver Beatles, The
Return to form

SELECT Title,Artist from CDs WHERE Artist LIKE '%beatles%' ORDER BY Artist

  • The first three lines of the script build up the SQL query. The wild card symbols % and % at the start and end of the search term will allow for a wider search since artist names such as The Beatles and Beatles Band will be picked up. Note that we are using the % symbol as the wildcard rather than the * symbol we used in the section on SQL. This is a requirement of ODBC.

  • Before displaying the result of the search the script displays the number of records found. It does this by using the RecordCount property of the recordset object.

  • The script also displays the SQL string. This is useful for debugging purposes. However, it would be more useful if we output the SQL string before we open the recordset. Since this is where the script will fail if we have any errors in the SQL. We want to display the SQL before the script fails.

Using radio buttons

As a further refinement to our search we can use radio buttons to refine the search. For example, we may want to give the user the choice of viewing albums, singles, or both. The following HTML form and script does that.

The html form, format.html, looks like:

<html>
<body>
<form method="get" action="format.asp">
<h3>CD Search</h3>
Enter an artist:<br>
<input type="text" name="artist">
<p>Select format:<br>
Album <input type="radio" name="format" value="Album">
Single <input type="radio" name="format" value="Single">
Both <input type="radio" name="format" value="Both" checked>
<p><input type="submit" value="Search">
</form>
</body>
</html>

Here we have added three radio buttons, grouped under the name format, to allow the user to select a CD format. We need to vary the SQL query depending on the user's choice of radio button. For example, if the user were to select the album format then the SQL could read:

SELECT Title,Artist,Format from CDs WHERE Artist LIKE '%artist%' AND format='CDA' ORDER BY Artist

This is achieved by using an If…Then…Elseif statement to add an extra part to the WHERE clause.

The script, format.asp, starts as:

format=Request("format")
Query="SELECT Title,Artist,Format from CDs WHERE Artist LIKE '%"
Query=Query & Request("artist") & "%'"
If format="Album" Then
   Query=Query & " AND format='CDA'"
ElseIf format="Single" Then
   Query=Query & " AND format='CDS'"
End If
Query = Query & " ORDER BY Artist"
  • If both has been selected then no addition needs to be made to the SQL query string.
  • The rest of the ASP will execute the query and output the results in a table as in the previous example.

Recordset Methods

So far we have used the MoveNext method of the recordset object, which moves the pointer on to the next record to allow us to read it. Related methods are:

MoveFirst move to the first record in the recordset
MoveLast move to the last record in the recordset
MovePrevious move to the previous record

Another useful method is the Close method which will close an open recordset. The method is also available to the connection object. Another way of closing an object is to use

   Set object_name = Nothing

For example:

Set Rslist=Nothing
Set DataConn=Nothing

Recordsets and connections should be closed automatically when the script ends, but it is always worth adding the extra couple of lines to close the recordset and connection objects in your script.

More about the Open method

So far we have been using the open method as follows:

   RSlist.Open Query,DataConn,1,2

Where Query is a variable which contains our SQL command and DataConn is the name of the connection (to a database) we wish to use. What about the other parameters? The syntax to the open method is as follows:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The Source and ActiveConnection parameters have been explained.

CursorType
This is an optional parameter and specifies what sort of cursor the recordset will have on opening - forward only (0), keyset (1), dynamic (2), or static (3). The default value is 0 (forward only). Each of the different cursors behave differently, suffice to say that the recordCount property does not function properly with the default, forward only, cursor so in our examples we will be using one of the others.

LockType
This optional parameter determines what sort of locking is used when the recordset is opened. Locking is important when more than one user is updating a database record. The options are Read Only (1), Pessimistic (2), Optimistic (3), and Batch Optimistic (4). When we are simply reading data from a recordset then the default, read only, locking is sufficient. When we wish to update a recordset then we will specify one of the other (usually Pessimistic) settings.

Options
This too is an optional parameter and is used to specify what the source is, if it is not an SQL command or a database table name.

Exercises

  • Write a form and script which displays all of the CDs in the database. The user should be allowed to select the sort order (by artist, or title) from a radio button.

  • Modify the form and script so that the user can choose to view the list in reverse alphabetical order.

  • The form artist.html and script artist.asp currently searches for the text anywhere in the artist field i.e. searching for "smith" would return "The Smiths" and "Eliot Smith". Modify the form and script so that the user can select, from a drop-down box, whether to search for an exact match, an artist beginning with the search phrase, or the search phrase appearing anywhere in the field.

  • Modify the search form so that it consists of a single text box, for a search phrase, and a two radio buttons. Depending on which radio button is selected, the script should search the title field or the artist field. The script should perform an "anywhere in the field" search.

previous  |  contents  |  next