Commas in data causing problems in Selectbox Bind

[SarasotaTim]

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!

[EddieLotter]

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]

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

[SarasotaTim]

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

[SarasotaTim]

Thanks Eddie, but no, I am talking about commas, within the data itself. I have about 300 entries, and only the 4 or 5 with commas in the column being outputted through the CFC are effected.

[EddieLotter]

SarasotaTim wrote:

Thanks Eddie, but no, I am talking about commas, within the data itself.

My apologies. In your example of "DRIVER'S" you used an apostrophe, or single quote, which is what made me think that's what you were talking about.

Cheers

Eddie

[SarasotaTim]

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

[BKBK · Accepted solution]

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>

[WolfShade]

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,

^_^