Copy link to clipboard
Copied
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!
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
...Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Thank you, I've heard that before, and I've been meaning to stop using them entirely. Thanks for the tip though
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Sorry, that was my fault I meant to use an example with commas!
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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,
^_^