asked on

cfselect display attribute question

I have a cfselect where I need to concat two variables "AgentFName" "AgentLName" in the display attribute. How can this be done?

<cfquery name="agent" datasource="test">
SELECT fagents.Agent,fagents.AgentLname,fagents.AgentFName from fagents
</cfquery>

<cfselect Name = "Agent" query = "agent" value = "Agent" display="AgentFName" queryPosition="below" class="selectpicker show-menu-arrow span4">
<option value=" ">Please Select</option>

</cfselect>

Thanks!

gdemaria

Concatenate the columns in the query to create one column and use that in the select

<cfquery name="agent" datasource="test">
SELECT f.Agent
,f.AgentLname + ' ' + f.AgentFName as AgentFName
from fagents
</cfquery>

Dusty

ASKER

Hi gdemaria,

When I run that I get no error, but each record is displayed as 0.0 ?? what could cause that? See attached pic."

<cfquery name="agent" datasource="test">
SELECT fagents.Agent,fagents.AgentLname + ' ' + fagents.AgentFName as Name from fagents
</cfquery>

<cfselect Name = "Agents" query = "agent" value = "Agent" display="Name" queryPosition="below" class="selectpicker show-menu-arrow span4">
<option value=" ">Please Select</option>
</cfselect>

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dusty

ASKER

I'm using MySQL I will try what you suggested. Thanks!

stu215

Check out the Concat_WS function for MySQL:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

CONCAT_WS(',', 'fagents.AgentLname', 'fagents.AgentFName') as Name

Open in new window

There is also the Concat function:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

Dusty

ASKER

SELECT fagents.Agent, CONCAT(fagents.AgentLname,' , ',fagents.AgentFName) as Name from fagents

Worked! Thanks again

_agx_

> each record is displayed as 0.0 ?? what could cause that?

FWIW, it's because MySQL only uses "+" for addition. If you use it with strings, MySQL attempts to convert the strings to numbers first. Since a name like "John" or "Smith" isn't a number, MySQL converts it to zero.

SELECT 'John' + 'Smith' AS Name <== translates to SELECT '0' + '0' AS Name

The behavior's similar to CF's Val() function. Val() converts any non-numeric values to 0.