Coldfusion Auto suggest with Jquery

I am trying to use Ray Camden's code from this page: http://www.coldfusionjedi.com/index.cfm/2010/4/12/Playing-with-jQuery-UIs-Autocomplete-Control

I am able to get it to work just fine, but would like to add/append a first name in the input when it returns the value. So when it returns a value it would be something like Lastname,Firstname.

Can someone help me accomplish this please. See code.
CFM PAGE////
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/redmond/jquery-ui.css">

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>
<script type="text/javascript">
$(function() {
	$("#category").autocomplete({
		source: "service2.cfc?method=searchcategories&returnformat=json",
		select:function(event,ui) {
			$("#catid").val(ui.item.id)
		}
	});
});
</script>

</head>

<body>
<form action="Test-AutoSuggest.cfm" method="post">	
category: <input name="category" id="category" />
<input name="catid" id="catid" type="hidden">
<input type="submit" value="Submit">
</form>

<cfif not structIsEmpty(form)>
	<cfdump var="#form#" label="Form">
</cfif>
</body>
</html>

SCRIPT BASED CFC //////
component {

	remote function searchCategories(string term) {
		var q = new com.adobe.coldfusion.query();
		q.setDatasource("#application.datasource#");
		q.setSQL("select ContactID as id, LastName as value, FirstName 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?
 
_agx_Commented:
> .setSQL("select ContactID as id, LastName as value, FirstName from contacts

Just change your query so "value" is a concatenation of Lastname, Firstname. The syntax is db dependent. For mySQL something like

      SELECT ContactID as id, CONCAT(LastName, ', ', FirstName) AS Value FROM  contacts .....

For MS SQL:  

      SELECT ContactID as id, LastName +', '+ FirstName AS Value FROM  contacts .....

0
 
jasch2244Author Commented:
What do you think of the cfc being in the format it is in? Should I convert to proper cfc code? I uploaded it to my sever and the it's not working, but locally it is. Weird. Thanks in advanced.
0
 
_agx_Commented:
The code looks ok.  If it's working on one server but not another maybe it's a path problem? Can you invoke it manually? Either via url or via cfinvoke?

Something like
http://yourserver.com/service2.cfc?method=searchcategories&returnformat=json&term=someTermHere
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jasch2244Author Commented:
It's throwing an error:
Could not find the ColdFusion component or interface com.adobe.coldfusion.query. and is pointing to this line:
4 : var q = new com.adobe.coldfusion.query();

I did not know what that statement was anyway I've been Googling it and can't seem to find what the heck it is... but works locally
0
 
_agx_Commented:
Is your prod server running CF9?
0
 
jasch2244Author Commented:
Yes as I just purchased a new hosting plan. I'll double check though.
0
 
_agx_Commented:
Also, try it without the full path

       var q = new query();
0
 
jasch2244Author Commented:
Still nothing... I just got off the horn with hosting co. and verified I'm on CF9. My new cfc is attached and I have a link here: http://www.northernkyhomesearch.com/TESTPAGES/Test-AutoSuggest.cfm
component {

	remote function searchCategories(string term) {
		var q = new query();
		q.setDatasource("############");
		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
 
_agx_Commented:
> verified I'm on CF9
May as well dump the server scope to verify it.

I can't say I've heard of that error, outside of using CF9 syntax on a CF8 server that is ...  If the query's really the problem, you could always switch back to using cfml syntax.

0
 
jasch2244Author Commented:
Sorry how do you dump the server scope? Do you want me to ask another question for points? I think I'm pressing my luck :)
0
 
_agx_Commented:
Nah :) That one's easy.  Only catch is some hosts disable it (no idea why).

     <cfdump var="#server#">
0
 
_agx_Commented:
> Only catch is some hosts disable it (no idea why).

Disable cfdump I mean.  If it's disabled, just use:
 
<cfoutput>
version = #server.coldfusion.productversion#
</cfoutput>
0
 
jasch2244Author Commented:
Cool! my version is: version = 9,0,1,274733
0
 
_agx_Commented:
Hm... then we're back to wondering about the error.

I know it says line #4 is the problem,  but sometimes the messages are off.  What happens if you return an empty 2d array? Or switch to cfml syntax.
0
 
jasch2244Author Commented:
Not sure how to convert all that to cfml syntax. I'll create another question as your solution worked locally.
0
 
jasch2244Author Commented:
Oh yeah Ray Camden "creater of the code" needed it to be in CFC format as it uses JSON formatting or something of the sort. Does CFML language support this?
0
 
_agx_Commented:
It should.  Not positive about the returnType but something like this cfml should work


<cfcomponent>
     <cffunction name="searchCategories" access="remote">
           <cfargument name="term" type="string" default="">

          <cfset var q = "">
	      <cfset var result = []>

           <cfquery name="q" datasource="cfartgallery">
		SELECT ContactID as id, CONCAT(LastName, ', ', FirstName) AS Value
		FROM   contacts 
		WHERE LastName LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar">
	     	</cfquery>

	     <cfscript>
			for(var i=1; i<= q.recordCount; i++) {
				result[arrayLen(result)+1] = {};
				result[arrayLen(result)]["id"] = q.id[i];
				result[arrayLen(result)]["value"] = q.value[i];			
			}
			return result;
	     </cfscript>
      </cffunction>
</cfcomponent>

Open in new window

0
 
_agx_Commented:
I have to pick up a friend shortly, so I didn't bother converting the for ... loop to cfml ;-)
0
 
_agx_Commented:
> new com.adobe.coldfusion.query

I think I may know what the problem is.  I'm betting your prod server doesn't have a mapping for "com.adobe.coldfusion". That's why you get an error when you try and create a new query(), because CF can't find the query.cfc.  On your local dev server you probably have that mapping because the files exist under custom tags:

      C:\ColdFusion9\CustomTags\com\adobe\coldfusion\query.cfc

0
 
jasch2244Author Commented:
I'm checking on it now great suggestion and thank you again. I'm going to open up a new question and have the code Ray offered to be converted into a "true" function. Thanks again
0
 
jasch2244Author Commented:
I don't know what I would do without you _agx you are a true asset to this website. Thanks again
0
 
_agx_Commented:
You're very welcome :)
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.