CFQUERY NAME="getZip" DATASOURCE="sometestdb"
Select *
>From tblZip
Where ZIP in ('#replace(ziplist,",","','","all")#');
/CFQUERY
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecomunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: David Schmidt [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 14, 2001 1:20 PM
To: CF-Talk
Subject: RE: Lists, Access, & the IN sql operator - Help needed.
Ok, this is ugly, but it works...
cfset ziplist= "98404,98405,98408"
CFQUERY NAME="getZip" DATASOURCE="sometestdb"
Select *
>From tblZip
Where ZIP in (<cfloop list="#ziplist#"
index="element">'#element#',</cfloop>);
/CFQUERY
cfdump var="#getZip#"
-----Original Message-----
From: David Schmidt [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 14, 2001 1:02 PM
To: CF-Talk
Subject: Lists, Access, & the IN sql operator - Help needed.
Hi all,
I have a little problem with Lists, Access & the sql "IN" operator and was
curious if anyone had come across this before. Apparently, CF drops in
extra single quotes around list items output inside sql statements. Anyone
have a work around?
Thanks In Advance,
David Schmidt
Here is the code (keep in mind that it is simply test code and not a real
app):
cfset ziplist= chr(39) & "98404" & chr(39) & "," & chr(39) & "98405" &
chr(39) (comment: CHR(39) = "'")
cfoutput#ziplist#/cfoutput (comment: This displays '98404','98405' and that
is what I want)
CFQUERY NAME="getZip" DATASOURCE="testlistdb"
Select *
>From tblZip
Where ZIP in (#ziplist#);
/CFQUERY
cfdump var="#getZip#" (comment: This never happens because of the sql error
below)
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'ZIP in (''98404'',''98405'')'.
SQL = "Select * From tblZip Where ZIP in (''98404'',''98405'');"
Please note the double (single quotes).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
