Coldfuion Function reformat

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

LVL 1
jasch2244Asked:
Who is Participating?
 
PluckaCommented:
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
 
PluckaCommented:
This line

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

needs to be

<cfset result[arrayLen(result)]['value'] = value />
0
 
ZvonkoSystems architectCommented:
Put also # around like %#arguments.term#%
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
jasch2244Author Commented:
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
 
PluckaCommented:
Whats the problem, what issue or error are you getting?

Also what version of ColdFusion are you using
0
 
devilJinKazamaCommented:
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
 
ZvonkoSystems architectCommented:
Sh!tt!
Like is always a String. I missed that.
0
 
jasch2244Author Commented:
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
 
_agx_Commented:
(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
 
jasch2244Author Commented:
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
 
_agx_Commented:
    > 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
 
jasch2244Author Commented:
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
 
PluckaCommented:
Yes in my version I didn't prefix the query columns, its not necessary to add q.
0
 
jasch2244Author Commented:
Yup... thanks again Plucka, you rock
0
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.

All Courses

From novice to tech pro — start learning today.