2 Replies Latest reply on Dec 17, 2015 10:06 AM by johnbruso23

    How to use CFPARAM with list of string values

    johnbruso23 Level 1

      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

        • 1. Re: How to use CFPARAM with list of string values
          WolfShade Level 4

          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 person found this helpful
          • 2. Re: How to use CFPARAM with list of string values
            johnbruso23 Level 1

            ahhh! double quotes! Thanks