How to use CFPARAM with list of string values

[johnbruso23]

Hi All,

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>

<cfloop condition="#find(',,',WOIDS)#">

<cfset WOIDS = "#rereplace(WOIDS,',,',',null,','ALL')#"><br>

</cfloop>

  <cfquery name="CapProjectsWOIDS" datasource="#URL.DB#">
  SELECT [ENGOPSMTRLSID]
      ,[Work_Order]
  FROM ENGOPSMTRLS
  WHERE
<CFOUTPUT>
  ENGOPSMTRLSID = #URL.INDEX#
</CFOUTPUT>
AND
<CFOUTPUT>
  Work_Order in  (<cfloop index="idx" list="#variables.WOIDS#">
        <cfqueryparam
                        value=#WOIDS#
                        cfsqltype="cf_sql_varchar"
                         list="true"
                         /></cfloop>)
</CFOUTPUT>
</cfquery>

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?

Thanks,

John

[WolfShade · Accepted solution]

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.

HTH,

^_^

[johnbruso23]

ahhh! double quotes! Thanks