Filter Combobox binding source data from another

I have two comboboxes, both with binding sources attached which are pre-populated with data from a SQL server;

    private void SetLocationAreaBindingSource()     {         cboLocationArea.DisplayMember = "name";         cboLocationArea.ValueMember = "areaID";          DynamicParameters parameters = new DynamicParameters();         parameters.Add("@LocationType", 4);         locationAreaBindingSource.DataSource = db.Query<LocationArea>("LocationAreas_GetWhereType", parameters, commandType: CommandType.StoredProcedure);     }      private void SetLocationBayBindingSource()     {         cboLocationBay.DisplayMember = "name";         cboLocationBay.ValueMember = "bayID";          locationBayBindingSource.DataSource = db.Query<LocationBay>("SELECT * FROM [LocationBays] ORDER BY [name] ASC", commandType: CommandType.Text);     } 

When the user selects a value in the first combobox (cboLoacationArea) I need to filter the results down in cboLocationBay to only show the results related to that area;

    private void cboLocationArea_SelectedIndexChanged(object sender, EventArgs e)     {         locationBayBindingSource.Filter = "[locationAreaID] = " + cboLocationArea.SelectedValue;         cboLocationBay.Refresh();     } 

however the above code doesnt seem to work and just shows the full list. How can I achieve this?

Add Comment
2 Answer(s)

You must use IBindingListView interface to support filtering in this case DataView.

DataView:
Represents a databindable, customized view of a DataTable for sorting, filtering, searching, editing, and navigation. The DataView does not store data, but instead represents a connected view of its corresponding DataTable. Changes to the DataView’s data will affect the DataTable. Changes to the DataTable’s data will affect all DataViews associated with it.

I think your better approach will be to use Dapper and implement the IDataReader API the framework itself has:

private void SetLocationBayBindingSource() {     DataTable dt = new DataTable();     var reader = db.ExecuteReader<LocationBay>("SELECT * FROM [LocationBays] ORDER BY [name] ASC", commandType: CommandType.Text);     dt.Load(reader);     locationBayBindingSource.DataSource = dt.DefaultView;      cboLocationBay.DisplayMember = "name";     cboLocationBay.ValueMember = "bayID";     cboLocationBay.DataSource = locationBayBindingSource; } 

Later then you don’t need to refresh the ComboBox, the changes to the datatable will affect the dataview as the documentation says. (I will interpolate the string)

private void cboLocationArea_SelectedIndexChanged(object sender, EventArgs e) {     locationBayBindingSource.Filter = $"[locationAreaID] = {cboLocationArea.SelectedValue}"; } 

Answered on August 30, 2020.
Add Comment

You need to re-bind the datasource again:

private void cboLocationArea_SelectedIndexChanged(object sender, EventArgs e) {     locationBayBindingSource.Filter = "[locationAreaID] = " + cboLocationArea.SelectedValue;     cboLocationBay.Items.Clear();     cboLocationBay.DataSource = locationBayBindingSource.DataSource;     // cboLocationBay.Refresh();  // this may not be necessary } 
Answered on August 30, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.