1.8 Bind Data to ComboBox and DataGrid Controls

Sometimes you might want to use a ComboBox control instead of a ListBox control to display a list of choices. You also might want to display information in a grid style based on the item chosen in that combo box. This How-To describes how to bind data to both ComboBox and DataGrid controls.

Instead of using a ListBox control to display customers, you would like to use a ComboBox control to display them. After you choose a customer, you would like to display information about the orders that belong to that customer. How do you bind data to the ComboBox and then bind the DataGrid control as well?

Technique

To bind both the ComboBox and the DataGrid controls, you will use the same properties and coding techniques that you have used for the ListBox control. You will first create DataAdapter and DataSet controls to which to set the DataSource properties. The DataAdapter for the DataGrid will include a parameter that will use the value selected in the ComboBox control.

You will set the DataSource properties of the controls to the DataSet created. That is all you really have to set for the DataGrid control. For the ComboBox control, you will also set the ValueMember and DisplayMember properties.

You will then add code to fill the DataSet control to which the DataSource property is set for the ComboBox control. Finally, you will create a subroutine to store the selected value in the ComboBox in the DataAdapter that was created for filling the DataSet on which the DataGrid control is based.

After the user has selected a value in the ComboBox that contains the customers, the DataGrid control displays the orders for that customer, as can be seen in Figure 1.14.

Figure 1.14. Users can display the header information for customer orders using these two controls.

graphics/01fig14.jpg

Steps

You will be starting with a fresh form.

  1. Add a new Windows Form called frmHowTo1_8.

  2. Add the OleDbDataAdapter and DataSet controls with the properties set forth in Table 1.7.

    Table 1.7. OleDataAdapter and DataSet Controls

    Object

    Property

    Setting

    OleDataAdapter

    Name

    odaCustomerList
     

    SelectCommand

    OleDbSelectCommand1
     

    CommandText

    SELECT CustomerID, CompanyName FROM Customers

    DataSet

    Name

    dsCustomerList
     

    DataSetName

    dsCustomerList

    OleDataAdapter

    Name

    odaOrdersForCustomer
     

    SelectCommand

    OleDbSelectCommand2
     

    CommandText

    SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE (CustomerID = ?) ORDER BY OrderDate

    DataSet

    Name

    dsOrdersForCustomer
     

    DataSetName

    dsOrdersForCustomer

    Tip

    graphics/tip_icon.gif

    You will want to create the OleDbDataAdapter controls using the Data Adapter Configuration Wizard, and then set the name after the DataAdapter has been created. You will want to create the DataSet controls by right-clicking on the appropriate OleDbDataAdapter and choosing Generate Dataset. If you have questions about creating these controls, reread How-To 1.1.

    Also, remember that when generating the DataSet, VS always changes the name by adding a 1 on the end and capitalizing the first letter. You might want to change it to what you really want in the properties directly.

  3. Add the ComboBox and DataGrid controls, as well as the Label for the ComboBox, setting the properties as shown in Table 1.8.

    Table 1.8. Label, ComboBox, and DataGrid Controls Property Settings

    Object

    Property

    Setting

    Label

    Name

    Label1
     

    Text

    "Customers to List Orders For:"

    ComboBox

    Name

    cboCustomers
     

    DataSource

    dsCustomerList.Customers
     

    DisplayMember

    CompanyName
     

    ValueMember

    CustomerID

    DataGrid

    Name

    dgOrders
     

    DataSource

    dgOrdersForCustomer.Orders

  4. Add code shown in Listing 1.23 to the Load event of the form. This code fills the dsCustomerList DataSet control based off the select statement used in the odaCustomerList OleDbDataAdapter control. After this occurs, the RefreshOrders subroutine is called, displayed in Listing 1.24.

    Listing 1.23 frmHowTo1_8.vb: Filling the Dataset Used by the ComboBox Control
    Private Sub frmHowTo1_8_Load(ByVal sender As Object, _
                    ByVal e As System.EventArgs) Handles MyBase.Load

        Me.odaCustomerList.Fill(Me.dsCustomerList)
        RefreshOrders()

    End Sub
    Listing 1.24 frmHowTo1_8.vb: Filling the Dataset Used by the DataGrid Control, Based on the Item Selected in the ComboBox Control
    Private Sub RefreshOrders()

        '- Clear Orders for customer dataset
        Me.dsOrdersForCustomer.Clear()

        '- Check to see if an item was selected
        If Me.cboCustomers.SelectedIndex <> -1 Then

            '- Store the selected customer ID into the parameter
       '    the SQL data adapter
            Me.odaOrdersForCustomer.SelectCommand.Parameters(0).Value = _
                        Me.cboCustomers.SelectedItem(0)

            '- Fill the dataset
            Me.odaOrdersForCustomer.Fill(Me.dsOrdersForCustomer)

        End If

    End Sub

  5. Add the code in Listing 1.25 to the SelectedIndexChanged event of the cboCustomers ComboBox controls.

    Listing 1.25 frmHowTo1_8.vb: Refreshing the DataGrid Control Based on Selected Item
    Private Sub cboCustomers_SelectedIndexChanged(ByVal sender
                    As System.Object, ByVal e As System.EventArgs)
                    Handles cboCustomers.SelectedIndexChanged

        RefreshOrders()

    End Sub

How It Works

When the form is first loaded or a new item is selected in the ComboBox control, the DataGrid control is filled with order information for the selected customer.

Comments

As you can see from this How-To, using the ComboBox control and DataGrid controls are not much tougher than using the ListBox control. The DataGrid control does offer quite a bit more power to let you create Main/Subform type forms such as invoices. You will see an example of this in the next How-To. The DataGrid control also lets you edit data.