I have a field value that can be stored with many values for a single record: Work_Order = '555555,666666,111111' (note, these are actually stored as a single string value in the db with commas .)
I need to parse this field and enter the string values into the where clause of a new query:
<CFSET WOIDS = CapProjects.Work_Order>
<cfset WOIDS = "#rereplace(WOIDS,',,',',null,','ALL')#"><br>
<cfquery name="CapProjectsWOIDS" datasource="#URL.DB#">
ENGOPSMTRLSID = #URL.INDEX#
Work_Order in (<cfloop index="idx" list="#variables.WOIDS#">
Am I doing this right? I'm getting the following error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P4'. Is that the loop length? How do I pass that in?
First, you don't need to use CFOUTPUT within a CFQUERY (or most/all other CFtags) - CF knows.
Second, you don't need to loop a list for an "IN". Unless you're using some obscure database that isn't MS-SQL, MySQL, or Oracle. And you already know that CFQUERYPARAM has a list attribute.
NOTE: This is assuming that the datatype of Work_Order is char or varchar, not integer.
<cfquery name="CapProjectsWOIDS" datasource="#URL.DB#"> SELECT [ENGOPSMTRLSID] ,[Work_Order] FROM ENGOPSMTRLS WHERE ENGOPSMTRLSID = #URL.INDEX# /* <--- I didn't see URL anything, before this. And I REALLY cannot stress how bad of an idea it is to use a URL variable for your DSN. Just sayin'. */ AND Work_Order in (<cfqueryparam value="#WOIDS#" cfsqltype="cf_sql_varchar" list="true"/>) </cfquery>
Since you are using a varchar for the type, you should put double-quotes around the value (line 08), as I have demonstrated.
ahhh! double quotes! Thanks