Share via

Walkthrough: Creating a Lookup Table

  • Article

A lookup table is used to display information from one table based on the value of a foreign-key field in another table. For example, consider a table of Orders in a sales database. Each record in the Orders table includes a CustomerID indicating which customer placed the order. The CustomerID is a foreign key pointing to a customer record in the Customers table. When presenting a list of Orders (from the Orders table) you may want to display the actual customers name, as opposed to the CustomerID. Since the customers name is in the customers table, and you are presenting data from the Orders table, you need to create a lookup table, which takes the CustomerID value in the Orders record, and uses that value to navigate the relationship and return the more readable, customer name. This concept is known as a lookup table.

Tasks illustrated in this walkthrough include:

Prerequisites

In order to complete this walkthrough, you need:

Creating a New Windows Application

To create the new Windows Application project

  1. From the File menu, create a new project.

  2. Name the project LookupTableWalkthrough.

  3. Select Windows Application and click OK. For more information, see Creating Windows-Based Applications.

    The LookupTableWalkthrough project is created and added to Solution Explorer.

Creating a New Data Source

To create the data source

  1. On the Data menu, click Show Data Sources.

  2. In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.

  3. Select Database on the Choose a Data Source Type page, and then click Next.

  4. On the Choose your Data Connection page do one of the following:

    • If a data connection to the Northwind sample database is available in the drop-down list, select it.

      -or-

    • Select New Connection to launch the Add/Modify Connection dialog box. For more information, see Add/Modify Connection Dialog Box (General).

  5. If your database requires a password, select the option to include sensitive data, and then click Next.

  6. Click Next on the Save connection string to the Application Configuration file page.

  7. Expand the Tables node on the Choose your Database Objects page.

  8. Select the Customers and Orders tables, and then click Finish.

    The NorthwindDataSet is added to your project and the two tables appear in the Data Sources window.

Creating Data-bound Controls on the Form

To create data-bound controls on the form

  1. Expand the Customers node in the Data Sources window.

  2. Change the drop type of the related Orders table to Details by selecting Details from the control list on the Orders node. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.

  3. Expand the related Orders node and change the CustomerID column's drop type to a combo box by selecting ComboBox from the control list on the CustomerID node.

  4. Drag the related Orders node from the Data Sources window onto Form1.

    Data-bound controls with descriptive labels appear on the form, along with a tool strip (BindingNavigator) for navigating records. A NorthwindDataSet, CustomersTableAdapter, BindingSource, and BindingNavigator appear in the component tray.

Creating the Lookup Table

You establish the lookup table by setting properties on the CustomerID combo box:

To create the lookup table functionality on the form

  • Drag the main Customers node from the Data Sources window directly onto the CustomerID combo box on Form1.

    Note

    The table that follows is for reference only; the properties are automatically set during the drag gesture in the procedure above. The designer sets the following properties as described in the "Explanation of setting" below. Depending on your table's structures, you may need to adjust the properties for the lookup table to function properly.

    Property

    Explanation of setting

    DataSource

    Visual Studio sets this property to the BindingSource created for the table you drag onto the control (as opposed to the BindingSource created when the control was created).

    If you need to make an adjustment, then set this property to the BindingSource of the table with the column you want to display. (CustomersBindingSource for this walkthrough.)

    DisplayMember

    Visual Studio sets this property to the first column after the primary key that has a string data type for the table you drag onto the control.

    If you need to make an adjustment, then set this property to the column name you want to display.

    ValueMember

    Visual Studio sets this property to the first column participating in the primary key, or the first column in the table if no key is defined.

    If you need to make an adjustment, then set this property to the primary key in the table with the column you want to display.

    SelectedValue

    Visual Studio sets this property to the original column dragged from the Data Sources window.

    If you need to make an adjustment, then set this property to the foreign-key column in the related table. (CustomerID in the Orders table for this walkthrough.)

Running the Application

To run the application

  1. Press F5.

  2. Navigate through the orders and ensure that the CustomerID combo box displays the CompanyName for each order from the Customers table (as opposed to the CustomerID column in the Orders table).

Next Steps

Depending on your application requirements, there are several steps you may want to perform after creating a data-bound form. Some enhancements you could make to this walkthrough include:

See Also

Concepts

Displaying Data Overview

Data Sources Overview

TableAdapter Overview

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Displaying Data on Forms in Windows Applications

Editing Data in Your Application

Validating Data

Saving Data