Fill ComboBox with DataTable result

Question

i'm trying to fill a combobox from a datatable result of a query, but it must have an empty first index and the displaymember should be concatenate

Public Function getAllSocios()
    Dim comanMySql As New MySqlCommand
    Dim ds As New DataSet
    Dim dt As DataTable
    If My.Settings.bdCom Then
        comanMySql.Connection = conMySql
        comanMySql.CommandText = "SELECT id , name, last_name FROM usuarios ORDER BY n_socio"
        comanMySql.Parameters.Clear()
        Try
            comanMySql.ExecuteNonQuery()
            Dim da As New MySqlDataAdapter(comanMySql)
            da.Fill(ds, "tabla")
            dt = ds.Tables(0)
            If dt.Rows.Count > 0 Then
                Return dt
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Trace.WriteLine(cBDError & "Error en getAllUsers ")
            Trace.WriteLine(cMySqlError & ex.Source & " - " & ex.Message)
            Trace.Flush()
        End Try
    End If

    Return Nothing
End Function

I tried to set the values by using a for loop, but i'm not able to set the valuemember dinamically (must be the id value)

    Public Sub New()
    InitializeComponent()
    socios = BD.getAllSocios
    For i As Integer = 0 To socios.Rows.Count - 1
        Me.combo_fam.Items.Add(socios.Rows(i).Item("id").ToString + " - " + socios.Rows(i).Item("name") + " " + socios.Rows(i).Item("last_name"))
    Next

Shell Shell · Accepted Answer · 2014-07-18T07:04:26

Change your query like this

SELECT (Cast(id As varchar) + ' - ' + name + ' ' + last_name) As DisplayItem, Id FROM ...

then fill the table using that query. To fill the blank item at the first row. use following code.

Dim row As DataRow = dtTable.NewRow()
row("DisplayItem") = "Enter Value or leave it blank"
row("ID") = -1
dtTable.Rows.InsertAt(row, 0)

Then bind the combobox with that datatable.

Combobox1.DisplayMember = "DisplayItem"
Combobox1.ValueMember = "Id"
Combobox1.DataSource = dtTable

Fill ComboBox with DataTable result

1 Answers