Rick Curtis - Tutorial 12

AST - Advanced Search Technology with @WhereString

Okay, you want to create a major Search page and you are tired of doing things in that little tiny UltraDev Query Dialog box. I ran into this while building a large Resume site. On my site employers need to be able to search against 25 text fields and over 100 checkboxes. Try getting that to fit in the Query Window! So what do you do? The answer, create an @WhereString query.

SQL Query and @WhereString

The beauty of an @Wherestring is that you build all the query parameters of the fly. So let's first write the basic SQL statement which could be done in the UD SQL window or could be a calling a stored procedure. Either way your SQL code would look like this.

SELECT * FROM RESUMES
WHERE @WhereString

Now I can pass anything I want to this query. The key is you have to understand your database and how to create the strings to populate the Wherestring. Here are a few options and their results. Just plug the @WhereString into SELECT * FROM RESUMES
WHERE... and see the results.

Creating a Dynamic Stored Procedure

In order to accomplish this in SQL Server you need to create a Dynamic Stored Procedure (DSP). You need to be familiar with SQL to create Dynamic Stored Procedures. In essence a DSP creates a SQL string on the fly and then executes whatever SQL statement has been created. This works perfectly for adding the @Wherestring. As you can see from the sample DSP below you first define and @Wherestring variable. In this case I have created a default value for the @Wherestring in the event that no values are passed into the stored procedure. This will return all records. Then you define a variable to hold the SQL string that you are building. Note that since this is a string you have to set off each line of text with ' ' and use the + to continue the concatenation of rows. At the end you can see where we are passing in the Wherestring value that we have built on our ASP page and finally execute the stored procedure.

Building the @WhereString

To build the @WhereString you need to create a Search.asp page. Create a form on your page with form elements (text boxes, list boxes, radio buttons, check boxes) for whatever values you need to query on. It's best to name each form element to the appropriate field name in your database to help you relate the two.

Now we need to build the @Wherestring by concatenating all of the form values into one long string--the WhereString--to pass to your SQL query. So let's look at the code from the Seach.asp page below. First we define the WhereString variable. Each subsequent line evaluates if the particular form element is empty or not or if it has a particular value. If it is not empty or has the appropriate value then a new string of SQL code is added to the WhereString. Each new line of code continues to be evaluated and added to the string as appropriate. In essence you are writing all possible values for your SQL query into IF...THEN statements on the Search.asp page and adding them on as needed. For example, if someone enters 9/16/2001 into the DateEntered textbox then the value of Resumes.DateEntered >= 9/16/2001 is added to the WhereString. After all form elements have been evaluated then we have built the complete WhereString.

So let's walk through some of the code and see what it is doing. The key to making this work is you have to understand enough about SQL to know what kind of SQL statements to use on each line of code as you build your Wherestring.

Making the Magic

But how do we evaluate the content of the form elements to assemble everything into the Wherestring? By submitting the page to itself! The form Method is set to POST and the Action to Search.asp. Note the line of code in red shown above. When the page first loads the form has not been submitted to itself so the Request.Form("Submit") value is "". Once you submit the form to itself then the WhereString is built line by line and then it reaches the Request.Form("submit") value which is no longer "" so the Search.asp page redirects to List.asp passing the value of Wherestring onto List.asp.

List.asp has a recordset based on the one we wrote above - SELECT * FROM RESUMES WHERE @WhereString. So whatever string you created is passed to your SQL statement and creates your recordset.

Passing the Wherestring

I use Sessions. If you don't like to use Sessions then you could store the Wherestring in a Cookie, or place invisible form elements on an intermediate page and pass the form value of Wherestring on to subsequent pages.

Using a Session Variable

As you can see from the code above just before redirecting to the List.asp page I create new Session variable the takes the completed Wherestring as its value (Session("WhereString") = Wherestring). Whatever you created on your Search.asp page now drives the SQL query on the List.asp page and takes Session("WhereString") as the runtime parameter value. I always use Stored Procedures for this instead of the UD SQL Query Window but either work fine. As I said before you can create the most complicated queries imaginable in a Wherestring and pass them on to subsequent pages. Since the UD query window has a length limit, there are SQL queries that you simple cannot do if you had to write each individual line in UD's query window. Wherestring sets you free.

Make sure you have the following code at the top of the Search page so that the Wherestring is set to nothing each time the Search page loads. That way you start with a clean slate.

<%
Session("WhereString") = ""
%>

Using a Redirect Page

Another approach is to take the Search page and pass it to an intermediate page (Redirect.asp). Here you can add a form element with the Method set to POST and the Action set to List.asp. Add an invisible form element with the value set to <% Request("Wherestring") %>. This will pull in the newly created Wherestring from the Search page. Now you have to submit this form information to the List page that holds the recordset. This works by using <body onLoad="document.formname.submit()">. As the intermediate page is loaded, it creates the WhereString form element and then posts the Wherestring value onto List.asp. The problem with this is you can get a "page flash" as the Redirect.asp page loads and then passes you on to List.asp. Once you are on the list page, navigating through the recordset works fine. The only problem is that pressing the browser back button from the first List.asp page sends you back to the redirect page which goes into an endless loop. That is it immediately kicks you back to list. That's one reason I prefer to store the Wherestring in a Session variable.

Saving Searches with Cookies

If you have a complicated search page (like one with 100 check boxes), simply filling it out the form may be a big pain. Another feature you can add to your search page is the option to save the Wherestring as a cookie on the users' machine for future searches. Then you can check for the cookie value before submitting the form to itself and use the cookie value instead.

More Magic - Keyword Searches!

If you also want to have a Keyword Search as part of your Wherestring I've expanded this technique to use Tom Steeper's great Keyword Search Behavior - thanks Tom! It takes just a little bit of modification of Tom's Code. Here it is. You can see the lines of code I added in red. What my added code is doing is first evaluating if the Keywords form element is empty or not. If it has a value, it runs Tom's code, otherwise it ignores Tom's code.

Happy Searching!

Rick