VB.NET SQL Search from table

Question

I've created the table: "tblInterni" on my sql database and made so that I can see it on a datagridview. I am now making a search function so that if I search for a name it loads everyone with that name in the datagridview, but the query I made isn't working.

Private Sub Home_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim conn = New SqlConnection("Data Source=SRV-SQL;Initial Catalog=dbTest;User ID=pwdDb;Password=pwdDb")
    Dim adapter As New SqlDataAdapter("SELECT * FROM tblInterni", conn)
    Dim table As New DataTable()
    adapter.Fill(table)
    DataGridView1.DataSource = table

End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    Dim searchQuery As String = "SELECT * From tblInterni WHERE name like '%" & TextBox1.Text & "%'"
End Sub

form graphic

Why would it do anything? All you're doing is creating a String. You have to do something with that String if you want it to affect anything. — jmcilhinney
– jmcilhinney, Commented Nov 30, 2018 at 9:38

jmcilhinney · Accepted Answer · 2018-11-30 09:37:30Z

2

Given that you are retrieving all the data when the form loads, what you should be doing is binding your DataTable to the DataGridView via a BindingSource and then filtering that data by setting the Filter property of the BindingSource.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using adapter As New SqlDataAdapter("SELECT * FROM MyTable", "connection string here")
        Dim table As New DataTable

        adapter.Fill(table)

        BindingSource1.DataSource = table
        DataGridView1.DataSource = BindingSource1
    End Using
End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    BindingSource1.Filter = $"MyColumn LIKE '%{TextBox1.Text}%'"
End Sub

Note that the BindingSource would be added in the designer, just like the grid.

This is still not ideal though. If the user wants to type several characters in order to filter then this code will modify the filter several times unnecessarily and actually slow them down. A better idea is to use a Timer to add a small delay before filtering that resets each time they make a change. That way, if they type several characters quickly enough, the filter will only change after the last character.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using adapter As New SqlDataAdapter("SELECT * FROM MyTable", "connection string here")
        Dim table As New DataTable

        adapter.Fill(table)

        BindingSource1.DataSource = table
        DataGridView1.DataSource = BindingSource1
    End Using
End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    'Start/reset the filter timer.
    Timer1.Stop()
    Timer1.Start()
End Sub

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    BindingSource1.Filter = $"MyColumn LIKE '%{TextBox1.Text}%'"
End Sub

You can experiment a bit with the Interval of the Timer but you should find that something around 300 milliseconds should mean that filtering still feels fast enough but typing at a reasonable speed should avoid most unnecessary intermediate filters.

edited Nov 30, 2018 at 9:37

answered Nov 30, 2018 at 9:25

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mary Over a year ago

How about a Search button?

jmcilhinney Over a year ago

How about you don't introduce some new requirement that wasn't mentioned at all in your question?

Mary Over a year ago

Merry Christmas, John. Maybe because TextChanged is not a great place for code like this. Putting the code in a Search button is cleaner, I my never humble opinion. :-) As a user I would certainly prefer a button rather than a less than 1/3 second to decide if I put in enough text. It wasn't my question, BTW.

jmcilhinney Over a year ago

@Mary, ah, I didn't look at the name so didn't realise that you weren't the OP. The OP didn't ask for a Button so I didn't provide code for a Button. There are plenty of apps that provide live filtering like this so there's nothing wrong with it. The use of a Timer alleviates one potential issue. That said, filtering is filtering and my answer provides filtering code. That code can be moved to a different event handler if desired.

Mary Over a year ago

Yes, filtering code is saved to my code file. Thanks. Glad to know that the Timer is a common mechanism used to temper the TextChanged event.

Collectives™ on Stack Overflow

VB.NET SQL Search from table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related