• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Is there a way to concatenate info from 2 database columns to display together in the same listbox control?

I use VS 2005. I am building a windows app. Is there a way to take info from 2 different database columns, like last name and first name, concatenate them into a listbox that will display in the form LastName, FirstName?

Thanks
0
grouchyfd
Asked:
grouchyfd
2 Solutions
 
maralansCommented:
Access:

"SELECT Id, FirstName & ' ' & LastName As FullName, Address FROM yourTableName"

SQL:

"SELECT ID, FirstName + ' ' + LastName AS FullName, Address FROM yourTableName"


 listbox.DisplayMember = "FullName"
 listbox.ValueMember = "Id"
 listbox.DataSource = yourDataSet.Tables(0)
0
 
SanclerCommented:
Yes.

You can either do it in the Select statement by which you get your data from the database to your app's dataset/datatable or, having got the data in its separate parts you can add an expression column to the datatable to do the concatenation.

For the first approach the SQL Select statement would be on these lines

   "SELECT LastName + ", " + FirstName AS FullName FROM MyTable"

although I imagine you would want other fields in there as well.

For the second approach the code would go something like this (assuming that MyTable had in it columns named FirstName and LastName)

        Dim dc As New DataColumn
        With dc
            .ColumnName = "FullName"
            .DataType = GetType(String)
            .Expression = "LastName + ', ' + FirstName"
        End With
        MyTable.Columns.Add(dc)

In either case the column you would want to bind to to get the display you want would be FullName.

Roger

Maralans had posted while I was typing this, but I still posted it as (a) it includes the comma between the LastName and FirstName and (b) it includes the alternative approach.  But, I think, any points should be his ;-)
0
 
MageDribbleCommented:
you are better off binding in VB.  If you move from SQL Server to Oracle (or other way), the concatenation changes from "+" to "||".  You could write a function on SQL Server that concatenates and then re-create it on Oracle if you change databases.

However, if you bind in VB - you won't have to worry about swapping database back-ends.

Sancler's suggestion shows how to bind in VB
0
 
grouchyfdAuthor Commented:
It's has taken me a while to get it to work through the VB side. Thank you all for your help. I was able to use the sql statement earlier thru the QA. I wanted to make it work thru the app. side so it wouldn't be dependant on the database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now