?
Solved

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

Posted on 2014-10-24
7
Medium Priority
?
137 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

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 1000 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 1000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month10 days, 9 hours left to enroll

765 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