In Part 2 we began our examination of the
script. In this part, we'll complete our dissection of this ASP page!
Recall that the main task of
/ASPScripts/search.asp is to grab the database results and page them.
That being said, we need to be able to convert the search terms into a SQL string. This is accomplished with
the following code:
This code may seem a bit confusing, but hopefully it will become more clear as we step through it. We start off
by breaking up the search terms into an array using the
and keyword as a delimiter. (For more
information on splitting a string into an array, check out the FAQ:
How can I convert a string into an array?)
Once we have this array, we grab it's upper bound and store that value in
iUpperTerms. We then
perform a loop through each element on the array.
At this point, imagine that we had the search string:
ADODB and (OLE-DB or database). Initially,
the parenthesis would have been stripped, and
txtSearchTerms would have equalled:
OLE-DB or database. Next, we'd split on the
and keyword, so we'd have the array
colTerms with two elements:
OLE-DB or database. Now, as we
step through each term of our array
colTerms, we split on the keyword
or and store
the results in the array
orTerms. We then step through each element of this array and build up
our SQL string, adding a conditional to the WHERE clause of our SQL string. (Recall that the column
Contains holds the complete text of the article.)
For each element in the
orTerms array, we place an
OR between it in the SQL string
and surround the overall
OR statement(s) with parenthesis... (Note that putting the
as the inner loop and using the parenthesis has the effect of giving the
or keyword a higher
presedence than the
For each term in the
colTerms array we place an
AND between it. So, assuming that we
had our search term of
ADODB and (OLE-DB or database), our completed SQL string WHERE clause
( Contents LIKE "%ADODB%" ) AND ( Contents LIKE "%OLE-DB%" OR Contents LIKE "%database%" ).
Next, we need to open our connection to the database and call our paging stored procedure:
I am not going to step through the
sp_PagedResults stored procedure, it is already covered in
a previous 4Guys article: Paging through Records using a Stored Procedure.
The remainder of the
/ASPScripts/search.asp page simply displays the results using HTML and then,
as described in the paging stored procedure article, simply packages up
all of the variables and stuffs them into a form. When the user clicks on the Next or Prev page buttons, the
form is submitted and
/ASPScripts/search.asp is revisited with a different page value (but with
the same search terms and such reposted back to the script).
Well, that wraps up this article! If you have any feedback or ideas on how to improve the script, I'd love to hear them! You can download the various scripts we examined in this article in the Attachments section below.