How to: Create a Lookup Table with the Windows Forms BindingSource Component
A lookup table is a table of data that has a column that displays data from records in a related table. In the following procedures, a ComboBox control is used to display the field with the foreign-key relationship from the parent to the child table.
To help visualize these two tables and this relationship, here is an example of a parent and child table:
CustomersTable (parent table)
CustomerID |
CustomerName |
|---|---|
712 |
Paul Koch |
713 |
Tamara Johnston |
OrdersTable (child table)
OrderID |
OrderDate |
CustomerID |
|---|---|---|
903 |
February 12, 2004 |
712 |
904 |
February 13, 2004 |
713 |
In this scenario, one table, CustomersTable, stores the actual information you want to display and save. But to save space, the table leaves out data that adds clarity. The other table, OrdersTable, contains only appearance-related information about which customer ID number is equivalent to which order date and order ID. There is no mention of the customers' names.
Four important properties are set on the ComboBox Control (Windows Forms) control to create the lookup table.
The DataSource property contains the name of the table.
The DisplayMember property contains the data column of that table that you want to display for the control text (the customer's name).
The ValueMember property contains the data column of that table with the stored information (the ID number in the parent table).
The SelectedValue property provides the lookup value for the child table, based on the ValueMember.
The procedures below show you how to lay out your form as a lookup table and bind data to the controls on it. To successfully complete the procedures, you must have a data source with parent and child tables that have a foreign-key relationship, as mentioned previously.
To create the user interface
From the ToolBox, drag a ComboBox control onto the form.
This control will display the column from parent table.
Drag other controls to display details from the child table. The format of the data in the table should determine which controls you choose. For more information, see Windows Forms Controls by Function.
Drag a BindingNavigator control onto the form; this will allow you to navigate the data in the child table.
To connect to the data and bind it to controls
Select the ComboBox and click the Smart Task glyph to display the Smart Task dialog box.
Select Use data bound items.
Click the arrow next to the Data Source drop-down box. If a data source has previously been configured for the project or form, it will appear; otherwise, complete the following steps (This example uses the Customers and Orders tables of the Northwind sample database and refers to them in parentheses).
Click Add Project Data Source to connect to data and create a data source.
On the Data Source Configuration Wizard welcome page, click Next.
Select Database on the Choose a Data Source Type page.
Select a data connection from the list of available connections on the Choose Your Data Connection page. If your desired data connection is not available, select New Connection to create a new data connection. For more information, see Add/Modify Connection Dialog Box (General).
Click Yes, save the connection to save the connection string in the application configuration file.
Select the database objects to bring into your application. In this case, select a parent table and child table (for example, Customers and Orders) with a foreign key relationship.
Replace the default dataset name if you want.
Click Finish.
In the Display Member drop-down box, select the column name (for example, ContactName) to be displayed in the combo box.
In the Value Member drop-down box, select the column (for example, CustomerID) to perform the lookup operation in the child table.
In the Selected Value drop-down box, navigate to Project Data Sources and the dataset you just created that contains the parent and child tables. Select the same property of the child table that is the Value Member of the parent table (for example, Orders.CustomerID). The appropriate BindingSource , data set, and table adapter components will be created and added to the form.
Bind the BindingNavigator control to the BindingSource of the child table (for example, OrdersBindingSource).
Bind the controls other than the ComboBox and BindingNavigator control to the details fields from the child table's BindingSource (for example, OrdersBindingSource) that you want to display.