|Title||Bind a ComboBox to a database lookup table in VB .NET|
|Keywords||database, ADO.NET, VB .NET, bound controls, data binding, ComboBox, lookup table|
|Categories||Database, VB.NET, Software Engineering, Controls|
This example uses a database with two tables:
The value in Users.UserType is a key into the lookup table UserTypes. The constraint is Users.UserType = UserTypes.UserTypeId.
When the program is displaying data for a Users record at run time, the ComboBox should display the corresponding UserTypes.UserTypeName value.
First, some geneal instructions for creating database connections, data adapters, and DataSets.
- Database Connection:
- Open the form designer. On the Toolbox, click the Data tab. Double click the OldDbConnection tool.
- Select the new connection object. In the Properties window, click on the ConnectionString property. Click the dropdown to the right and select . Use the resulting Data Link Properties dialog to specify the data provider and database.
- Data Adapter:
- Open the form designer. On the Toolbox, click the Data tab. Double click the OldDbDataAdapter tool.
- Work through Data Adapter Configuration Wizard.
- Select the database connection you just created.
- Select Use SQL Statements.
- Enter a SQL statement to select the records you want to display (or use the Query Builder to build a SQL statement).
- Click Finish.
- Open the form designer. Open the Data menu and select Generate DataSet. Select the New option, enter a descriptive name for the generic DataSet type (e.g. DataSetUsers), select the table(s) you want to use, leave the "Add this dataset to the designer" box checked, and click OK.
- Initially the new DataSet is named something like DataSetUsers1. Change the name to something more consistent with your naming conventions such as dsUsers.
Now for the specific instructions.
- Build a database connection (connUsers) as usual.
- Build two data adapters, one for the Users table (daUsers) and one for the UserTypes table (daUserTypes).
- Make the DataSet (dsUserData), selecting all fields in both tables.
- Make a many-to-one relationship in the DataSet:
- Right-click the DataSet and select View Schema.
- Click the Schema button on the lower left.
- Drag the UserTypes.UserTypeId field onto the Users.UserType field. In the resulting dialog, fill in the Users.UserType field.
- Close the schema, accepting the changes.
- Set the ComboBox's properties.
- Set DataSource = the lookup table: dsUserData.UserTypes.
- Set DisplayMember = the field to display in the lookup table: UserTypeName.
- Set ValueMember = the field that is the actual value in the lookup table: UserTypeId.
- Bind the SelectedValue property:
- Open the ComboBox's DataBindings property (at the top in the Properties window).
- Select the SelectedValue property. Click the dropdown arrow on the right.
- Select the field in the main Users record that you want the ComboBox to represent: dsUserData.Users.UserType.
- Bind other controls if desired.
At run time, you still need to load and save the database data. In the form's Load event handler, call the data adapters' Fill methods to fill the DataSet. In this case, the program must load the UserTypes data first so it will be available when the Users table is loaded (the Users.UserType value must match a UserTypes.UserTypeId value).