Populate ComboBox from Database query

Question

I see many of the answers to similar question where people are saying that in order to get the value of the item loaded in combobox you need to use

  combobox1.displayMamer =""
  combobox1.valuemember=""
  combobox1.datasource=""

But this stuff does not work.....

here is what I have....

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Using con As New SqlConnection(sConnection)
        con.Open()
        Using com As New SqlCommand("Select Code1, Code2 from  tblTable6 where fldname ='Things'", con)
            Using rdr = com.ExecuteReader
                If rdr.HasRows Then
                    Do While rdr.Read = True
                        ComboBox1.Items.Add(rdr.GetString(0))
                        ''''missing something here
                    Loop
                    con.Close()
                End If
            End Using
        End Using
    End Using
End Sub

I'm selecting Code1 and Code2 from Table, i want to be able to display code1 and when selected, I want to be able to have the value of Code2, but with displayMember, and ValueMember, I'm not seeing any result.

EDIT: Here's all my code:

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Using con As New SqlConnection(sConnection)

        Using com As New SqlCommand("Select Label, Code from Table.....", con)
            con.Open()
            Dim dt As New DataTable()
            Dim rows = dt.Load(com.ExecuteReader)
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "Code"
            ComboBox1.ValueMember = "Label"
            con.Close()
        End Using
    End Using

End Sub

Dim rows = dt.Load(com.ExecuteReader) --- this line gets underlined

ERROR says: Expression does not produce value

EDIT2:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Using con As New SqlConnection(sConnection)
        con.Open()
        Using com As New SqlCommand("Select Label, Code from  tblData where fldname ='M'", con)

            Dim dt As New DataTable()
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "Code"
            ComboBox1.ValueMember = "Label"
            con.Close()
        End Using
    End Using

End Sub

Now I get another error saying that: Cannot bind to the new value member. This happens on combobox1.valuemember="Label"

You don't need that HasRows check. Do While rdr.Read will just skip the loop if there aren't any rows. — LarsTech
change it to use your actual SQL - column, table names WHERE clause etc. I changed it to present a more general solution since I have no idea about the data — Ňɏssa Pøngjǣrdenlarp
@plutonix - i did change the SQL statement, I deleted it right before I pasted it in here — BobSki
Get rid of the rows thing - I got it confused with a different way to fill a Datatable. if you need the number of rows, use dt.Rows.Count. sorry — Ňɏssa Pøngjǣrdenlarp
@plutonix please see edit2. No worries thank you for your help! — BobSki

Ňɏssa Pøngjǣrdenlarp Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-10-19T19:35:37

Rather than populating the items collection, you can bind a DataTable to the control to be used as the Datasource. Then, you can tell it which element to display and which value to submit to you when there is a selection:

Using con As New SqlConnection(sConnection)
    Using com As New SqlCommand("Select Id, Name FROM ....", con)
        con.Open()

        Dim dt As New DataTable()
        dt.Load(com.ExecuteReader)
        cbox1.Datasource = dt
        cbox.DisplayMember = "Name"
        cbox.ValueMember = "Id"
    End Using
End Using

"Name" and "Id" would be column names from the database table. The event you probably want to work with in this case would be the SelectedValueChanged and SelectedValue would hold the ID related to the item selected. This will be returned as Object so you may need to cast it back to whatever it is.

You can also bind to List(Of T) collections in the same way. In that case, the SelectedItem could be the entire object. For instance, using a List(of Employee), SelectedItem would be the object for the Employee the user selected.

Populate ComboBox from Database query

1 Answers