Skip to main content
S
SarasotaTim
Participating Frequently
August 2, 2016
Answered

Commas in data causing problems in Selectbox Bind

  • August 2, 2016
  • 2 replies
  • 599 views

Hi guys, going nuts trying to figure this out....

I'm using a function within a CFC to dynamically populate 3 select boxes with data, which works fine.........

The problem is, anytime one of the dropdowns pulling from the database contains a comma (such as in the phrase DRIVER'S), the page either produces a bind error, or the effected box populates as blank...

This only happens on any data containing a comma.....I've tried using CFQUERYPARAM in the query, but that's not making any difference in this respect.

The CFC:

<!--- GET APPLICATIONS BASED ON LOCATION --->

    <cffunction name="getApplications" access="remote" returnType="query">

        <cfargument name="Location" type="string" required="no">

        <!--- Get data --->

        <cfquery name="data_applications" datasource="#application.dsn#">

         SELECT DISTINCT (Applicat)

    FROM Cklst_source

         WHERE Location = <cfqueryparam value="#ARGUMENTS.Location#">

    GROUP BY Applicat

         UNION ALL

  SELECT  '' AS Applicat

    ORDER BY Applicat ASC

        </cfquery>

      

        <cfset session.itemlocation = '#ARGUMENTS.Location#'>

  

        <!--- And return it --->

        <cfreturn data_applications>

    </cffunction>

  

    <!--- GET CATEGORY BASED ON APPLICATION ---> 

<cffunction name="getCategories" access="remote" returnType="query">

        <cfargument name="Applicat" type="string" required="no">

        <!--- Get data --->

        <cfquery name="data_categories" datasource="#application.dsn#">

         SELECT DISTINCT (Category)

    FROM Cklst_source

         WHERE Applicat = <cfqueryparam value="#ARGUMENTS.Applicat#"> AND Location = <cfqueryparam value="#session.itemlocation#">

    GROUP BY Category

         UNION ALL

  SELECT  '' AS Category

    ORDER BY Category ASC

        </cfquery>

      

        <cfset session.itemapplication = '#ARGUMENTS.Applicat#'>

  

        <!--- And return it --->

        <cfreturn data_categories>

    </cffunction>

<!--- GET TRANSACTIONS BASED ON CATEGORY ---> 

<cffunction name="getTransactions" access="remote" returnType="query">

        <cfargument name="Category" type="string" required="no">

        <!--- Get data --->

        <cfquery name="data_transactions" datasource="#application.dsn#">

         SELECT DISTINCT (Transact)

    FROM Cklst_source

         WHERE Category = <cfqueryparam value="#ARGUMENTS.Category#"> AND Applicat = <cfqueryparam value="#session.itemapplication#"> AND Location = <cfqueryparam value="#session.itemlocation#">

    GROUP BY Transact

         UNION ALL

  SELECT  '' AS Transact

    ORDER BY Transact ASC

        </cfquery>

      

        <cfset session.itemcategory = '#ARGUMENTS.Category#'>

  

        <!--- And return it --->

        <cfreturn data_transactions>

    </cffunction>

  

    <!--- GET ITEMS BASED ON TRANSACTION ---> 

<cffunction name="getItems" access="remote" returnType="query">

        <cfargument name="Transact" type="string" required="no">

        <!--- Get data --->

        <cfquery name="data_items" datasource="#application.dsn#">

         SELECT DISTINCT (Displayed)

  FROM Cklst_source

  WHERE Transact = '#ARGUMENTS.Transact#' AND Category = '#session.itemcategory#' AND Applicat = '#session.itemapplication#' AND Location = '#session.itemlocation#'

        </cfquery>

      

        <cfset session.transaction = '#ARGUMENTS.Transact#'>

      

      

        <!--- And return it --->

        <cfreturn data_items>

    </cffunction>

And the dropdowns themselves:

<tr style="display: none;">

<td width="100" align="right"><strong>LOCATION</strong></td>

<td> </td>

<td align="left">

<div style="display: none;">

<cfif isDefined("form.submit")><cfselect name="Location"  display="Location" bind="cfc:webapps.checklist.cfc.queries.getLocations()" bindonload="true" selected="#form.Location#" />

<cfelse><cfselect name="Location"  display="Location" bind="cfc:webapps.checklist.cfc.queries.getLocations()" bindonload="true" />

</cfif>

</div>

</td>

</tr>

<tr>

<td width="100" align="right"><strong>APPLICATION</strong></td>

<td> </td>

<td align="left">

<cfif isDefined("session.itemapplication") AND session.itemapplication NEQ "">

<cfselect name="Application" id="Application" selected="#session.itemapplication#"  required="yes" size="1" message="Please select an application before continuing."  display="Applicat" bind="cfc:webapps.checklist.cfc.queries.getApplications({Location})" bindonload="false" />

<cfelse>

<cfselect name="Application" id="Application" selected="#form.Application#"  required="yes" size="1" message="Please select an application before continuing."  display="Applicat" bind="cfc:webapps.checklist.cfc.queries.getApplications({Location})" bindonload="false" />

</cfif>

</td>

</tr>

<tr>

<td width="100" align="right"><strong>CATEGORY</strong></td>

<td> </td>

<td align="left">

<cfif isDefined("session.itemcategory") AND session.itemcategory NEQ "">

<cfselect name="Category" id="Category" selected="#session.itemcategory#"  required="yes" size="1" message="Please select a category before continuing."  display="Category" bind="cfc:webapps.checklist.cfc.queries.getCategories({Application})" bindonload="false" />

<cfelse>

<cfselect name="Category" id="Category" selected="#form.Category#"  required="yes" size="1" message="Please select a category before continuing."  display="Category" bind="cfc:webapps.checklist.cfc.queries.getCategories({Application})" bindonload="false" />

</cfif>

</td>

</tr>

<tr>

<td width="100" align="right"><strong>TRANSACTION</strong></td>

<td> </td>

<td align="left">

<cfif isDefined("session.transaction") AND session.transaction NEQ "">

<cfselect name="Transact" id="Transact"  required="yes" size="1" message="Please select a transaction before continuing." selected="#session.transaction#" value="Transact" display="Transact" bind="cfc:webapps.checklist.cfc.queries.getTransactions({Category})" bindonload="false" />

<cfelse>

<cfselect name="Transact" id="Transact" required="yes" size="1" message="Please select a transaction before continuing." value="Transact" display="Transact"  selected="#form.Transact#" bind="cfc:webapps.checklist.cfc.queries.getTransactions({Category})" bindonload="false" />

</cfif>

</td>

<td><cfinput type="submit" id="transsubmit" name="submit" value="Show Items" onclick="submitFormOkay = true;"></td>

<td><cfinput type="button" id="transclear" name="transclear" value="Clear Transaction" onclick="location.href('clear_transaction.cfm?source=original');"></td>

</tr>

<tr><td> </td></tr>

</table>

Thanks in advance guys!

This topic has been closed for replies.
Correct answer BKBK

Sorry, that was my fault I meant to use an example with commas!


Try returning the results as two-dimensional arrays, rather than as queries. Something like this:

  <!--- GET APPLICATIONS BASED ON LOCATION --->

<cffunction name="getApplications" access="remote" returnType="array">

<cfargument name="Location" type="string" required="no">

    <!--- Define variables --->    

    <cfset var data_applications="">

    <cfset var result=ArrayNew(2)>  

    <cfset var i=0>

    <!--- Get data --->

    <cfquery name="data_applications" datasource="#application.dsn#">

    SELECT DISTINCT (Applicat)

    FROM Cklst_source

    WHERE Location = <cfqueryparam value="#ARGUMENTS.Location#">

    GROUP BY Applicat

    UNION ALL

    SELECT  '' AS Applicat

    ORDER BY Applicat ASC

    </cfquery>

    <cfset session.itemlocation = '#ARGUMENTS.Location#'>

    <!--- Convert query results to array --->

    <cfloop index="i" from="1" to="#data_applications.recordCount#">

        <cfset result[1]=data_applications.Applicat>

        <cfset result[2]=data_applications.Applicat>

    </cfloop>

<!--- And return it --->

<cfreturn result>

</cffunction>

<!--- GET CATEGORY BASED ON APPLICATION --->

<cffunction name="getCategories" access="remote" returnType="array">

<cfargument name="Applicat" type="string" required="no">

    <!--- Define variables --->    

    <cfset var data_categories="">

    <cfset var result=ArrayNew(2)>  

    <cfset var i=0>

    <!--- Get data --->

    <cfquery name="data_categories" datasource="#application.dsn#">

    SELECT DISTINCT (Category)

    FROM Cklst_source

    WHERE Applicat = <cfqueryparam value="#ARGUMENTS.Applicat#"> AND Location = <cfqueryparam value="#session.itemlocation#">

    GROUP BY Category

    UNION ALL

    SELECT  '' AS Category

    ORDER BY Category ASC

    </cfquery>

    <cfset session.itemapplication = '#ARGUMENTS.Applicat#'>

    <!--- Convert query results to array --->

    <cfloop index="i" from="1" to="#data_categories.recordCount#">

        <cfset result[1]=data_categories.Category>

        <cfset result[2]=data_categories.Category>

    </cfloop>

    <!--- And return it --->

<cfreturn result>

</cffunction>

<!--- GET TRANSACTIONS BASED ON CATEGORY --->

<cffunction name="getTransactions" access="remote" returnType="array">

<cfargument name="Category" type="string" required="no">

    <!--- Define variables --->    

    <cfset var data_transactions="">

    <cfset var result=ArrayNew(2)>  

    <cfset var i=0>

    <!--- Get data --->

    <cfquery name="data_transactions" datasource="#application.dsn#">

    SELECT DISTINCT (Transact)

    FROM Cklst_source

    WHERE Category = <cfqueryparam value="#ARGUMENTS.Category#"> AND Applicat = <cfqueryparam value="#session.itemapplication#"> AND Location = <cfqueryparam value="#session.itemlocation#">

    GROUP BY Transact

    UNION ALL

    SELECT  '' AS Transact

    ORDER BY Transact ASC

    </cfquery>

    <cfset session.itemcategory = '#ARGUMENTS.Category#'>

<!--- Convert query results to array --->

    <cfloop index="i" from="1" to="#data_transactions.recordCount#">

        <cfset result[1]=data_transactions.Transact>

        <cfset result[2]=data_transactions.Transact>

    </cfloop>

<!--- And return it --->

<cfreturn result>

</cffunction>

<!--- GET ITEMS BASED ON TRANSACTION --->

<cffunction name="getItems" access="remote" returnType="array">

<cfargument name="Transact" type="string" required="no">

    <!--- Define variables --->    

    <cfset var data_items="">

    <cfset var result=ArrayNew(2)>  

    <cfset var i=0>

    <!--- Get data --->

    <cfquery name="data_items" datasource="#application.dsn#">

    SELECT DISTINCT (Displayed)

    FROM Cklst_source

    WHERE Transact = '#ARGUMENTS.Transact#' AND Category = '#session.itemcategory#' AND Applicat = '#session.itemapplication#' AND Location = '#session.itemlocation#'

    </cfquery>

    <cfset session.transaction = '#ARGUMENTS.Transact#'>

<!--- Convert query results to array --->

    <cfloop index="i" from="1" to="#data_items.recordCount#">

        <cfset result[1]=data_items.Displayed>

        <cfset result[2]=data_items.Displayed>

    </cfloop>

<!--- And return it --->

<cfreturn result>

</cffunction>

2 replies

WolfShade
WolfShade
Legend
August 2, 2016

I will second EddieLotter​'s statement.  Absolutely, positively stop using CFFORM and any related form elements (CFINPUT, CFSELECT, etc.).  Almost no veteran coder uses them, anymore.  Whatever benefits you get from being (I hate to use this word) lazy and binding anything to the elements aren't worth it, IMHO.  Better off using a standard FORM, with standard elements, and manually creating the select options within a CFOUTPUT query="{queryName}".  From what I understand, CF is using an outdated Ext.js whenever you use CFFORM, et al.

V/r,

^_^

    EddieLotter
    EddieLotter
    Inspiring
    August 2, 2016

    You're talking about a single quote, not a comma. You need to escape single quotes when using them in javascript strings. Use the replace() function to replace one single quote with two single quotes.

    Cheers
    Eddie

    EddieLotter
    EddieLotter
    Inspiring
    August 2, 2016

    P.S. I just noticed that you're using cfselect. This complicates matters. Hopefully someone with experience using that tag can help, but all cfform tags should be avoided if at all possible.

    Cheers

    Eddie

    S
    SarasotaTimAuthor
    Participating Frequently
    August 2, 2016

    Thank you, I've heard that before, and I've been meaning to stop using them entirely. Thanks for the tip though

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