Solved

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

Posted on 2014-10-24
7
131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

707 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