Highlighted

How to use CFPARAM with list of string values

Explorer ,
Dec 17, 2015

Copy link to clipboard

Copied

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

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,

^_^

Views

1.4K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How to use CFPARAM with list of string values

Explorer ,
Dec 17, 2015

Copy link to clipboard

Copied

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

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,

^_^

Views

1.4K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 17, 2015 0
LEGEND ,
Dec 17, 2015

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 17, 2015 2
Explorer ,
Dec 17, 2015

Copy link to clipboard

Copied

ahhh! double quotes! Thanks

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 17, 2015 0