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 Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188136
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
