?
Solved

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

Posted on 2006-05-21
4
Medium Priority
?
239 Views
Last Modified: 2010-08-05
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
Comment
Question by:grouchyfd
4 Comments
 
LVL 5

Accepted Solution

by:
maralans earned 1600 total points
ID: 16730376
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
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 400 total points
ID: 16730416
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
 
LVL 5

Expert Comment

by:MageDribble
ID: 16730765
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
 

Author Comment

by:grouchyfd
ID: 16737610
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question