Autosuggest with Different First Character Return

It works if I return "Simpson Bart" but not "Simpson, Bart", even if I return valueList(query.value, ';')

Oh well, good enough.

"inner join" means the same as "join" It's also equivalent to the syntax of the query you posted.

To troubleshoot your problem, you have to run your query in circumstances where you can see debugging info. I'm guessing autosuggest is not one of those circumstances.

Also, I'm pretty sure that IS an inner join. My database does not allow for "INNER JOIN" syntax.

Even though my data is returned correctly from the query I took your advice and switched to the CF ucase() function. I am still having the same problem.

I add in cfqueryparam before I make pages live, but I do use it.

Thanks!

> upper('#search#%')

that is not right. you are trying to use a db function on a cf variable.
it should instead be '#ucase(search)#%'

your query is probably better re-written using INNER JOIN:

SELECT v.fistname || ' ' || v.lastname name
FROM consultants_v v INNER JOIN employees_v ae ON v.employee_id =
ae.employee_id
WHERE (UPPER(v.firstname) LIKE '#UCASE(search)#%') OR (UPPER(v.lastname)
LIKE '#UCASE(search)#%')
GROUP BY v.lastname, v.firstname
ORDER BY name

btw, i am not sure you need that GROUP BY clause there at all...

and, please, DO use <cfqueryparam> tag in your queries!

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Alas, it is not case sensitive. Here is my query:
select v.firstname || ' ' || v.lastname name
from consultants_v v, employees_v ae
where v.employee_id = ae.employee_id
AND (upper(v.firstname) like upper('#search#%')
OR upper(v.lastname) like upper('#search#%'))
group by v.lastname, v.firstname
ORDER BY name

yes, as Dan said, you seem to be performing case-sensitive checking of
names in your db. depending on your db, use available string
manipulation functions to convert the case. i.e. in MySQL it would be
something like:

...
WHERE LOWER(first_name) LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#lcase(keyword)#%"> OR LOWER(last_name) LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="#lcase(keyword)#%">

your db's function to convert the case of column value may be different...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Looks like your cfc code is case sensitive.