Solved

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

Posted on 2014-10-24
7
111 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now