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).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
