It treats the value as a comma separated list. You use that with "column IN()"
Pascal > -----Original Message----- > From: Tim Laureska [mailto:[EMAIL PROTECTED] > Sent: 18 December 2004 14:25 > To: CF-Talk > Subject: RE: PreserveSingleQuotes error, I think > > Thanks Jochem... I really wondered whether or not if I need that > function > > The reference below to list="yes"... what exactly does that do? > > <cfqueryparam cfsqltype="cf_sql_integer" value="#product_id#" > list="yes"> > > -----Original Message----- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Saturday, December 18, 2004 8:04 AM > To: CF-Talk > Subject: Re: PreserveSingleQuotes error, I think > > Tim Laureska wrote: > > > > ERROR: > > [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in > criteria > > expression. > > SQL = "INSERT INTO mailing_list (product_id, state_id) SELECT > > p.product_id, s.state_id FROM products p, states s WHERE p.product_id > IN > > ('1') AND s.state_id IN ('3')" > > > > > > PROCESSNG TEMPLATE CODE: > > > > <CFSET Product_id_QVL = "'" & > > replace(replace(product_id,"'","''","ALL"),",","','","ALL") & "'"> > > <CFSET state_id_QVL = "'" & > > replace(replace(state_id,"'","''","ALL"),",","','","ALL") & "'"> > > > > <CFQUERY Name="InsNewXref" DataSource="pap_search"> > > INSERT INTO mailing_list (product_id, state_id) > > SELECT p.product_id, s.state_id > > FROM products p, states s > > WHERE p.product_id IN (#PreserveSingleQuotes(product_id_QVL)#) AND > > s.state_id IN (#PreserveSingleQuotes(state_id_QVL)#) > > </CFQUERY> > > Your error is the use of single quotes, integers don't need them. > But there is a better way then just fixing that: cfqueryparam > with the list attribute: > > <cfquery dataSource="pap_search"> > INSERT INTO mailing_list (product_id, state_id) > SELECT p.product_id, s.state_id > FROM products p, states s > WHERE p.product_id IN (<cfqueryparam > cfsqltype="cf_sql_integer" value="#product_id#" list="yes">) > AND s.state_id IN (<cfqueryparam cfsqltype="cf_sql_integer" > value="#state_id#" list="yes">) > </cfquery> > > Jochem > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188138 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
