[VB.NET] Desktop Application - Bind Datasource To Control

I have seen this question asked frequently in the VB.NET forum and I figured I would add a tutorial submission to address this in a single location.

Imagine for a second that you have a lookup table with the following schema:

Field Name	Data Type	Properties
LookupId	INT	Auto-incremented, unique, primary
LookupName	VARCHAR(64)
Slug	VARCHAR(64)	Unique

A common scenario is to setup a ComboBox that allows the user to pick the LookupName but when the data is sent to the server it sends the LookupId or Slug.

Visual Basic .NET allows for you to bind the control to a data source. By setting up the data binding, the ComboBox will have every record from the data source in its Items collection, display the user friendly LookupName, and return the database friendly LookupId (or Slug).

To bind the ComboBox, do the following:

1. Create a new instance of a DataTable
2. Create a new instance of the database connection (SqlConnection, OleDbConnection, or something else)
3. Create a new instance of the database command (SqlCommand, OleDbCommand, or something else) to select the records you wish to display in the ComboBox
4. Create a new instance of the database data adapter (SqlDataAdapter, OleDbDataAdapater, or something else)
5. Use the database data adapter's Fill method, passing the DataTable
6. Bind the ComboBox to the DataTable
7. Set the DisplayMember to the user friendly field name
8. Set the ValueMember to the database friendly field name

Here is a SQL Server example:

Code:

---

Dim dt As New DataTable()
Using con As New SqlConnection("My Connection String")
    Using cmd As New SqlCommand("SELECT Lookup.LookupId, Lookup.LookupName FROM Lookup;", con)
        Using adapter As New SqlDataAdapter(cmd)
            adapter.Fill(dt)
            With MyComboBox
                .DisplayMember = "LookupName"
                .ValueMember = "LookupId"
                .DataSource = dt
            End With
        End Using
    End Using
End Using

---

Here is a MS Access example:

Code:

---

Dim dt As New DataTable()
Using con As New OleDbConnection("My Connection String")
    Using cmd As New OleDbCommand("SELECT Lookup.LookupId, Lookup.LookupName FROM Lookup;", con)
        Using adapter As New OleDbDataAdapter(cmd)
            adapter.Fill(dt)
            With MyComboBox
                .DisplayMember = "LookupName"
                .ValueMember = "LookupId"
                .DataSource = dt
            End With
        End Using
    End Using
End Using

---

Important to Note: Notice that in the code I am setting the DataSource property last. This is on purpose, not just to screw people who are OCD about alphabetization. If you have handled the SelectedIndexChanged, SelectedValueChanged, etc. of the ComboBox and set the DataSource property first, the event will fire. This can cause issues because the SelectedValue will be null, SelectedIndex will be -1, etc.

Now that the ComboBox is bound to the DataTable, it will display the LookupName values to the user. Once you need to get the selected LookupId value, you would simply get the SelectedValue of the ComboBox and optionally convert it to an Integer:

Code:

---

Dim objLookupId As Object = MyComboBox.SelectedValue
Dim intLookupValue As Integer = Convert.ToInt32(objLookupId)

' do something with the LookupId

---