Search with ACCESS DB

[ITGL72]

I am working with an access DB, and have a simple web form that searches based on the SUBJECT field of the DB Table. But I am getting errors.

If I do a search, for example, the word "active" I get:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Invalid use of '.', '!', or '()'. in query expression 'subject like '%active%'



The code I am using is:

<CFQUERY NAME=&quot;GETCAT&quot; DATASOURCE=&quot;GLKB&quot;>
SELECT *
FROM KnowledgeBase
WHERE subject like '%#form.SUBJECT#%'
</CFQUERY>





Any suggestions?

 

[mikeshort]

Try building up the where clause:

<cfset matchfield = &quot;%&quot; & form.SUBJECT & &quot;%&quot;>


<CFQUERY NAME=&quot;GETCAT&quot; DATASOURCE=&quot;GLKB&quot;>
SELECT *
FROM KnowledgeBase
WHERE subject like '#matchField#'
</CFQUERY>

 

[ITGL72]

Well that does work. Thank you. I guess I expected more of a powerful search though.

Maybe I should also have it search the content as well as the subject line? Unless theres something else you recommend as far as how to search through a table with subject and article content better.

For example (as far as it not being as powerful a search tool) if I search for &quot;TESTING&quot; and I know there is an entry that has the word &quot;TEST&quot; where its searching, it will not display it. Seems like the word being searched for MUST be exact.

 

[calista]

Is there some reason you don't want to use CFINDEX and CFSEARCH? You can index the results of a query, and that should give you better results. Calista :-X
Jedi Knight,
Champion of the Force

 

[ITGL72]

Other then the fact that I never looked into it or learned how to use it, no theres no reason other then that.

 

[calista]

OK, then, why don't you try this? First of all, go into the Cold Fusion Administrator, click on &quot;Verity Collections&quot;, and create a collection. It's a very simple process. Just type in a name for your collection and &quot;Submit&quot;.

Here is some code to index the results of a query:
<!--- Index the messages so new message is searchable. --->
This is the actual query I want to index.
<CFQUERY NAME=&quot;IndexMessages&quot;
DATASOURCE=&quot;#Application.Datasource#&quot;
DBTYPE=&quot;ODBC&quot;>
SELECT MessageID,
MsgAuthor,
MsgMessage
FROM MessageTable
</CFQUERY>
This is the indexing process.
<CFINDEX
ACTION=&quot;Update&quot;
COLLECTION=&quot;Messages&quot;
QUERY=&quot;IndexMessages&quot;
KEY=&quot;MessageID&quot;
TITLE=&quot;MsgMessage&quot;
TYPE=&quot;Custom&quot;
CUSTOM1=&quot;MsgAuthor&quot;
BODY=&quot;MsgMessage&quot;>

Here is my search against this collection:

<!--- Perform the search --->
<CFSEARCH COLLECTION=&quot;Messages&quot;
NAME=&quot;SearchResults&quot;
TYPE=&quot;SIMPLE&quot;
CRITERIA=&quot;#Form.SearchTerm#&quot;>

As you've probably figured out by now, I use this to index the messages on my discussion board to make the messages searchable.

To display the results, simply output them like any other query results using the name of your seach. Mine is &quot;SearchResults&quot;. If you need anything else, let me know. Give it a try, I think you'll like it. This way, I am updating my collection dynamically, as soon as the user posts a message it is searchable. Calista :-X
Jedi Knight,
Champion of the Force