Filter Combobox binding source data from another

Question

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?

Is the event wired up? If you debug it, does that code run? No reason to call `cboLocationBay.Refresh();` in the SelectedIndexChanged event for that control. — LarsTech, Aug 12 '20 at 17:26
Its all hooked up and the code is being ran, but the results are not filtering — Shadyjunior, Aug 12 '20 at 17:39
I don’t know if it helps, but the selected value will be of type in (areaID) and locationAreaID is also of type int. do I need to format the string and then cast/convert or does this not matter? — Shadyjunior, Aug 12 '20 at 18:43

Ivan-San · Answer 1 · 2020-08-12T23:59:11.680

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}";
}

I receive an error on ExecuteReader as db is set using; SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["ObsidianDB"].ConnectionString); — Shadyjunior, Aug 13 '20 at 13:09
You need to post how the connection to database is implemented — Ivan-San, Aug 13 '20 at 13:15
Yes sorry, I have the above set at the top of the form class and then the setting of the combo-boxes are done during form initialization. The database connection is then stored within my configuration manager as; — Shadyjunior, Aug 13 '20 at 13:19

score 0 · Answer 2 · edited Aug 13 '20 at 15:43

0

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
}

edited Aug 13 '20 at 15:43

Shadyjunior

437
3
13

answered Aug 12 '20 at 23:43

Jamal

398
4
9

It didn’t unfortunately, I am unsure if it is something to do with dapper. I ended up using the old way with data adapter and table and not using a binding source object and it is now working. I think I need to do some more reading into dapper, but I very much appreciate your assistance! – Shadyjunior Aug 14 '20 at 11:18

Filter Combobox binding source data from another

2 Answers2