how to turn off filter command

[hawke]

hello i was wonder if i could get some ideal of how to turn this function off

what i have is this

Public Sub FilterData(valueToSearch As String)
    Dim Connection As New SqlConnection("server=someip;user=someuser;password=somepassword;database=Report")
    Dim searchQuery As String = "SELECT * From Table1 WHERE CONCAT ([ID], Julian_Date, Serial_Number, [User])  like '%" & valueToSearch & "%'"
    Dim command As New SqlCommand(searchQuery, Connection)
    Dim adapter As New SqlDataAdapter(command)
    Dim Table As New DataTable

    Connection.Open()
    adapter.Fill(Table)
    Table1DataGridView.DataSource = Table
    Connection.Close()
End Sub

this is some code that i picked up on you-tube how ever in the video it does not tell you how to shut it off so that your datagridview will return to normal once you have done the search this is what i have tried.

Private Sub refreshdgv()
    Me.Update()

    TextBox3.Text = ""
    TextBox2.Text = ""
    TextBox4.Text = ""

    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
End Sub

i have also tried

Private Sub refreshdgv()
    Table1DataGridView = Table1DatagridView

    TextBox3.Text = ""
    TextBox2.Text = ""
    TextBox4.Text = ""

    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
End Sub

last but not least

Private Sub refreshdgv()
    Table1DataGridView.Update

    TextBox3.Text = ""
    TextBox2.Text = ""
    TextBox4.Text = ""

    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
End Sub

all 3 will reset the datagridview but none of them shut the search off so that you can add another entry without having to close the program

any help i would appreciate.

 

[jmcilhinney]

Firstly, it is important to understand that there are two ways to filter data: locally and remotely. Filtering locally means retrieving all the data upfront and then using a filter to hide some of it. The initially query will take longer but each subsequent filter will be quicker, plus you avoid potentially retrieving the same data multiple times. Filtering remotely means executing multiple queries as and when needed. The initial query will be quicker but subsequent filters will be slower and you may end up retrieving the same data repeatedly. Local filtering is generally preferable if the total amount of data is relatively small and you are likely to want to view all or most of it at some point. Remote filtering is generally preferable if the total amount of data is relatively large and you are likely to only want to view small parts of it at a time.

If, by turning filtering off, you mean that you want to display all the data then you should definitely be using local filtering. In that case, you should execute a single query with no WHERE clause upfront to populate a DataTable, bind that to a BindingSource and then bind that to the grid. When it comes time to filter, you simply set the Filter property of the BindingSource. To clear the filter, simply set that same property to Nothing or an empty String. For instance, if you have two TextBoxes to filter two columns:

Private Sub SetFilter()
    Dim criteria As New List(Of String)
    
    If TextBox1.TextLength > 0 Then
        criteria.Add($"Column1 LIKE '%{TextBox1.Text}%'")
    End If
    
    If TextBox2.TextLength > 0 Then
        criteria.Add($"Column2 LIKE '%{TextBox2.Text}%'")
    End If
    
    BindingSource1.Filter = String.Join(" AND ", criteria)
End Sub

That can be extended to as many criteria as you want.

 

[hawke]

what you just gave me will clear the search filter? or is the example the search filter and i would link that to my textbox so that i could search for the information that i want.....im sorry, i dont understand the example....

 

[jmcilhinney]

As the name of the method suggests, you call that method whenever you want to set the filter. You need to change the If statements appropriately to use your controls and filter by your columns. If all the filter fields are empty then the Filter property will be set to an empty String.