[RESOLVED] BindingSource.Filter String

I have a database with a table that is a child to many other tables. I am using this child table as a datasource for a datagridview. I am using comboboxes to display specific fields in any parent tables, instead of the user seeing the foreign key. Everything is fine.

However, I would like to filter the BindingSource based on text matches between any of the fields in the child table and the corresponding fields of the parent tables.

I have spent the last 6 or 8 hours scouring the internet for answers and trying various ideas, but to no avail! I'm extremely frustrated.

For example, according to MS (see here), the following code should filter my datatable when text is typed into a textbox named txtRefFireDFilter:

Code:

---

    Private Sub txtRefFireDFilter_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRefFireDFilter.TextChanged
        Dim tb As TextBox = CType(sender, TextBox)

        If tb.TextLength = 0 Then
            bsRefFireD.RemoveFilter()
        Else
            bsRefFireD.Filter = String.Format("Parent(RefFireD2Manf).Name LIKE '*{0}*'", tb.Text)
        End If
    End Sub

---

Now this assumes that there is a parent-child reference named "RefFireD2Manf". I am certain of this reference, as I see it both in the design-time screen and in the .designer code. Unfortunately, the following error is thrown:

Code:

---

Cannot find the parent relation 'RefFireD2Manf'.

---

Also, placing the reference name in single quotes and/or square brackets will only break the parser:

Code:

---

Syntax error in Lookup expression:
Expecting keyword 'Parent' followed by a single column argument with possible relation qualifier:
Parent[(<relation_name>)].<column_name>.

---

As far as I can tell, this is a bug in VS. The linked article mentions a work-around, by I'm too taxed right now to try and implement it, as a first pass read was over my head.

Look at the project in the link below which uses a MS-Access backend. The relationship is set between customers and orders where customers is the parent.

Sounds like in your case vs what I am showing your child is the master so the logic here may need to be reverse but it shows either way how to work to do your filtering without actually filtering in this case.

The following show how to point to a column in the child relation

Code:

---

Dim FreightExpression As String = "Sum(Child(CustomersOrders).Freight) "
dsCustomersOrders.Tables("Customers").Columns.Add(New DataColumn With _
    { _
        .ColumnName = "Freight", _
        .DataType = GetType(System.String), _
        .Expression = FreightExpression _
    } _
)

---

Working the other way (see project) we could do something like this

Code:

---

    Private tb As New TextBox With {.Text = "Whatever"}
    Private Sub cmdShowParentInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdShowParentInfo.Click
        Dim ColunName As String = dsCustomersOrders.Relations(0).ParentTable.Columns(0).ColumnName
        Console.WriteLine("Relationship column: {0} Current value: {1}", ColunName, bsDetails.CurrentRow(ColunName))

        Console.WriteLine("Parent table values")
        For Each col As DataColumn In bsMaster.CustomerTable.Columns
            Dim MyFilter As String = String.Format("{0} LIKE '*{1}*'", col.ColumnName, tb.Text)
            Console.WriteLine("    {0} = {1} | {2}", col.ColumnName, bsMaster.CurrentRow(col.ColumnName), MyFilter)
        Next
    End Sub

---

Which demos how you might construct a filter against your data.




http://kevininstructor.home.comcast....ilRelation.zip

I don't think I'm understanding your response. I don't see how that will filter the rows in a bindingsource, so that my datagridview will only show the rows in the filter result set.

Quote:

---

Originally Posted by arcanine

I don't think I'm not understanding your response. I don't see how that will filter the rows in a bindingsource, so that my datagridview will only show the rows in the filter result set.

---

The code does not filter but gives you the "how" to put pieces together to filter.

The following code from the project

Code:

---

    Private Sub cmdShowParentInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdShowParentInfo.Click
        Dim ColunName As String = dsCustomersOrders.Relations(0).ParentTable.Columns(0).ColumnName
        Console.WriteLine("Relationship column: {0} Current value: {1}", ColunName, bsDetails.CurrentRow(ColunName))

        Console.WriteLine("Parent table values")
        For Each col As DataColumn In bsMaster.CustomerTable.Columns
            Dim MyFilter As String = String.Format("{0} LIKE '*{1}*'", col.ColumnName, tb.Text)
            Console.WriteLine("    {0} = {1} | {2}", col.ColumnName, bsMaster.CurrentRow(col.ColumnName), MyFilter)
        Next
    End Sub

