You forgot your parentheses. SELECT table1.field1 table2.field1, table2.field2, table2.field3 FROM table1 INNER JOIN table2 ON table1.field1 = table2.field4 WHERE table1.field1 = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#attributes.id#" /> AND table2.field1 IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#attributes.typelist#" list="Yes" separator="," />) ORDER BY table2.field1
> -----Original Message----- > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 18, 2003 1:44 PM > To: CF-Talk > Subject: CFQUERYPARAM with lists > > > I'm having all kinds of trouble with using CFQUERYPARAM on a > list of VARCHAR > values. I found a couple threads in the archives, and I > can't figure what > I'm doing wrong. Here's the query: > > SELECT table1.field1 > table2.field1, table2.field2, table2.field3 > FROM table1 > INNER JOIN table2 ON table1.field1 = table2.field4 > WHERE table1.field1 = <cfqueryparam cfsqltype="CF_SQL_INTEGER" > value="#attributes.id#" /> > AND table2.field1 IN <cfqueryparam cfsqltype="CF_SQL_VARCHAR" > value="#attributes.typelist#" list="Yes" separator="," /> > ORDER BY table2.field1 > > #attributes.id# a number and works fine. > #attributes.typelist# is a list of > strings (not my decision) that will be of length one of > greater. I've tried > with and without the SEPARATOR attribute, and I've also tried > adding dummy > list elements, to ensure that there is more than one value. > > The error i'm getting is this (when attributes.typelist is > set to 'Images', > without the quotes): > > Syntax error or access violation: You have an error in your > SQL syntax near > ''Images' ORDER BY table2.field1' at line 6 > > Near as I can tell, CF is not bothering to adding the > parentheses around the > list of values, just the single quotes, so it's screwing up > the SQL parser, > because it sees 'IN' and then expects a parentheis. > > CFMX w/ U2, Apache 2.0, MySQL 3.23, RedHat 8. > > MTIA, > barneyb > > --- > Barney Boisvert, Senior Development Engineer > AudienceCentral (formerly PIER System, Inc.) > [EMAIL PROTECTED] > voice : 360.671.8708 x12 > fax : 360.647.5351 > > www.audiencecentral.com > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.462 / Virus Database: 261 - Release Date: 3/13/2003 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
