Results 1 to 6 of 6

Thread: [RESOLVED] BindingSource.Filter String

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Resolved [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.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: BindingSource.Filter String

    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

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Re: BindingSource.Filter String

    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.
    Last edited by arcanine; Jul 16th, 2011 at 08:54 PM. Reason: Grammar/Thought Coherence

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: BindingSource.Filter String

    Quote Originally Posted by arcanine View Post
    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")
    Attached Files Attached Files

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Re: BindingSource.Filter String

    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.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: [RESOLVED] BindingSource.Filter String

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width