Question how do I filter a bindingsource filled by a table adapter

[keb1965]

I have several tables with data that needs to be editable and it also needs to be filtered according to a distinct values in one of the fields.

As it stands I simply imported the data using a tableadapter like this:

Me.HouseholdsTableAdapter.Fill(Me.MyDataSet.Households)

This dataset is used to bind many controls in the application and to make matters more complicated, I also have three other tables that are related.

The three tables I have that are related are one to many. They are 'households', 'vehicles', 'persons' and "trips"

Each household will have multiple vehicles and persons.
Each person will have multiple trips.

In the record, there is a field that is used to track the person's ID who is entering or editing the data. These values are gathered by the following:

        Dim colnames() As String = {"INTERVIEWER"}
        Dim tdistinctRows As DataTable = Me.MyDataSet.Households
        tdistinctRows = tdistinctRows.DefaultView.ToTable(True, colnames)
        INTERVIEWERComboBox.DataSource = tdistinctRows
        INTERVIEWERComboBox.DisplayMember = "INTERVIEWER"

Now, whenever I start the application the interviewer combo is filled only with distinct values, however, I need to filter the records that are in MyDataSet.Households based on the value in INTERVIEWERCombobox. If I try to create a dataview and filter it, then I lose the ability to navigate the records using the combobox showing the IDs for the records.

The same solution for this will also apply to the household - vehicle relationship, household - persons relationship and persons - trip relationship.

Each household is given a primary key that is used to identify the household uniquely and each vehicle, person and trip is assigned a number from 1 - ~ and matches up with the related table on the HH_ID field in both tables.

I need a way to keep the data intact while filtering only those records in a cascading fashion where the HH_ID, P_ID, V_ID, or T_ID only show for the records that are related.

I am using VB, but I can also utilize C# if needed.

Thanks!

 

[jmcilhinney]

It's very simple, e.g.

myBindingSource.Filter = String.Format("SomeColumn = {0}", someValue)

In your case, the value would be the SelectedValue from the ComboBox. As for binding and filtering related data, take a look at this:

Master/Detail (Parent/Child) Data-binding

 

[keb1965]

I briefly looked over the code on the link, but I have to say, it doesn't seem to be the same scenario. I understand filtering the data if I have it in a dataview, but it isn't. MyDataSet.Households is a DataTable and there is no filter mechanism, or at least none I can find, and a datatable won't cast to a dataview.

 

[jmcilhinney]

You apparently haven't read the documentation for either the DataView or the DataTable class. Every DataTable is implicitly associated with a DataView via its DefaultView property. In fact, when you bind a DataTable, it's actually the contents of its DefaultView that you see. That is how you're able to sort a DataGridView bound to a DataTable.

That's pretty much irrelevant though, if you're using a BindingSource. You set the Sort and Filter properties of the BindingSource to sort and filter the bound data, where you would set the Sort and RowFilter properties of the DefaultView without one.

 

[keb1965]

Ok, I understand now, in my haste trying to cast to a DataView I completely missed the filter property on the underlying BindingSource. I should have read your previous comment a little more closely.

Thanks a bunch!