Avatar of Dusty
Dusty
Flag for United States of America 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!
ColdFusion Language

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
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>

screenshot
ASKER CERTIFIED SOLUTION
gdemaria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dusty

ASKER
I'm using MySQL I will try what you suggested. Thanks!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.