DisplayMember from 2 database columns

RussGreen
RussGreen used Ask the Experts™
on
I have a function which i use a lot to populate combos with database data. It assigns the contents of one database column to ValueMember and another database column as the DisplayMember. I want to alter it slightly to display to database columns in the DisplayMember e.g.

It does this:
ValueMember  = ClientID  
DisplayMember = LName  

But I want it to do this
ValueMember  = ClientID  
DisplayMember = LName + FName

    Public Function PopulateListContol(ByVal ListControl As Object, ByVal ValMem As String, _
    ByVal DisMem As String, ByVal Database As String, ByVal DataTable As String, _
    ByRef ds As DataSet)
        Try
            'connection stuff
            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM " & DataTable & " ORDER BY " & DisMem, _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database)

            da.Fill(ds)

            ListControl.DataSource = ds.Tables(0)
            ListControl.DisplayMember = DisMem
            ListControl.ValueMember = ValMem

        Catch ex As Exception
            MsgBox(ex.Source & ", " & ex.Message)
        End Try
    End Function




Tried to do this but it just populates the list control with the ValMem...

    Public Function PopulateListContol(ByVal ListControl As Object, ByVal ValMem As String, _
    ByVal DisMem1 As String, ByVal Database As String, ByVal DataTable As String, _
    ByRef ds As DataSet, Optional ByVal DisMem2 As String = "")
        Try
            'connection stuff
            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM " & DataTable & " ORDER BY " & DisMem1, _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database)

            da.Fill(ds)

            Dim DisMem As String = DisMem1 & ", " & DisMem2

            ListControl.DataSource = ds.Tables(0)
            ListControl.DisplayMember = DisMem
            ListControl.ValueMember = ValMem

        Catch ex As Exception
            MsgBox(ex.Source & ", " & ex.Message)
        End Try
    End Function


Any ideas?

Regards
Russ Green
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
There is no direct way of popultaing 2 column values into listcontrol or combo contro. You will have to maulayy do it by looping through each item in your data collection and constructing list items and adding it to list control

Here is a proptotype just to explain the concept. You can implement it in VB..

for (i=0; i < number of data items; i++)
{
 ListItem newitem = new ListItem();
 newitem.Text = ds.Tables[0].row[0]["FName"] + ds.Tables[0].row[0]["LName"];
 ListControl.Add(newITem);
}
Commented:
You can concatenate the Name in the query:
Change
SELECT * FROM
to
SELECT *, L_Name + ', ' + F_Name as FullName FROM

ListControl.DisplayMember = "FullName"

                                  Greets, J.P.

Author

Commented:
I've made my function into this now. But when there is is a database field that is empty of just contains " " the entire dismem is not visible.

An ideas?

    Public Function PopulateListContol(ByVal ListControl As Object, ByVal ValMem As String, _
    ByVal DisMem1 As String, ByVal DisMem2 As String, ByVal Database As String, ByVal DataTable As String, ByRef ds As DataSet)
        Try
            'connection stuff
            Dim da As New OleDb.OleDbDataAdapter("SELECT *," & DisMem1 & " + ', ' + " & DisMem2 & " as DisMem FROM " & DataTable & " ORDER BY " & DisMem1, _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database)

            da.Fill(ds)

            ListControl.DataSource = ds.Tables(0)
            ListControl.DisplayMember = "DisMem"
            ListControl.ValueMember = ValMem

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial