CF8 Auto Suggest

that should be
lastName+', '+firstName AS name
if you also want a space between them , not just a comma

also, depending on your db, concatenation syntax may be different, like
CONCAT(firstName, ', ', lastName) AS name
for example

Azadi

It's a MySql (4/5) server.  Does that change anything?
 

use CONCAT() function as i posted above - it's guaranteed to work.

Azadi

Like this?

<cfquery name="local.query" datasource="vicky" >
select          CONCAT(firstName, ', ', lastName) AS name
from            deskCalls
where           lastName like <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.search)#%" />
order by        lastName
</cfquery>

yep, but if you are using upper() to convert the value to search against to uppercase, you better convert your column value to uppercase too:
where UPPER(lastName) like <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.search)#%" />

Azadi

For some reason it isn't working at all now.  This is the current cfc:

<cfcomponent output="false">

      <!--- Lookup --->
      <cffunction name="findClient" access="remote" returntype="string">
            <cfargument name="search" type="any" required="false" default="">
            
            <!--- Variables --->
            <cfset var local = {} />
            
            <!--- Query Location Table --->
                        <cfquery name="local.query" datasource="vicky" >
                        select          CONCAT(firstName, ', ', lastName) AS name
                        from            deskCalls
                        where           UPPER(lastName) like <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.search)#%" />
                        order by        lastName
                </cfquery>
                <!--- And return  --->
                <cfreturn valueList(local.query.name)>
      </cffunction>

</cfcomponent>

oh, i just remembered that you can't have commas in the values you return for autosuggest  - it breaks the javascript behind it (because you are returning a comma-delimited list with values that have commas as well - your list ends up looking like "Bob, Smith, Barabara, Bush")

so you can't return your names as firstName, lastName. return them just as firstName lastName (without the comma between them):

CONCAT(firstName, ' ', lastName) AS name

Azadi

Did I copy this correctly?

<cfcomponent output="false"> 
	<!--- Lookup --->
	<cffunction name="findClient" access="remote" returntype="string">
		<cfargument name="search" type="any" required="false" default="">
		
		<!--- Variables --->
		<cfset var local = {} />
		
		<!--- Query Location Table --->
				<cfquery name="local.query" datasource="vicky" >
                        select          CONCAT(firstName, ' ', lastName) AS name
                        from            deskCalls
                        where           UPPER(lastName) like <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.search)#%" />
                        order by        lastName
                </cfquery> 
                <!--- And return  --->
                <cfreturn valueList(local.query.name)>
	</cffunction> 
</cfcomponent>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

yes, that is correct.

Azadi

It's not working.  The little wheel spins, but nothing is returned.  I am using a name that exists in the DB and nothing appears.  I've tried uppercase in the form, lowercase in the form.  Nada.  Thanks so much for your patience.

Brilliant!  Works great.  I have another question about how to handle after the fact, which I will put in another post.