We help IT Professionals succeed at work.

cfselect display attribute question

Bang-O-Matic
Bang-O-Matic asked
on
337 Views
Last Modified: 2014-05-27
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!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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>

Author

Commented:
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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I'm using MySQL I will try what you suggested. Thanks!
stu215Systems Analyst/Project Manager/Programmer
CERTIFIED EXPERT

Commented:
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

Author

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

Worked! Thanks again
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>  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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.