SQL Search Multi Keyword ASP

ari613 · January 18th, 2010, 09:01 AM

I am having a problem with the following code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim MyWhereClause
If Len(Request.QueryString("txtSearch")) > 0 Then
MyWhereClause = ParseSearchTerms(Request.QueryString("txtSearch"))
Else
MyWhereClause = " AND 1 = -1"
End If
%>
<%
If Request("txtSearch")<>"" Then
%>
<%
Dim rsSearch
Dim rsSearch_numRows

Set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_ConnString_STRING
rsSearch.Source = "SELECT * FROM ProductsPages WHERE Content Like " + "%" + Request("txtSearch") + "%"
rsSearch.Source = rsSearch.Source & MyWhereClause & _
" ORDER BY StartDate ASC"
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 1
rsSearch.Open()

rsSearch_numRows = 0

End IF
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body >
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<form action="search.asp" method="get" name="frmSearch">
Search the Site For:<br />
<input name="txtSearch" type="text" value="<%=Request.QueryString("txtSearch")%>" maxlength="50">
<input name="btnSearch" type="submit" value="Go"></form></td>
<td> </td>
</tr>
</table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><h2>Search Results</h2></td>
</tr>
<tr>
<td><form action="" method="get" name="frmSelectCategory">
<table width="600" border="0" cellspacing="2" cellpadding="2" class="clsSolidBlackBorder">
<tr>
<td class="clsHeading"><%=(rsSearch.Fields.Item("PageN ame").Value)%></td>
</tr>
<tr>
<td class="clsSummary"><%=(rsSearch.Fields.Item("CatNa me").Value)%><br>
Read
On...</td>
</tr>
<tr>
<td><hr align="center" size="1" noshade></td>
</tr>
</table>
<span class="clsErrorMessage">We're sorry, but we couldn't find any events matching your search
criteria. Please enter a new search term and hit the Go button to try
again. If you requested this page directly, please enter a search term
in the text box above and click the Go button.</span>
</form></td>
</tr>
<tr>
<td align="center">
<p><br>
<br>
obelix</p>
<p><br>
<br>
<br>
</p></td>
</tr>
</table>
</body>
</html>
<%
rsSearch.Close()
Set rsSearch = Nothing
%>
<%
Private Function ParseSearchTerms(ByVal searchTerm)
'************************************************* ***************
' This function is heavily based on the original
' code from: http://www.4guysfromrolla.com/webtech/113000-1.3.shtml
' Author: Scott Mitchel
' For: http://www.4guysfromrolla.com/
'************************************************* ***************
Dim colTerms ' will hold each main "search part"
Dim orTerms ' will hold the OR conditions
Dim iUpperTerms ' UBound of the colTerms array
Dim iLoop
Dim iUpperOrTerms
Dim iOrLoop
Dim strWhereClause

' Protect against SQL injection
searchTerm = Replace (searchTerm, "'", "''")
searchTerm = Replace (searchTerm, ";", "")

colTerms = Split(searchTerm, " and ", -1 ,1)

iUpperTerms = UBound(colTerms)

strWhereClause = ""

For iLoop = LBound(colTerms) to iUpperTerms
orTerms = Split(colTerms(iLoop), " or " , -1, 1)
iUpperOrTerms = UBound(orTerms)

strWhereClause = strWhereClause & "("

For iOrLoop = LBound(orTerms) To iUpperOrTerms
strWhereClause = strWhereClause & "Page LIKE '%" & _
Trim(orTerms(iOrLoop)) & "%' OR " & _
" Content LIKE '%" & _
Trim(orTerms(iOrLoop)) & "%' OR " & _
" Description LIKE '%" & _
Trim(orTerms(iOrLoop)) & "%' OR " & _
" Keywords LIKE '%" & _
Trim(orTerms(iOrLoop)) & "%'"

If iOrLoop < iUpperOrTerms Then
strWhereClause = strWhereClause & " OR "
End If
Next

strWhereClause = strWhereClause & ")"

If iLoop < iUpperTerms Then
strWhereClause = strWhereClause & " AND "
End If
Next

' Return the Where clause
If Len(strWhereClause) > 0 Then
ParseSearchTerms = " AND " & strWhereClause
Else
ParseSearchTerms = ""
End If
End Function
%>

Here is the error I am getting:

Microsoft Office Access Database Engine error '80040e14'

Syntax error in query expression 'Content Like %pastel% AND (Page LIKE '%pastel%' OR Content LIKE '%pastel%' OR Description LIKE '%pastel%' OR Keywords LIKE '%pastel%')'.

/deve2e/search.asp, line 26

I have tried variation but no luck yet.

any help will be appreciated