---

Produces the following results where the code in red+bold show that I could (if my code was set-up for what you wanted) filter on the parent BindingSource DataSource.DataSet, first table.

Code:

---

Relationship column: CustomerID Current value: BSBEV
Parent table values
    CustomerID = BSBEV | CustomerID LIKE '*Whatever*'
    CompanyName = B's Beverages | CompanyName LIKE '*Whatever*'
    Address = Fauntleroy Circus | Address LIKE '*Whatever*'
    City = London | City LIKE '*Whatever*'
    PostalCode = EC2 5NT | PostalCode LIKE '*Whatever*'
    Phone = (171) 555-1212 | Phone LIKE '*Whatever*'
    Freight = 281.31 | Freight LIKE '*Whatever*'

---

While this shows how to use Child, no different from Parent

Code:

---

' http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
Dim FreightExpression As String = "Sum(Child(CustomersOrders).Freight) "
dsCustomersOrders.Tables("Customers").Columns.Add(New DataColumn With _
    { _
        .ColumnName = "Freight", _
        .DataType = GetType(System.String), _
        .Expression = FreightExpression _
    } _
)

---

Therefore, the pieces are there you simply need to understand what I am doing by reviewing the code and then apply it to your situation.


Now we could go a different route altogether as shown in the attached demo. Now before you say it does not fit your exact issue I will say that there are times when one needs to choose a different direction than planned as this is doing but clearly shows you can traverse the child rows and display parent column data and also traverse parent data to see child data.


You will need to wrap you head around the following, specifically the DataBinding shown below where the property to bind to is the primary key of the parent BindingSource where the source for the data binding is the child/detail Bindingsource which allows you to traverse child rows to select the child's parent row data in the ComboBox and TextBoxes.

Code:

---

        Dim SampleData As New DataLoader

        bsParent.DataSource = SampleData.ParentTable
        bsChild.DataSource = SampleData.ChildTable

        cboLastName.DisplayMember = "LastName"
        cboLastName.ValueMember = "Identifier"
        cboLastName.DataSource = bsParent
        cboLastName.DataBindings.Add("SelectedValue", bsChild, "ParentIDColumn")

---

Okay, somewhere (sorry, I've read so many posts about this that I don't remember the link), I read that you must create (and possibly rename) all relations in the DataSet Designer before adding any of the DataTables to your form. Apparently any relational changes made in the DataSet Designer are not reflected on forms that use those DataSets.

I don't know if I would classify that as a bug or not, because keeping the integrity of your forms when the dataset is changed might be ideal or necessary. I'm not experienced enough to know one way or the other.

Regardless, I removed all of the BindingSources, TableAdapters, and DataSets from my form, updated all of the DataSets in the Designer, and then added them back to my form. Everything now works. It's a pain, but not being able to navigate the relationships in the DataSet was a much bigger pain.

I stumbled upon this, because I was creating a sample project to post, hoping it would better explain what I was encountering. However, when I created it, I built the database first, and then built the forms. Because I unknowingly did things in the correct order, everything worked.

This reminded me of the tidbit I had read about the order of building your project. So, in my sample project (currently working), I tried to add another table to the DataSet that was a parent to an existing table. The new relationship did not work, and was throwing the same error as my project.

Well, in a round-about way, you solved my problem for me. Thanks!

And just to answer my original post, filtering on fields from a parent table using a foreign key relationship is done as follows:

Code:

---

BindingSource.Filter="Parent([NameOfRelationshipBetweenTables]).[ParentTableFieldName] LIKE '*[FilterText]*'"

---

Where the values of the text inside the square brackets (bold and red) are the actually names of the objects in your code.

A round about way is right :-) one last thing, the more experience developers get the more they move away from the IDE generating code for them and move to hand coding which gives you pretty much full control over how your app executes and less chance for bugs. Anyway sounds like you are doing okay now.