|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
May 29th, 2005, 09:57 PM
|
|||
|
|||
|
Am I blind or stupid...Too few parameters
Hello all, I am unsure if i am blind or stupid or may i've just been looking at the problem for too long.
I have a form that allows the user to find businesses on the db (access). They can also select the area and way they want the results to be ordered. Though I get an error To few parameters Expect 1, which from my experience just means i have made a stupid mistake. It is occuring in the cfif statements that are used to call the appropriate order by. Strange thing is that i have other searches that use pretty much the same code and work fine. Thanks for you help ad heres the code Code:
SELECT
b.business_id,
b.bus_name,
b.phone,
b.address,
b.zone_id,
a.area_name,
sc.sub_cat_name,
sc.description
FROM
(sub_category AS sc INNER JOIN business as b ON sc.sub_cat_id = b.sub_cat_id) INNER JOIN area AS a ON b.area_id = a.area_id
WHERE
([b].[bus_name] LIKE '%#keyword#%'
OR
[b].[description] LIKE '%#keyword#%'
OR
[sc].[sub_cat_name] LIKE '%#keyword#%')
AND
([b].[zone_id] = #variables.zone#)
ORDER BY
<cfif isDefined("variables.order") AND variables.order EQ "asc">
b.bus_name ASC
</cfif>
<cfif isDefined("variables.order") AND variables.order EQ "dsc">
b.bus_name DESC
</cfif>
<cfif isDefined("variables.order") AND variables.order EQ "area">
a.area_name ASC
</cfif>
|
|
#2
May 30th, 2005, 04:43 PM
|
|||
|
|||
|
First, always check the SQL that is actually executed by looking at the debugging information. But my first instinct might be that if more than one condition runs you need a comma between each element of the order by clause. You also might have a problem if none of the conditions are met because there will be nothing in the order by clause at all.
__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian. How to Post a Question in the Forums
|
|
#3
May 30th, 2005, 11:41 PM
|
|||
|
|||
|
There will only ever be one order by clause that will always be met.
This is the query that is being passed in the debugging info SELECT DISTINCT b.business_id, b.bus_name, b.phone, b.address, b.zone_id, a.area_name, sc.sub_cat_name, sc.description FROM (sub_category AS sc INNER JOIN business as b ON sc.sub_cat_id = b.sub_cat_id) INNER JOIN area AS a ON b.area_id = a.area_id WHERE ([b].[bus_name] LIKE '%t%' OR [b].[description] LIKE '%t%' OR [sc].[sub_cat_name] LIKE '%t%' OR [sc].[description] LIKE '%t%') AND ([b].[zone_id] = 1) ORDER BY a.area_name ASC I hope that gives you more insight into my problem Cheers, AD
|
|
#4
May 31st, 2005, 07:53 AM
|
|||
|
|||
|
Then the next step should be to run that query in your database's SQL tool. Does it run?
And the next step is to strip away the joins and the likes, and then add them one at a time until you determine which one is causing the problem. It could be a type mismatch, a missing comma, etc.
|
|
#5
May 31st, 2005, 07:32 PM
|
|||
|
|||
|
Are you sure that #variables.order# is defined??? If it's not defined then you have ORDER BY "" which would throw that same error. Just for grins, why don't you try outputing that variable just on top of where you execute that query to make sure it's set as something. Also, if it is set as something, make sure it's "asc", "dsc", or "area".
|
|
#6
June 2nd, 2005, 09:17 PM
|
|||
|
|||
|
I'm stupid, and i'm too embarrased to tell you what caused the error, lets just say it wasn't the code but a db thing... ok i'll tell you i had a table name wrong... i guess lack of sleep make you do dumb mistakes
thanks again, ad
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Am I blind or stupid...Too few parameters |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
