concatenate two fields in a dropdown

Question

in an sql table there's an id, first name and last name field. i'd like to concatenate the first and the last name fields and display it as one in a dropdown control.

this is the vb.net code:

con()
    sqry = "[SELECT QUERY]"
    sqcom = New SqlCommand(sqry, sqcon)
    da.SelectCommand = sqcom

    ds.Clear()
    da.Fill(ds)
    ddl_adv.DataSource = ds
    ddl_adv.DataTextField = "emp_fname"
    ddl_adv.DataValueField = "emp_no"

    ddl_adv.DataBind()
    sqcon.Close()

^this code displays only the first name. how do i go about concatenating in asp.net?

Answer 1

Would it work if you used something like this?

sqry = "SELECT emp_no, emp_fname+' '+emp_lname as emp_fullname FROM employee"
sqcom = New SqlCommand(sqry, sqcon)
da.SelectCommand = sqcom

ds.Clear()
da.Fill(ds)
ddl_adv.DataSource = ds
ddl_adv.DataTextField = "emp_fullname"
ddl_adv.DataValueField = "emp_no"

ddl_adv.DataBind()
sqcon.Close()

Answer 2

You need to re-work the items in your data object (ds in your case) to contain a property that is the concatenation of the first and last names.

What version of VB.NET are you using? If you are using (or can use) .NET 3.5 then you might find that LINQ to SQL (or another ORM) will make your data access life easier as it provides you with strongly typed objects that relate to the data in your database.