[EagleRidge Home] | [Resources] |
On-the-Fly Queries in ASP
"How can I make a query that changes depending on a visitor's search
So you've learned the basics of ASP and SQL -- you know how to ask a database
(politely, of course) for selected information. Now you want to be able to do
that on the fly. "But how?" you ask.
Good question! The trick is to build the SQL
statement in a string first, using concatenation, then pass that string to open
Let's say you've got a movie site, and you want your users to be able to search
the database for titles of comedies. What might that ASP code look like?
Let's further assume that you have all your movie comedy titles in a database, in
a table called "tblComedies" (witty,
no?) and each title is uniquely identified with an ID number. Equally wittily,
the field in that table which holds the titles is called "strComedyName".
The Internet user types a movie name into an input box, and that name is then
passed (using the Request object) to the database. We'll also assume you've
already created the
connection object (which allows you to connect to your database) and that it's
So now let's create the recordset...
Set rstComedies = Server.CreateObject ("ADODB.Recordset")
...and put together the SQL string.
strSQL = "Select * FROM tblComedies WHERE
[strComedyName] = " & strComedyName & ";"
(Note: the above line is all one line; it may appear wrapped in your browser.)
We're here selecting all fields from tblComedies; these fields might include
a summary of the movie in question, reviews (or links to reviews), its
title and cast of characters, and so on.
Notice that the field in the database need not have the same name as the request
string. In the above example it does: strComedyName is the variable and [strComedyName]
is the field in the database. If the user is searching for the movie
"Beethoven" then that is what will be passed as the variable "strComedyName"
(we could have called it "strTitle" or "strSlobberingDog")
to the database, and that is what will be matched against the field [strComedyName].
Now you're ready to open the recordset:
rstComedies.Open strSQL, objCnn1
Voila! You've just gotten all the relevant information that your database has on
this query. It will output the fact that it starred Tom Hanks and a big dog, and
anything else you've included in that record.
You then integrate that information into the HTML that you're outputting, to
make it look pretty, useful, clear, and all those other things that web
consultants are overpaid to tell you.
David Wolpe is a published author, playwright, and professional programmer.
He may or may not be at work on a play about the Internet in which people
are referred to as "data objects."
Used with permission of the author.