• I removed the www from the URL, please update your bookmarks and pay attention to the bottom of the screen if using Google Chrome to allow push notifications again.

how to turn off filter command

hawke

New member
Joined
May 29, 2019
Messages
2
Programming Experience
Beginner
hello i was wonder if i could get some ideal of how to turn this function off

what i have is this
VB.NET:
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.
VB.NET:
Private Sub refreshdgv()
    Me.Update()

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

    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
End Sub
i have also tried
VB.NET:
Private Sub refreshdgv()
    Table1DataGridView = Table1DatagridView

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

    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
End Sub
last but not least
VB.NET:
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.
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,062
Location
Sydney, Australia
Programming Experience
10+
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:
VB.NET:
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

New member
Joined
May 29, 2019
Messages
2
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,062
Location
Sydney, Australia
Programming Experience
10+
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.
 
Top Bottom