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

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.
bman2011Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
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
bman2011Author Commented:
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
gdemariaCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

bman2011Author Commented:
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
gdemariaCommented:
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
bman2011Author Commented:
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
_agx_Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.