EagleRidge Technologies, Inc., is proud to be able to present an occasional note from the irrepressible David Wolpe of Motley Fool fame.

From David Wolpe's answers to questions from readers...

[EagleRidge Home] | [Resources] | On-the-Fly Queries in ASP

Question:

"How can I make a query that changes depending on a visitor's search term?"

Answer:

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 the recordset.

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 called objCnn1.

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."

[Legal Stuff]
Used with permission of the author.