locked
Filtering the filtered bindingsource results RRS feed

  • Question

  • Hi Guys!   
    Code:
    Private Sub ToolStripTextBox1_TextChanged(sender As Object, e As EventArgs) Handles ToolStripTextBox1.TextChanged
            Dim str As String() = ToolStripTextBox1.Text.Split(" ")
            For Each value As String In str
                Me.Contact_InfoBindingSource.Filter = String.Format("Name LIKE '%" & value & "%' OR Surname LIKE'%" & value & "%' OR Address LIKE'%" & value & "%'")
            Next
        End Sub
    
    This fills a datagrid.

    Now, on str(1), it resets the results found by str(0).
    I want to filter str(1) within the filtered results previouslyby str(0)

    Thanks a lot!
    • Edited by Flashbond Friday, August 9, 2013 9:46 PM
    Friday, August 9, 2013 9:45 PM

Answers

  • Dim filterValues = ToolStripTextBox1.Text.Split()
        Dim criteria As New List(Of String)
         
        For Each filterValue In filterValues
            criteria.Add(String.Format("(Name LIKE '%{0}%' OR Surname LIKE '%{0}%' OR Address LIKE '%{0}%')", filterValue))
        Next
         
        Contact_InfoBindingSource.Filter = String.Join(" AND ", criteria)

    SOLVED!
    • Marked as answer by Flashbond Sunday, August 11, 2013 3:54 AM
    Sunday, August 11, 2013 3:54 AM

