Skip to main content
Participating Frequently
May 22, 2008
Question

Pre-populate select box causing query error

  • May 22, 2008
  • 4 replies
  • 1507 views
Referring to this thread http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56335#305025
I wanted to pass a value to a dynamic select box (in a url), so I changed this:

<option value="'#SITEID#'" selected=#sid#>
to this:
<option value="#SITEID#"<cfif SITEID EQ sid> selected="selected"</cfif>>

and now get "Error Executing Database Query...... Too few parameters. Expected 1."

The query:

SELECT <CFLOOP list="#form.parameters#"
index="i">sitedata.#i#,</CFLOOP>
site.coordinates_n, site.coordinates_w, sitedata.monitordate, site.name,
site.siteid
FROM site INNER JOIN sitedata
ON site.siteid = sitedata.siteid
WHERE sitedata.SITEID IS NOT NULL
<CFIF form.siteid gt "">AND sitedata.SITEID in
(#PreserveSingleQuotes(form.siteid)#)</CFIF>
<CFIF startdate gt "" AND enddate gt "">AND sitedata.monitordate >=
#startdate# and sitedata.monitordate <= #enddate#</CFIF>
<CFIF startdate gt "" AND enddate is "">AND sitedata.monitordate >=
#startdate#</CFIF>
ORDER BY sitedata.monitordate, sitedata.siteid

I admit I'm not a CF or SQL expert! I'm guessing it has something to do
with the cfloop. Hoping this is something simple I've overlooked...

TIA

This topic has been closed for replies.

4 replies

Elfboy007Author
Participating Frequently
May 22, 2008
Well that did it! All day to find a single quote...
Elfboy007Author
Participating Frequently
May 22, 2008
Oi! I just changed the code and I do see something now:
There are no single quotes around the siteID. Will try again now with fix.
Inspiring
May 22, 2008
Also verify that form.siteid has a default value using cfparam in case the user does not check a value.
Inspiring
May 22, 2008
To set the selected HTML attribute try using an inline if (Iif function).

<option value="#SITEID#"#Iif(SITEID EQ sid, DE(' selected="selected"), DE(''))#>

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_h-im_05.html#1105815


Beware of accepting form data directly into your SQL query, you are open to SQL injection attacks.
http://www.adobe.com/devnet/coldfusion/articles/ben_forta_faster.html
http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored
Elfboy007Author
Participating Frequently
May 22, 2008
Thanks, I will try that today! Any ideas why the cfif wouldn't work?

Yes I have <CFPARAM name="form.siteid" default="">
on the target page but I wouldn't think that would be necessary if a value was preselected.
Elfboy007Author
Participating Frequently
May 22, 2008
Darn, didn't fix it. Still getting "Too few parameters. Expected 1."

Why do I not get errors with:
<option value="'#SITEID#'" selected=#sid#>