Introduction
The following simple WinForms tutorial reveals the fastest way to create a foreign key combobox filter for a DataGrid in Visual Studio 2005. We will be using the Access NorthWind database as our data source. If you do not have the NorthWind database, you can download it here.
Tutorial
1. Add a DataSource
Open your Form in Design View. Click on Add New Datasource in the Data Sources window.
Choose Database as the Datasource Type. Select Microsoft Access Database File (OLE DB) as the Data Source. Browse to the location of the Northwind database. Usually: C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
Click Next till you get to Choose Your Database Objects. Select CustomerID and ContactName from the Customers table, and select the entire Orders table as below:
Click Finish.
Note: A Foreign Key Relationship is automatically added to the dataset since the relation was defined in the database. If you are using a non-relational database, you will need to add this relation to the dataset manually.
2. Create the Combobox
Drag a ComboBox from the Toolbox onto the form.
Select the combobox and go to the Properties window. Set the Datasource to the Customers table.
Set the DisplayMember to ContactName. This will determine the combobox's text.
Set the ValueMember to CustomerID. This will determine the combobox's selected value.
3. Create the Grid
Drag the Orders table from the Datasources window onto the form. This will create a DataGrid bound to the Orders table and will add an Orders BindingSource to the form.
4. Code the ValueChanged Handler
Select the Combobox. Click the Event Icon (lightning bolt) in the Properties window. Double click on the SelectedValueChanged event to create and go to the SelectedValueChanged handler in the code.
Set CustomersBindingSource.Position to the SelectedIndex as below:
VB:
Private Sub ComboBox1_SelectedValueChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
Me.CustomersBindingSource.Position = Me.ComboBox1.SelectedIndex
End Sub
C#:
private void ComboBox1_SelectedValueChanged(object sender, System.EventArgs e)
{
this.CustomersBindingSource.Position = this.ComboBox1.SelectedIndex;
}
Conclusion
We now have an order history filtered by customer in a matter of minutes. This method works with any relational datasource.
Please comment on anything that needs further clarification, or with any questions on implementing this. Also, please provide suggestions before rating the article less than a 5.
