Skip to main content
J
johnbruso23
Inspiring
December 17, 2015
Answered

How to use CFPARAM with list of string values

  • December 17, 2015
  • 1 reply
  • 7373 views

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

    This topic has been closed for replies.
    Correct answer WolfShade

    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,

    ^_^

    1 reply

    WolfShade
    WolfShadeCorrect answer
    Legend
    December 17, 2015

    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,

    ^_^

      J
      johnbruso23Author
      Inspiring
      December 17, 2015

      ahhh! double quotes! Thanks

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices