Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Walkthrough: Creating a Lookup Table on a Data-bound Windows Form

Visual Studio .NET 2003
 

Steve Stein
Visual Studio® Team
Microsoft Corporation

July 2003

Applies to:
   Microsoft Visual Basic® .NET
   Microsoft Visual C#® .NET
   Windows Forms

Summary: This paper describes how to create a lookup table by creating a data entry form for the Orders table in the Northwind database. Instead of displaying the CustomerID field, a combo box will be used to display the CompanyName from the Customers table that corresponds to the correct CustomerID in the Orders table. (9 printed pages)

Contents

Creating a New Project
Creating a Data Connection in Server Explorer
Adding a Connection Object and Two Data Adapters to the Form
Adding a Typed Dataset to the Project
Adding Controls to Display and Navigate the Data
Adding Code to Fill the Tables in the Dataset and Bind the Controls to the Appropriate Columns of Data
Adding Code to Navigate the Data
Running the Application
Conclusion
Next Steps

Creating a New Project

Begin this walkthrough by creating a new Windows® application in Microsoft® Visual Basic® or Visual C#®.

To create the new project

  • Create a new Windows Application project named LookupTableExample, then click OK.

    The LookupTableExample project is added to Solution Explorer and a new Windows Form (Form1) appears in the designer.

Creating a Data Connection in Server Explorer

A data connection in Server Explorer facilitates creating data objects visually by allowing you to drop data items onto forms and designers. A connection to the Northwind sample database is required for this walkthrough.

To add a data connection in Server Explorer

  • In Server Explorer, create a new connection to the Northwind sample database. For more information, see Adding New Data Connections in Server Explorer.
    Security Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.

Adding a Connection Object and Two Data Adapters to the Form

Communication between the application and the Northwind database requires a connection object. To query and update data in the database, a data adapter is needed. This application uses one connection and two data adapters.

To create the connection and adapters

  1. In Server Explorer, expand the Northwind connection.
  2. Expand the Tables area.
  3. Drag the Orders table onto the form.

    A Connection object and a DataAdapter object appear in the component tray below the form.

  4. Rename the data adapter daOrders.
  5. Rename the connection dcNorthwind.
  6. Drag the Customers table onto the form.

    Another data adapter appears.

  7. Rename that data adapter daCustomers.

Adding a Typed Dataset to the Project

The application uses a typed dataset named DsNorthwind to hold data from the Orders and Customers tables.

To create the DsNorthwind dataset

  1. From the Data menu, choose Generate Dataset.

    The Generate Dataset dialog box appears.

  2. Click New and name the dataset DsNorthwind.
  3. Click the Customers and Orders tables.
  4. Click the Add this dataset to the designer option.
  5. Click OK.

    An instance named DsNorthwind1 appears in the component tray.

Adding Controls to Display and Navigate the Data

A ComboBox control is used to display the CompanyName field from the Customers table, which is the lookup table. Text boxes are used to display the other fields and buttons are used to navigate back and forth through the data.

To add controls to display and navigate the data

  1. Add the following controls to Form1 and change the default names:
    Control Name
    TextBox1 txtOrderID
    ComboBox1 CustomerLookup
    TextBox2 txtEmployeeID
    TextBox3 txtOrderDate
    TextBox4 txtRequiredDate
    TextBox5 txtShippedDate
    TextBox6 txtShipVia
    TextBox7 txtFreight
    TextBox8 txtShipName
    TextBox9 txtShipAddress
    TextBox10 txtShipCity
    TextBox11 txtShipRegion
    TextBox12 txtShipPostalCode
    TextBox13 txtShipCountry
    Button1 btnNext
    Button2 btnPrevious
  2. Set the Text property as follows:
    Control Text
    Button1 Next
    Button2 Previous
  3. If you want, add a label to each control.

Adding Code to Fill the Tables in the Dataset and Bind the Controls to the Appropriate Columns of Data

Populate the dataset by calling the Fill method of the data adapter and bind the controls by adding a Binding object to each controls DataBindings collection.

To fill the dataset and bind the controls to the data

  1. Double-click an empty area of Form1.

    The Code Editor opens with an empty Form1_Load event handler.

  2. Add code so that the Form1_Load event handler looks like the following:
    ' Visual Basic
    Private Sub Form1_Load(ByVal sender As Object, ByVal e _
            As System.EventArgs) Handles MyBase.Load
        daCustomers.Fill(DsNorthwind1, "Customers")
        daOrders.Fill(DsNorthwind1, "Orders")
    
        ' Bind the controls to the datasource.
        txtOrderID.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.OrderID"))
        txtEmployeeID.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.EmployeeID"))
        txtOrderDate.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.OrderDate"))
        txtRequiredDate.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.RequiredDate"))
        txtShippedDate.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShippedDate"))
        txtShipVia.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipVia"))
        txtFreight.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.Freight"))
        txtShipName.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipName"))
        txtShipAddress.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipAddress"))
        txtShipCity.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipCity"))
        txtShipRegion.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipRegion"))
        txtShipPostalCode.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipPostalCode"))
        txtShipCountry.DataBindings.Add(New Binding("Text", _
            DsNorthwind1, "Orders.ShipCountry"))
    
        ' Set up the lookup table.
        With CustomerLookup
            .DataSource = DsNorthwind1
            .DisplayMember = "Customers.CompanyName"
            .ValueMember = "Customers.CustomerID"
            .DataBindings.Add(New Binding("SelectedValue", _
                 DsNorthwind1, "Orders.CustomerID"))
        End With
    End Sub
    
    // C#
    private void Form1_Load(object sender, System.EventArgs e)
    {
        daCustomers.Fill(dsNorthwind1);
        daOrders.Fill(dsNorthwind1);
    
        // Bind the controls.
        txtOrderID.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.OrderID"));
        txtEmployeeID.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.EmployeeID"));
        txtOrderDate.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.OrderDate"));
        txtRequiredDate.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.RequiredDate"));
        txtShippedDate.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShippedDate"));
        txtShipVia.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipVia"));
        txtFreight.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.Freight"));
        txtShipName.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipName"));
        txtShipAddress.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipAddress"));
        txtShipCity.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipCity"));
        txtShipRegion.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipRegion"));
        txtShipPostalCode.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipPostalCode"));
        txtShipCountry.DataBindings.Add(new Binding("Text",dsNorthwind1,"Orders.ShipCountry"));
    
     // Set up the lookup table.
        CustomerLookup.DataSource = dsNorthwind1;
        CustomerLookup.DisplayMember = "Customers.CompanyName";
        CustomerLookup.ValueMember = "Customers.CustomerID";
        CustomerLookup.DataBindings.Add(new Binding("SelectedValue",dsNorthwind1,"Orders.CustomerID"));
    }
    

Adding Code to Navigate the Data

The data is navigated by changing the Position property of the BindingContext.

Add the navigation code to the click events of the btnNext and btnPrevious buttons.

To move forward through the data

  1. Double-click the Next button.

    The Code Editor opens with an empty btnNext_Click event handler.

  2. Add code so that the btnNext_Click event handler looks like the following:
    ' Visual Basic
    Private Sub btnNext_Click(ByVal sender As Object, ByVal e _
            As EventArgs) Handles btnNext.Click
        BindingContext(DsNorthwind1, "Orders").Position += 1
    End Sub
    
    // C#
    private void btnNext_Click(object sender, System.EventArgs e)
    {
        BindingContext[dsNorthwind1, "Orders"].Position += 1;
    }
    

To move backward through the data

  1. Double-click the Previous button.

    The Code Editor opens with an empty btnPrevious_Click event handler.

  2. Add code so that the btnPrevious_Click event handler looks like the following:
    ' Visual Basic
    Private Sub btnPrevious_Click(ByVal sender As Object, ByVal e _
            As EventArgs) Handles btnPrevious.Click
        BindingContext(DsNorthwind1, "Orders").Position -= 1
    End Sub
    
    // C#
    private void btnPrevious_Click(object sender, System.EventArgs e)
    {
        BindingContext[dsNorthwind1, "Orders"].Position -= 1;
    }
    

Running the Application

Press F5 to run the application.

Move through the records and notice the ComboBox control displays the CompanyName from the Customers table, based on the CustomerID value in the Orders table.

Conclusion

To create a lookup table, set the DisplayMember property to the column (typically in the related table) you want to appear as the Text value in the control, and then set the ValueMember property to the column containing the actual value in the database field (typically the foreign key field).

Note   This example uses a ComboBox control for the lookup table, but you can also use a ListBox or a CheckedListBox control.

Next Steps

  1. Add more lookup tables to the form.

    There are two more fields that could benefit from a lookup table in this application: EmployeeID and ShipVia.

    1. Replace the TextBox control for the EmployeeID field with a ComboBox control that displays more meaningful data, such as the employee's first or last name from the Employees table.
    2. Replace the TextBox control for the ShipVia field with a ComboBox control that displays the CompanyName from the Shippers table.
      Note   You need to create more data adapters to fetch the data from the additional tables, then you must regenerate the DsNorthwind dataset so that it will include the new tables.
  2. Add functionality to update the database.

    Add a button that calls the Update method of the data adapter.

    Tip   Wrap the data adapter's update call in a try/catch block to ensure that your application gracefully handles any unforeseen connectivity problems.
  3. Add functionality to add new records to the Orders table.

    Add a button that calls the AddNew method of the BindingContext object. Here is part of the code:

    ' Visual Basic
    BindingContext(DsNorthwind1, "Orders").AddNew()
    
    // C#
    BindingContext[dsNorthwind1, "Orders"].AddNew();
    
    
Show:
© 2015 Microsoft