This tutorial shows you how to build a results page which will take multiple words from a single text field and create a dynamic recordset. The search also allows searching by ANY WORD, ALL WORDS, or EXACT PHRASE. The tutorial comes with sample files/database that you can easily modify to suit your needs, or you can cut and paste the code into your existing project.
This updated version of the tutorials now allows full functionality of the Ultradev DataBindings/Server Behaviour windows.
T-Cubed
advanced search tutorial - UltraDev4
friendly
Requirements:
ZIP File
1. Create a new page and
a Connection to the database.
2. Add a Recordset to the
page. You need to use the 'advanced' window.
Notice that there is the word t3_String
at the end of the SQL statement. This is added so that we can use our own WHERE
clause that will be built dynamically based on the search options. Be sure to
put a default value that would make a valid WHERE clause. If you are using the
sample database available with this tutorial then you can create you recordset
SQL/Variables exactly as above. If you are customising it to you database modify
the table name and the default value accordingly.
Next we need to go to the code view, and
modify the code that UD4 has created.
rsSearch__t3_String = "WHERE
ID=1"
Modify this line to:
'rsSearch__t3_String =
"WHERE ID=1"
Also:
rsSearch.Source = "SELECT
* FROM tblCatalogue " + Replace(rsSearch__t3_String, "'", "''")
+ ""
You need to modify this line to read:
rsSearch.Source = "SELECT
* FROM tblCatalogue " + Replace(rsSearch__t3_String, "'", "'")
+ ""
There is only 1 single quote inside the
second set of double quotes.
OK, now we need to add the code which will
create the dynamic 'WHERE CLAUSE'
<%
Dim CompType, t3_advSearch_String
'Store type of query in CompType ie All Words/OR, Any Word/AND or Exact Phrase/EXACT
CompType = Request("CompType")
SearchColumn = "Description"
SearchField = "zzz"
if(Request("SearchParam") <> "") then SearchField
= Request("SearchParam")
'Remove any single quotes from search field to eliminate potential errors.
SearchField = Replace(SearchField, "'", "")
'Checks the CompType, Executes
this code if the option All words or Any Word is chosen
if(CompType <> "EXACT") then
t3_advSearch_String = "WHERE " & SearchColumn & " LIKE
'%"
'Splits the search criteria into seperate words and stores them in an Array
SearchArray=Split(SearchField," ")
for i = 0 to Ubound(SearchArray)
if i > 0 then
'Builds the sql statement using the CompType to substitute AND/OR
t3_advSearch_String = t3_advSearch_String & " " & CompType
& " " & SearchColumn & " LIKE '%"& SearchArray(i)
& "%'"
else
'Ends the sql statement if there is only one word
t3_advSearch_String = t3_advSearch_String & SearchArray(i) & "%'"
end if
next
else
t3_advSearch_String = "WHERE " & SearchColumn & " LIKE
'%" & SearchField & "%'"
end if
%>
Add this code immediately after the line
that says:
<!--#include file="../Connections/myconnection.asp"
-->
When you want to use the code for your
own database, just modify the entry:
SearchColumn = "Description"
to reflect the column that
you want to search in your database.
You can now create your page using the
Data Bindings as normal to display the dynamic data on the page.
To make the page work, you need to have
a 'search page' which has the correct form fields. A sample of this is included
in the Zip File.
Note!
You will find that the Recordset has a 'Red Exclamation Mark' next to it in
the Server Behaviour window. This is normal and will not affect the page layout/functionality.
If you want to modify the recordset, you
can either change it directly inside the code view, or reverse the code changes
shown in blue earlier in the tutorial, modify the
recordset in the normal way, and then re-apply these changes afterwards.
Any
questions?
Read Tom's other tutorials at
http://members.prestige.net/ultradev/
Comments
Searching more fields of the database
If you want to search more that one field of the database then add a '+' in the Search Column line eg:
SearchColumn = "Name + Description"
More than one word?
I can't seem to get it to accept more than one word. One word or partial words are fine, but it freaks on a space. I get
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'keywords + shirtname + description + statename LIKE '%FIRSTSEARCHEDWORD%' keywords + shirtname + description + statename LIKE '%SECONDSEARCHEDWORD%''.
/freeform.asp, line49
The colored words represent the 2 words in a search string. I've customized the script some, but as I said, the single and partial word search work fine. Any ideas?
Searching more fields of the database 2
EX: SearchColumn = "Name & Description"
esta es mi primera noticia
You must me logged in to write a comment.