Bind an ADO.NET DataTable to a Combobox in VB.NET

You can bind a DataTable to a Combobox to display one value in the ComboBox's list while using another value to identify rows. VB6 provided the ItemData property for this purpose. .NET did away with the ItemData property but allows you to bind almost any type of data object to a ComboBox.

This code shows how to bind a DataTable to a ComboBox and display column 1 of the DataTable (the "Description" column) in the ComboBox and use column 2 ("Code") to select ComboBox items.

NOTE: a bug exists in the .NET Framework V1.1 (see article Q327244) when the ComboBox is used on a Tab Control. The bug causes the ComboBox to select its first item when the tab control's selected index changes. Also, the ComboBox cannot be cleared unless its SelectedIndex property is set to -1 twice. click here to learn how to get around this.

Retrieve the data into a DataSet:

    Dim strSQL As String = "Select Code,Description From MyTable"
    Dim Connection As New OleDbConnection("PROVIDER=....")
    Dim DA As New OleDbDataAdapter(strSQL, Connection)
    Dim DS As New DataSet

    DA.Fill(DS, "Codes")

Create and populate the DataTable to bind to the ComboBox:

    Dim dt As New DataTable
    dt.Columns.Add("Description", GetType(System.String))
    dt.Columns.Add("Code", GetType(System.String))
    '
    ' Populate the DataTable to bind to the Combobox.
    '
    Dim drDSRow As DataRow
    Dim drNewRow As DataRow

    For Each drDSRow In DS.Tables("Codes").Rows()
        drNewRow = dt.NewRow()
        drNewRow("Description") = drDSRow("Description")
        drNewRow("Code") = drDSRow("Code")
        dt.Rows.Add(drNewRow)
    Next

Bind the DataTable to the ComboBox by setting the Combobox's DataSource property to the DataTable. To display the "Description" column in the Combobox's list, set the Combobox's DisplayMember property to the name of column. Likewise, to use the "Code" column as the value of an item in the Combobox set the ValueMember property.

    ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList

    With ComboBox1
        .DataSource = dt
        .DisplayMember = "Description"
        .ValueMember = "Code"
        .SelectedIndex = 0
    End With

To select an item based on the "Code" or ValueMember property:

    Dim aIndex As Integer

    With ComboBox1
        For aIndex = 0 To .Items.Count - 1
            If CType(.Items(aIndex)(1), String).Trim = TextBox2.Text.Trim Then
                .SelectedIndex = aIndex
                Exit For
            End If
        Next

        If aIndex >= .Items.Count Then .SelectedIndex = -1
    End With




About TheScarms
About TheScarms


Sample code
version info

If you use this code, please mention "www.TheScarms.com"

Email this page


© Copyright 2024 TheScarms
Goto top of page