Advanced Multi-Word Search - UD4 Friendly Support Product Page
This topic was archived
NEED Help So Bad!!! Yes I am down on my knees
Asked 30 May 2001 21:16:17
1
has this question
30 May 2001 21:16:17 Kelly Brady posted:
<P>Here is the problem. I have created a search page using the <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o></P><P><SPAN style="FONT-SIZE: 24pt; COLOR: red; FONT-FAMILY: Impact">T-Cubed</SPAN><o
></o></P><P><SPAN style="FONT-SIZE: 24pt; COLOR: blue; FONT-FAMILY: Impact">advanced search</SPAN><o
></o></P><P>Behavior written by <A href="../mailto: "><SPAN style="FONT-SIZE: 10pt">Tom Steeper</SPAN></A> I have tried to email him regarding the issue but he must be to busy for me.</P>
<P>I am trying to add a few enhancements to this search by adding another variable. I have revised the query string to search for two things. The words presented in the search and another passed variable. It works however when I implement the GO TO Navigation Server behavior NEXT I receive the following error.</P>
<P><SPAN style="FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana">Error Type:<BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)<BR>[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.<BR><B>/garlock/internet/adv_search_results.asp, line 49<o
></o></B></SPAN></P><P><SPAN style="COLOR: black; mso-bidi-font-size: 8.0pt">Line 49 is the one below highlighted in red bold</SPAN></P>
<P><%<o
></o></P><P>Dim CompType, t3_advSearch_String<o
></o></P><P>'Store type of query in CompType ie All Words/OR, Any Word/AND or Exact Phrase/EXACT<o
></o></P><P>CompType = Request("CompType"
<o></o></P><P>SearchColumn = "FAQ_KEYWORDS"<o
></o></P><P> <o
></o></P><P>SearchField = "all"<o
></o></P><P>if(Request("SearchParam"
<> "" then SearchField =<SPAN style="mso-spacerun: yes"> </SPAN>Request("SearchParam"<o></o></P><P>'Remove any single quotes from search field to eliminate potential errors.<o
></o></P><P>SearchField = Replace(SearchField, "", ""
<o></o></P><P> <o
></o></P><P>'Checks the CompType, Executes this code if the option All words or Any Word is chosen<o
></o></P><P>if(CompType <> "EXACT"
then<o></o></P><P>t3_advSearch_String = " AND " & SearchColumn & " LIKE '%"<o
></o></P><P>'Splits the search criteria into seperate words and stores them in an Array<o
></o></P><P>SearchArray=Split(SearchField," "
<o></o></P><P>for i = 0 to Ubound(SearchArray)<o
></o></P><P>if i > 0 then<o
></o></P><P>'Builds the sql statement using the CompType to substitute AND/OR<o
></o></P><P>t3_advSearch_String = t3_advSearch_String & " " & CompType & " " & SearchColumn & " LIKE '%"& SearchArray(i) & "%'"<o
></o></P><P>else<o
></o></P><P>'Ends the sql statement if there is only one word<o
></o></P><P>t3_advSearch_String = t3_advSearch_String & SearchArray(i) & "%'"<o
></o></P><P>end if<o
></o></P><P>next<o
></o></P><P> <o
></o></P><P>else<o
></o></P><P>t3_advSearch_String = " AND " & SearchColumn & " LIKE '%" & SearchField & "%'"<o
></o></P><P>end if<o
></o></P><P>%><o
></o></P><P><%<o
></o></P><P>Dim category_id<o
></o></P><P>category_id = Request.Form("FAQ_CATEGORY_ID"
<o></o></P><P>%><o
></o></P><P><%<o
></o></P><P>Dim rsSearch__t3_String<o
></o></P><P>' rsSearch__t3_String = "WHERE FAQ_CATEGORY_ID=2"<o
></o></P><P>if (t3_advSearch_String<SPAN style="mso-spacerun: yes"> </SPAN><> ""
then rsSearch__t3_String = t3_advSearch_String <o></o></P><P>%><o
></o></P><P><%<o
></o></P><P>set rsSearch = Server.CreateObject("ADODB.Recordset"
<o></o></P><P>rsSearch.ActiveConnection = MM_garlock_STRING<o
></o></P><P>rsSearch.Source = "SELECT *<SPAN style="mso-spacerun: yes"> </SPAN>FROM FAQ WHERE FAQ.FAQ_CATEGORY_ID = " & category_id & + Replace(rsSearch__t3_String, "'", "'"
+ ""<o></o></P><P>rsSear