error executing database query: too few parameters

[Guest]

Hi, I am having trouble with this error message - have used similar code before without any problems (I'm pretty new to coldfusion)

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

The error occurred in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\CFIDE\collinsvillerealestate\buypropertyresults.cfm: line 36

34 : <!---search by price range --->
35 : <cfif FORM.price IS NOT "">
36 : AND [price] <= #FORM.price#
37 : </cfif>
38 :



--------------------------------------------------------------------------------

SQLSTATE 07002
SQL SELECT shortDescription, propertyID, price, numberOfBedrooms, numberOfBathrooms, numberOfCarports, locationID, imageNameOne, categoryID, longDescritpionOne FROM propertiesForSale WHERE 0=0 AND locationID = 1 AND categoryID = 1 AND numberOfBedrooms = 2 AND [price] <= 300000 ORDER BY propertyID
VENDORERRORCODE -3010
DATASOURCE collinsvillerealestate
___________________________________________
My code is:

<cfquery datasource="collinsvillerealestate" name="properties">
SELECT shortDescription, propertyID, price, numberOfBedrooms,
numberOfBathrooms, numberOfCarports, locationID, imageNameOne,
categoryID, longDescritpionOne
FROM propertiesForSale
WHERE 0=0

<!---search by location --->
<cfif FORM.locationID IS NOT "">
AND locationID = #FORM.locationID#
</cfif>

<!---search by category--->
<cfif FORM.categoryID IS NOT "">
AND categoryID = #FORM.categoryID#
</cfif>

<!---search by number of bedrooms --->
<cfif FORM.numberOfBedrooms IS NOT "">
AND numberOfBedrooms = #FORM.numberOfBedrooms#
</cfif>

<!---search by price range --->
<cfif FORM.price IS NOT "">
AND price <= #FORM.price#
</cfif>

ORDER BY propertyID

</cfquery>

Have tried removing and fiddling with statement, but error message applies to all parts of the statement.

Thanks

[cf_dev2]

Does the query run successfully if you omit all of the parameters?

<cfquery datasource="collinsvillerealestate" name="properties">
SELECT shortDescription, propertyID, price, numberOfBedrooms,
numberOfBathrooms, numberOfCarports, locationID, imageNameOne,
categoryID, longDescritpionOne
FROM propertiesForSale
WHERE 0=0
ORDER BY propertyID
</cfquery>

If it works, start adding back the form field parameters one at a time. See which one causes the error. But you should use cfqueryparam for all form fields.

Also use a check that's appropriate for the values. For example, if the locationID value must be numeric then use IsNumeric(FORM.locationID) instead of testing for an empty value ""

<!---
The correct cfsqltype depends on the data type of your column. For a "text" column use "cf_sql_varchar" for "number" (long integer) use "cf_sql_integer", etc..
--->
<cfif IsNumeric(FORM.locationID)>
<cfqueryparam value="#FORM.locationID#" cfsqltype="cf_sql_integer">
</cfif>

[SafariTECH]

WHERE 0=0

What is this? a typo? as far as I know you can't have a DB field with a number only as name.

Sometimes errors say they are thrownn in one spot in the debugging, but was actually a bit prior. This statement seems completely odd in the query.

[paross1]

This WHERE 0=0 is commonly used where you have a dynamic query using AND clauses within CFIF tags, and none may be "true". In other words, if none of the CFIF statements are true, then WHERE 0=0 is always true and will return all rows. When using OR clauses in your query, then WHERE 1=0 could be used to make sure that no rows are returned unless at least one OR existed.

Phil

[Guest]

I think the 0=0 is a check that will always be true. I've used 1=1 before. It just says this is true continue, then you are free to use AND or OR's, etc.