All replies

  • Hi,

    you will have to combine the filter expressions using OR or AND:

            Dim str As String() = {"henry", "smith's"} ' ToolStripTextBox1.Text.Split(" ")
            Dim filter As New System.Text.StringBuilder()
            For Each value As String In str
                If filter.Length > 0 Then
                    filter.Append(" OR ")
                End If
                If Not String.IsNullOrWhiteSpace(value) Then
                    value = "'%" & value.Replace("'", "''") & "%'"
                    filter.AppendFormat("(Name LIKE {0} OR Surname LIKE {0} OR Address LIKE {0})", value)
                End If
            Next
            'Console.WriteLine(filter.ToString())
            Me.Contact_InfoBindingSource.Filter = filter.ToString()
    

    Regards, Elmar

    Saturday, August 10, 2013 7:49 AM
  • First of all thanks a lot for your reply. It throws an error on this ine:

    Me.Contact_InfoBindingSource.Filter = filter.ToString()

    Syntax error: Missing operand after 'Or' operator.

    It occurs while typing the first letter of the second word.

    • Edited by Flashbond Saturday, August 10, 2013 2:05 PM
    Saturday, August 10, 2013 2:02 PM
  • Do a Console.WriteLine(filter.ToString) which writes to the IDE Output window. Inspect the string for incorrect syntax. If you don't find a syntax error then post the filter string here for inspection.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, August 10, 2013 2:10 PM
  • Thanks for advice. By your advice, I discovered that the error does not occur with the first letter of the second word. It appears with the first "space".

    With the "space", it adds one more OR to the end without any statement after.


    BTW, I don't like to use "Len >0". How can I use ToolStripTextBox1.Text.Trim.Split(" ") in this code?
    • Edited by Flashbond Saturday, August 10, 2013 2:23 PM
    Saturday, August 10, 2013 2:20 PM
  • Hi,

    that can be resolved if you move the If value condition:

        If Not String.IsNullOrWhiteSpace(value) Then
            If filter.Length > 0 Then
                filter.Append(" OR ")
            End If
            value = "'%" & value.Replace("'", "''") & "%'"
            filter.AppendFormat("(Name LIKE {0} OR Surname LIKE {0} OR Address LIKE {0})", value)
        End If
    

    The are some other special cases that may be cause errors - for example "*" or "%" inside a string, see DataColumn.Expression - Wildcard Characters

    Regards, Elmar

    Saturday, August 10, 2013 3:02 PM
  • Ok, now it is working but one last problem..
    The problem is like that;
    Think that the first record is John Smith and the second record is James Brown.
    If you write "John Brown", my filter returns James Brown's row because the last array item has been filtered was "Brown".
    In the same scenario, your code returns both rows.

    Actually there shouldn't be any result with "John Brown" filter.
    Saturday, August 10, 2013 3:15 PM
  • Hi,

    thats more question what you want to achieve with your filter.

    Be aware that a LIKE %...% will match to many rows very often and can give unexpected results.

    You can replace OR by AND to combine the expressions. Then only a row will match if a "John" and a "Brown" are found in one of the evaluated fields.

    Regards, Elmar

    Saturday, August 10, 2013 4:28 PM
  • Ok, I want something like a google search with any part of the string( like "ohn mith").

    Think that you put + between words on google. This is what I want.

    Using AND is like wrapping the word with quotes " " on google. I don't want this.

    Is this possible?

    EDIT: Remember what I want in the beginning. This is exactly what I need. On the second string attempt, filtering the filtered results previously with the first string. Sorry for my English :)

    • Edited by Flashbond Saturday, August 10, 2013 4:47 PM
    Saturday, August 10, 2013 4:41 PM
  • Hi,

    No, Google / Bings search capabilities are far beyond the (simple) expressions supported by DataSet expressions. A BindingSource.Filter is the same as the DataView.RowFilter.

    There is no way to add a filter to a filtered resultset, as the expressions are always evaluated as a whole. The nearest equivalent is an AND expression. 

    More of a "Google feeling" can be achieved using SQL Server full text search, but that requires a new query each time - see CONTAINS.

    Another way is Lucene.NET, but thats needs additional preparations for the data.

    Regards, Elmar

    • Proposed as answer by Reed KimbleMVP Saturday, August 10, 2013 6:38 PM
    Saturday, August 10, 2013 5:39 PM
  • -EDIT-

    Scratch this; it doesn't work as I initially thought so now I don't think it will actually do what you want.

    You may be able to use temporary tables to try the same idea; load the result of each filter into its own table/view which can then be filtered again.  Performance could still be an issue though.

    -/EDIT-

    Another option might be to chain binding sources and add each individual filter to the next binding source in the chain.  This should work ok for a small set of data but I'm not sure how well it would scale.

    Public Class Form1
        Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged
            If TextBox1.TextLength > 0 Then
                Dim words() As String = TextBox1.Text.Split(" "c)
                Dim currentBS As BindingSource = BindingSource1
                For Each word As String In words
                    If word.Length > 0 Then
                        currentBS.Filter = String.Format("Name LIKE '%" & word & "%' OR Surname LIKE'%" & word & "%' OR Address LIKE'%" & word & "%'")
                        Dim tmpBS As New BindingSource(currentBS, Nothing)
                        currentBS = tmpBS
                    End If
                Next
                DataGridView1.DataSource = currentBS
            Else
                BindingSource1.RemoveFilter()
                DataGridView1.DataSource = BindingSource1
            End If
        End Sub
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            With DataSet11.Contact
                .AddContactRow("Fred", "Flintstone", "123 Rubble St")
                .AddContactRow("Wilma", "Flintstone", "123 Rubble St")
                .AddContactRow("Barney", "Rubble", "125 Rubble St")
                .AddContactRow("Betty", "Rubble", "125 Rubble St")
            End With
        End Sub
    End Class
    This example assumes you begin with a single BindingSource1 on the form which is bound to the source data table. The DataGrid1 is initially bound to BindingSource1 when the form loads.  The datasource for DataGrid1 then changes according to the user input.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"



    Saturday, August 10, 2013 6:51 PM
  • Yeah, in fact I was working on something similar:

    Private Sub ToolStripTextBox1_TextChanged(sender As Object, e As EventArgs) Handles ToolStripTextBox1.TextChanged
            Dim str1 As String() = ToolStripTextBox1.Text.Trim.Split(" ")
            Dim searchBindingSource(str1.Length - 1) As BindingSource
            For n = 0 To str1.Length - 1
                If n = 0 Then
                    Me.Contact_InfoBindingSource.Filter = String.Format("Name LIKE '%" & str1(n) & "%' OR Surname LIKE'%" & str1(n) & "%' OR Address LIKE'%" & str1(n) & "%'")
                    searchBindingSource(n) = Me.Contact_InfoBindingSource
                Else
                    searchBindingSource(n - 1).Filter = String.Format("Name LIKE '%" & str1(n) & "%' OR Surname LIKE'%" & str1(n) & "%' OR Address LIKE'%" & str1(n) & "%'")
                    searchBindingSource(n) = searchBindingSource(n - 1)
                End If
            Next
            Me.Contact_InfoDataGridView.DataSource = searchBindingSource(str1.Length - 1)
    End Sub

    I couldn't manage it yet. But your direct casting method is worth to try :)




    • Edited by Flashbond Saturday, August 10, 2013 9:59 PM
    Saturday, August 10, 2013 8:26 PM
  • Dim filterValues = ToolStripTextBox1.Text.Split()
        Dim criteria As New List(Of String)
         
        For Each filterValue In filterValues
            criteria.Add(String.Format("(Name LIKE '%{0}%' OR Surname LIKE '%{0}%' OR Address LIKE '%{0}%')", filterValue))
        Next
         
        Contact_InfoBindingSource.Filter = String.Join(" AND ", criteria)

    SOLVED!
    • Marked as answer by Flashbond Sunday, August 11, 2013 3:54 AM
    Sunday, August 11, 2013 3:54 AM