Solved

Handling value with a comma when calling cfc using <cfset>

Posted on 2014-10-24
7
122 Views
Last Modified: 2014-11-12
I am passing in certain values when calling a cfc function from a cfset. However, if one of the values contain a comma inside it, then my call to the cfc does not work correctly.

Here is an example of what I am doing (Obviously not full code, but should get the idea on my problem when reading below):

<cfset fname = getCustomerName.t1value>

<cfset get_data = application.customerinfo.getData(fname,phone,date,address) />

<cfif !get_data.recordCount>
No results found
</cfelse>
#fname#
#phone#
#data#
#address#
</cfif>

Open in new window


The fname may contain commas sometimes. When it does contain the comma it shows that no data is found, when there really is data. I need to figure out how to pass in a value that may contain comma when calling a cfc and passing values to it.
0
Comment
Question by:bman2011
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 40404099
I don't think the problem is in passing the values, the problem inside the cfc function.   Sounds like you are trying to select some records from the database and it is not matching.    If that is true, in order to match, you would need the value in the database to also have the comma and be an exact match to the value passed in.   If you show some of that code we can help more..
0
 

Author Comment

by:bman2011
ID: 40405667
gdemaria - The way I confirmed my issue was doing a cfdump on the first call and then the second. Basically, I added a cfdump on line 2. The result output  for fname value was correct.

When I added the cfdump to line 4, it came out with 5 array results instead of 4.

Example fname value: JAKE'S AIR GUITAR LAND, INC-123456

Example cfdump result:
JAKE array[1]
S AIR GUITAR LAND, INC-123456 array [2]
609-123-2451 array [3]
10-25-2014 array[4]
1234 Creoul Court Las Vegas, Nevada 00000 array[5]
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40405668
It appears to be breaking at the single quote, not at the comma.   JAKE  +  S AIR GUITAR...

So let's see the code that you use that will break the quote, perhaps you should use double "
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:bman2011
ID: 40405712
Sorry, that was a bad example I used.

Here is code snips from the cfc itself:

cfcomponent>

<cffunction name="getERSData" access="public" output="no"  hint="Returns ERS Data based on billto and ship date">

<cfargument name="ContractID" required="true" type="string" />
<cfargument name="sortby" type="string" default="PRNumber">
<cfargument name="BeginDate" type="date">
<cfargument name="EndDate" type="date">
<cfargument name="BillTo" type="string" />

<CFQUERY NAME="ersdata" DATASOURCE="#session.reportdsn#" DBTYPE="ODBC">
 
   SELECT   *
   FROM   ers 
   WHERE   ers.ShipDate >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#arguments.BeginDate#">
   		   And ers.ShipDate <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#arguments.EndDate#"> 
		   And ers.ContractID In ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ContractID#" list="yes" > )
		   <cfif arguments.ContractID is 'VI_DEPLOYMENT'>
		   	And ers.BillToSiteID In ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.BillTo#" list="yes" > )
		   </cfif>
   ORDER BY ers.ContractID,#SortType#,ers.CustomerID
</CFQUERY>
<cfreturn ersdata>
</cffunction>

Open in new window


<cfparam name="sortType" default="PRNumber" />
<cfparam name="display_type" default="HTML" />

<cfset BeginDate = CreateODBCDate(BeginShipDate) />
<cfset EndDate = CreateODBCDate(EndShipDate) />
<cfset Contract = getMatrixRepairBillTo.param1value />
<cfset BillTo = getMatrixRepairBillTo.param2value />

<cfset get_ers = application.mrainfo.getErsData(contract,SortType,BeginDate,EndDate,BillTo) />

<cfdump var="#get_ers#" abort="true" >

<cfif !get_ers.RecordCount>
	No records match your search criteria <cfoutput>#contract# (#Billto#).</cfoutput><br />
	Please go back to the form and try again.
</cfif>

Open in new window


cfdump
SQL	SELECT * FROM ers WHERE ers.ShipDate >= ? AND ers.ShipDate <= ? AND ers.ContractID IN ( ?,? ) ORDER BY ers.ContractID,ShipDate,ers.CustomerID
SQLPARAMETERS	
array
1	2014-10-24 00:00:00.0
2	2014-10-24 00:00:00.0
3	IPC
4	INC. - A SUBWAY FRANCHISEE

Open in new window


Acutal value should be
IPC,INC.- A SUBWAY FRANCHISEE
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
ID: 40406227
You're not showing it, but I assume your code is converting a list to an array.   When doing so, it sees the comma and creates two different items.    This is the bit of code you need to modify.  Can you locate where you create the array for the contract ID
0
 

Author Comment

by:bman2011
ID: 40406247
Once again sorry for all the confusion. Disregard any of my previous posts with code in it. The only one relevant is my most recent post ID: 40405712.

Line 6 on second set of code is where the Contract value is being retrieved from.
<cfset Contract = getMatrixRepairBillTo.param1value />

I did cfdump on this line and it displayed it correctly.

Then on line 9, it passes this value to the function getERSdata (line 3 on the first set of code)
<cfset get_ers = application.mrainfo.getErsData(contract,SortType,BeginDate,EndDate,BillTo) />

Let me know if this makes any more sense :)
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 40406375
EDIT

(no points...)

>> <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ContractID#" list="yes" >

Essentially problem is what gd said earlier. You're using some sort of list function with commas as a delimiter so "IPC,INC.- A SUBWAY FRANCHISEE " gets  treated as two separate strings instead of one.  

      * IPC  
      *  INC.- A SUBWAY FRANCHISEE

cfqueryparam list="true" uses a comma as the default delimiter.  So the query that's sent to the db is actually:

      WHERE Column IN ( 'IPC',   'INC.- A SUBWAY FRANCHISEE ')   <=== notice there are two values here

... instead of this

      WHERE Column IN ( 'IPC,INC.- A SUBWAY FRANCHISEE '  )      <=== single string

You either need to use a different delimiter  (one that won't appear in the values)

     <cfqueryparam separator="|" list="True" ....>

OR if there will always be a single contract value - get rid of list="true".
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question