I am trying to enable a search on my access database. I have it working on the basis that any one word input into the search textfield will return a match, but if I put more than one word it returns nothing. The query I have setup on the page is:

SELECT *
FROM main_downloads
WHERE File_name LIKE '%MMColParam%'
ORDER BY RequestDate DESC

MMColParam % Request.QueryString("search"

How can I overcome this problem?

Red Leader

I just checked mine and it does the same <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>
so if any one has any ideas, i would be grateful also.

Dave

Just been looking around and have noticed that if you enter more than one word in the exact order that they are in in the filed you are searching then it works fine, but if they are not in the same order it doesn't.

If you want to search for all words in search string but not as exact phrase do something like:
<pre id=code><font face=courier size=2 id=code>
&lt;%
Dim arrSearch, strWhere, strSQL
arrSearch = Split(Request.QueryString("search", " "
strWhere = "WHERE"
For i = 0 To UBound(arrSearch)
strWhere = strWhere & " File_name LIKE '%" & arrSearch(i) & "%'"
Next
strSQL = "SELECT * FROM main_downloads " & strWhere & " ORDER BY RequestDate DESC"
%&gt;
</font id=code></pre id=code>
Use strSQL as SQL string.

Viktor Farcic
www.farcic.com
TalkZone Manager

That works great, but if i search by more than one word it still comes up with

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'File_name LIKE '%chang%' File_name LIKE '%lives%''.

/download_site_new/download_search_results.asp, line 22

and line 22 says

Recordset1.Open()

Is this a human error?

Your help would be greatly received.

Many Thanks

Simon

Red Leader

My mistake... This will probably work (I haven't tried it).
<pre id=code><font face=courier size=2 id=code>
&lt;%
Dim arrSearch, strWhere, strSQL
arrSearch = Split(Request.QueryString("search", " "
strWhere = "WHERE File_name &lt;&gt; '' "
For i = 0 To UBound(arrSearch)
strWhere = strWhere & " AND File_name LIKE '%" & arrSearch(i) & "%'"
Next
strSQL = "SELECT * FROM main_downloads " & strWhere & " ORDER BY RequestDate DESC"%&gt;
</font id=code></pre id=code>

Try it out and tell me how it works.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
That works great, but if i search by more than one word it still comes up with

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'File_name LIKE '%chang%' File_name LIKE '%lives%''.

/download_site_new/download_search_results.asp, line 22

and line 22 says

Recordset1.Open()

Is this a human error?

Your help would be greatly received.

Many Thanks

Simon

Red Leader
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Viktor Farcic
www.farcic.com
TalkZone Manager

Edited by - vfarcic on 25 Jul 2002 14:52:05

Edited by - vfarcic on 25 Jul 2002 14:52:35

Put the code in my page and it gives this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/newreunite/TMP29x8bzuf93.asp, line 43


line 43 is Recordset1.Open()

any ideas?

Add <pre id=code><font face=courier size=2 id=code>Response.Write(strSQL)</font id=code></pre id=code> after the code above so that we can see what's the SQL it generates.

Viktor Farcic
www.farcic.com
TalkZone Manager

Fistly, really appreciate your help.

Secondly, upon doing all of the above I am still getting an error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/private _new/search_results.asp, line 20

again, line 20 is "Recordset1.Open()"

What could be the problem know?

Again, your help would be greatly received.

Simon

Red Leader

Put:<pre id=code><font face=courier size=2 id=code>Response.Write(strSQL)</font id=code></pre id=code>above<pre id=code><font face=courier size=2 id=code>Recordset1.Open()</font id=code></pre id=code>
You'll still see the error, but above it there will be SQL String displayed. Post it back here.
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Fistly, really appreciate your help.

Secondly, upon doing all of the above I am still getting an error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/private _new/search_results.asp, line 20

again, line 20 is "Recordset1.Open()"

What could be the problem know?

Again, your help would be greatly received.

Simon

Red Leader
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Viktor Farcic
www.farcic.com
TalkZone Manager

My search parameters are a bit more complicated in that I search on more than one field, here is my SQL that will find words in the correct order in field Message, but not words in any order.


<b>SELECT *
FROM Messages
WHERE YourFirstName LIKE '%MMColParam%' AND LastName LIKE '%MMColParam4%' AND Username LIKE '%MMColParam5%' AND WhoAreYouLookingFor LIKE '%MMColParam1%' AND Category LIKE 'MMColParam2' AND Message LIKE '%MMColParam3%'
ORDER BY WhoAreYouLookingFor ASC</b>

here is what i have tried to do with the variable you define in your code -strWhere

<b>SELECT *
FROM Messages
WHERE YourFirstName LIKE '%MMColParam%' AND LastName LIKE '%MMColParam4%' AND Username LIKE '%MMColParam5%' AND WhoAreYouLookingFor LIKE '%MMColParam1%' AND Category LIKE 'MMColParam2' AND '" & strWhere & "'
ORDER BY WhoAreYouLookingFor ASC</b>

tried putting Response.Write(strSQL) on page but it doesn't display anything, still gives the error though.

Think I am gonna try it with two search forms, on for the other fields, which works on a one word basis, and one for the Message field which uses the multi word thingy.

hang on though, do i need to make strSQL contain all my fields??? rather than using my existing SQL and adding the strSQL on the end?

might have a go at that.

oh my god , this is very complicated for my small brain<img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Yes, strSQL should contain all your records and it must be executed before the code for executing recordset. strSQL is replacement for "WHERE something = somethingElse...".

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
hang on though, do i need to make strSQL contain all my fields??? rather than using my existing SQL and adding the strSQL on the end?

might have a go at that.

oh my god , this is very complicated for my small brain<img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Viktor Farcic
www.farcic.com
TalkZone Manager

Here is the sql that is generated from a search:

SELECT * FROM main_downloads WHERE ID &lt;&gt; -1 AND File_name LIKE '%eco%' ORDER BY RequestDate DESC

Still with the same error.

Cheers

Red Leader

SQL looks fine. If table structure containt all the fields from it and if they are of right type, it should work. Are you sure that you have the field ID (identity field) and that File_name is text type?

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Here is the sql that is generated from a search:

SELECT * FROM main_downloads WHERE ID &lt;&gt; -1 AND File_name LIKE '%eco%' ORDER BY RequestDate DESC

Still with the same error.

Cheers

Red Leader
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Viktor Farcic
www.farcic.com
TalkZone Manager

Problem solved, I changed the Id reference to the name of the column that I was searching and all is now working, you are star.

Just one more thing, is it possible to have it display on the results page what their search was for?

Again thanks very uch for your help.



Red Leader

Insert this where you want it to appear:<pre id=code><font face=courier size=2 id=code>&lt;%=Request.QueryString("search"%&gt;</font id=code></pre id=code>
*Replace "search" with name of the query you're using for search.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Problem solved, I changed the Id reference to the name of the column that I was searching and all is now working, you are star.

Just one more thing, is it possible to have it display on the results page what their search was for?

Again thanks very uch for your help.



Red Leader
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Viktor Farcic
www.farcic.com
TalkZone Manager

Thankyou once again.


Red Leader