Advanced Multi-Word Search - UD4 Friendly

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/