Query with variable throwing errors.

[leadman]

Can anyone tell me why the following query:

<cfquery name=&quot;search&quot; datasource=&quot;myDatabase&quot; dbtype=&quot;ODBC&quot;>
SELECT strProdID, strProdName, strProdCatagory, curPrice, strPublisher, strAuthor, memDescription, hypSmallPic, numWeight
FROM Ccdatabase
WHERE strProdName LIKE &quot;*#searchtext#*&quot;
</cfquery>

gives the following error (ive posted this in the access forum and it was suggested i come here because it may be related to ColdFusion):

ODBC Error Code = 07001 (Wrong number of parameters)

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

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.


I checked that all of the fields exist in the table. I deleted all but one of the select fields (strProdId) and the only change to the error message was &quot;Expected 1&quot; instead of &quot;Expected 4&quot;? And when i put single quotes around the *#searchtext#* it says &quot;Expected 3&quot;. Does this make sense? (also, putting single or double quotes inside the wildcard asterisk causes &quot;missing operator&quot; error).

 

[DeZiner]

How are you populating *#searchtext#* . If it is from, for example a form filed from the previous page it should look more like:

WHERE strProdName LIKE '*#form.searchtext#*'

This way it knows to fill with a form result.
DeZiner

When the gears stop turning,
we all stop learning.

 

[leadman]

*#searchtext#* is populated from a form - i changed it but still get an error - it &quot;Expected 3&quot; parameters.... i have the query inside a <cfif> tag, could hat be the problem?

 

[calista]

Just for kicks, try putting your WHERE clause inside a PreserveSingleQuote function.

WHERE strProdName LIKE (PreserveSingleQuotes(*searchtext*))

Another suggestion I have for you is to check out a custom tag from Allaire's Developers Exchange called CF_Boolsearch. It might help you. Calista :-X
Jedi Knight,
Champion of the Force

 

[milleem]

The error means that the symbols (',*,&quot; etc) are not understood. It may have to do with the version of odbc driver being used...try the following sql instead:

WHERE strProdName LIKE '%#searchtext#%'

-milleem

 

[leadman]

thanks guys but no good - tried exchanging % for * with no change (i think Access likes * - thats what im using). Used the PreserveSingleQuotes with no luck either. Hmmmm - but when i put Select * instead of the fieldnames it went through - but my field names are good! okay back to the drawing board. Does it matter what order I select my fields in?

 

[milleem]

No, order doesn't matter - but back to something you mentioned earlier - the query is within a <cfif> tag? Can you post some of that code? Also, does the error message show the actual data being passed through, such as [SQL = &quot;SELECT userName, password, TotalLogins, LastIP].

That may also be helpful for us to diagnose.

 

[leadman]

okay - ive gotten a little further. When i do SELECT and then a bunch of fields that ive had in my table since it was created it works fine. But when i try adding one of the three new fields i added yesterday i start getting the &quot;Too few parameters&quot; error. Its as if the fields are not actally in the table or are spelled wrong (as the error hint suggests) but they are definitely spelled right and when i open the database and check the table, the fields are there!! aarrrgh! Perhaps this is an Access issue?

 

[strantheman]

hi leadman

Try:

<cfquery name=&quot;search&quot; datasource=&quot;myDatabase&quot; dbtype=&quot;ODBC&quot; DEBUG>
SELECT
strProdID
, strProdName
, strProdCatagory
, curPrice
, strPublisher
, strAuthor
, memDescription
, hypSmallPic
, numWeight
FROM Ccdatabase
WHERE strProdName LIKE '%#searchtext#%'
</cfquery>

Now, if you using the NT the DEBUG attribute should show your query. If this still fails copy the resulting dynamic query and paste here.

good luck