Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Coldfuion Function reformat

Posted on 2011-10-11
14
Medium Priority
?
337 Views
Last Modified: 2012-08-14
I recently got this function from Raymond Camden's blog: http://www.coldfusionjedi.com/index.cfm/2010/4/12/Playing-with-jQuery-UIs-Autocomplete-Control 

Accept he did not use the "normal" syntax for coldfuion. Can someone convert the following code so that it conforms to the norm?
component {

	remote function searchCategories(string term) {
		var q = new com.adobe.coldfusion.query();
		q.setDatasource("#application.datasource#");
		q.setSQL("select ContactID as id, CONCAT(LastName, ', ', FirstName) as value from contacts where LastName like :search");
		q.addParam(name="search",value="%#arguments.term#%",cfsqltype="cf_sql_varchar");
		var query = q.execute().getResult();
		var result = [];
		for(var i=1; i<=query.recordCount; i++) {
			result[arrayLen(result)+1] = {};
			result[arrayLen(result)]["id"] = query.id[i];
			result[arrayLen(result)]["value"] = query.value[i];			
		}
		return result;
	}

}

Open in new window

0
Comment
Question by:jasch2244
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 18

Accepted Solution

by:
Plucka earned 1200 total points
ID: 36952854
I just wrote this by hand, so there may be some typos, but that should get you going.
<cfcomponent>
<cffunction name="searchCategories" access="remote">
    <cfargument name="term" type="string" />

    <cfset var q = "" />
    <cfquery name="q" datasource="#application.datasource#">
        select ContactID as id, CONCAT(LastName, ', ', FirstName) as value from contacts where LastName like %arguments.term%
    </cfquery>
    <cfset result = [] />
    <cfloop query="q">
        <cfset result[arrayLen(result)+1] = {} />
        <cfset result[arrayLen(result)]['id'] = id />
        <cfset result[arrayLen(result)]['value] = value />
    </cfloop>

    <cfreturn result />
</cffunction>
</cfcomponent>

Open in new window

0
 
LVL 18

Expert Comment

by:Plucka
ID: 36952858
This line

<cfset result[arrayLen(result)]['value] = value />

needs to be

<cfset result[arrayLen(result)]['value'] = value />
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 36953078
Put also # around like %#arguments.term#%
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 1

Author Comment

by:jasch2244
ID: 36953395
I must be doing something wrong here is my new code
<cfcomponent>
<cffunction name="searchCategories" access="remote">
    <cfargument name="term" type="string" />
    <cfset var q = "" />
    <cfquery name="q" datasource="########">
        select ContactID as id, CONCAT(LastName, ', ', FirstName) as value from contacts where LastName like %#arguments.term#%
    </cfquery>
    <cfset result = [] />
    <cfloop query="q">
        <cfset result[arrayLen(result)+1] = {} />
        <cfset result[arrayLen(result)]['id'] = id />
        <cfset result[arrayLen(result)]['value'] = value /> 
    </cfloop>
    <cfreturn result />
</cffunction>
</cfcomponent>

Open in new window

0
 
LVL 18

Expert Comment

by:Plucka
ID: 36953419
Whats the problem, what issue or error are you getting?

Also what version of ColdFusion are you using
0
 
LVL 3

Assisted Solution

by:devilJinKazama
devilJinKazama earned 800 total points
ID: 36953646
seems like your missing the quotes surrounding the string, try this

<cfcomponent>
<cffunction name="searchCategories" access="remote">
    <cfargument name="term" type="string" />
    <cfset var q = "" />
    <cfquery name="q" datasource="########">
        select ContactID as id, CONCAT(LastName, ', ', FirstName) as value from contacts where LastName like <cfqueryparam value="%#arguments.term#%"  cfsqltype="cf_sql_varchar">
    </cfquery>
    <cfset result = [] />
    <cfloop query="q">
        <cfset result[arrayLen(result)+1] = {} />
        <cfset result[arrayLen(result)]['id'] = id />
        <cfset result[arrayLen(result)]['value'] = value /> 
    </cfloop>
    <cfreturn result />
</cffunction>
</cfcomponent>

Open in new window

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 36954063
Sh!tt!
Like is always a String. I missed that.
0
 
LVL 1

Author Comment

by:jasch2244
ID: 36959609
Ok the following code is what I got from the person who initially created the code. I'm still getting an error of: Element ID is undefined in a Java object of type class [Ljava.lang.String;. Take note of the attached code as it has changed. In addition the return format has to be json. I"m stumped by this one... but then again it dose not take much to stump me.
<cfcomponent>
<cffunction name="searchCategories" access="remote">
	<cfargument name="term" type="string">
	<cfset var q = ""> 
	<cfset var result = []>
	<cfquery name="q" datasource="########">
	select ContactID as id, 
    CONCAT(LastName, ', ', FirstName) as value 
	from contacts
	where LastName like <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.term#">
	</cfquery>
	<cfloop query="q">
		<cfset result[arrayLen(result)+1] = {}>
		<cfset result[arrayLen(result)]["id"] = query.id[i]>
		<cfset result[arrayLen(result)]["value"] = query.value[i]>
	</cfloop>
	<cfreturn result>
</cffunction>
</cfcomponent>

Open in new window

0
 
LVL 53

Expert Comment

by:_agx_
ID: 36959632
(no points...)

> <cfloop query="q">
> <cfset result[arrayLen(result)]["id"] = query.id[ii]>

You forgot to change the query name. It should be "q" not "query".

       <cfset result[arrayLen(result)]["id"] = q.id[i]>
       <cfset result[arrayLen(result)]["value"] = q.value[i]>

Open in new window


But you do not actually need the full reference now that you're using a <cfloop query="...">.  One of the shorter versions above should work too.
0
 
LVL 1

Author Comment

by:jasch2244
ID: 36959724
That was a great find.. thanks again. Now I'm getting: Variable I is undefined. I'm pretty sure it has something to do with the for loop in the original code but some how did not make it to the new. Not sure how to define the i variable if it is looping a query.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 36959739
    > Not sure how to define the i variable if it is looping a query.

You don't ;-) You're mixing examples.  The original used a from/to loop and "i" was the index. Now that you switched to a query loop, "i" doesn't exist anymore.  It's simpler, but you need to update your code to use the syntax the others showed you in http:#a36952854 or http:#a36953646 .

Notice they got rid of q.id[..] stuff and just replaced it with the field names "id" and "value"?


 <cfloop query="q">
        <cfset result[arrayLen(result)+1] = {} />
        <cfset result[arrayLen(result)]['id'] = id />
        <cfset result[arrayLen(result)]['value'] = value /> 
    </cfloop>

Open in new window


0
 
LVL 1

Author Comment

by:jasch2244
ID: 36959814
Thanks all... finally after what seems to be an eternity I've been able to get it to work. My final working code is attached. I hope it helps someone else.
<cfcomponent>
<cffunction name="searchCategories" access="remote">
	<cfargument name="term" type="string">
	<cfset var q = ""> 
	<cfset var result = []>
	<cfquery name="q" datasource="##########">
	select ContactID as id, 
    CONCAT(LastName, ', ', FirstName) as value 
	from contacts
	where LastName like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.term#%">
	</cfquery>
    <cfloop query="q">
        <cfset result[arrayLen(result)+1] = {} />
        <cfset result[arrayLen(result)]['id'] = id />
        <cfset result[arrayLen(result)]['value'] = value /> 
    </cfloop> 
	<cfreturn result>
</cffunction>
</cfcomponent>

Open in new window

0
 
LVL 18

Expert Comment

by:Plucka
ID: 36959828
Yes in my version I didn't prefix the query columns, its not necessary to add q.
0
 
LVL 1

Author Comment

by:jasch2244
ID: 36959832
Yup... thanks again Plucka, you rock
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

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 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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

569 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