search filter for datagridview

[echoUser]

Hello,

I have a datagridview and a search textbox that filters the dgv on button click with whatever the user inputs in that textbox.
The problem is, when the user tries to update a row AFTER a search, the datarow grabbed is NOT the current one but the one that was selected BEFORE the search.
I think this is because I am creating 2 datatables (one for form_load and another for the search filter), and the user hits the update button, the row grabbed is from the form_load datatable.

Please take a look at my (simplified) code below:

Public Class VBApp

Private Sub fetchData(ByVal tableName As String)

            Dim dt As New DataTable
            adapter = New MySqlDataAdapter

            command.Connection = conn
            command.CommandText = "select * from TableName"

            adapter.SelectCommand = command
            reader = command.ExecuteReader

            dt.Load(reader)
            DataGridView1.DataSource = dt

        reader.Close()
        conn.Close()

    End Sub

    Private Sub updateData()

        Dim row As DataRow = DirectCast(BindingSource.Current, DataRowView).Row

        Using dialogue As New updatePopup(row)
            Dim dr As DialogResult = dialogue.ShowDialog()
        End Using

        'etc. etc. (skipping code)

    Private Sub searchData()

        Dim search As String = KeywordTextbox.Text
        Dim tableName As String = TableList.Text
        Dim dt As New DataTable

        If (conn.State = ConnectionState.Closed) Then
            setConnection()
        End If

        command.Connection = conn
        command.CommandText = ""

            command.CommandText = "select * from persons where `columnA` like '%" & search "%'"

        reader = command.ExecuteReader

        dtable.Load(reader)
        DataGridView1.DataSource = dt

        reader.Close()
        conn.Close()

    End Sub

End Class

Thanks for your help.

[jmcilhinney]

This is actually very easy. You just query the database once. Get all the data into a DataTable, bind that to a BindingSource and bind that to your control(s). When you want to filter the data you simply set the Filter property of the BindingSource. The data in the DataTable is unaffected but the BindingSource only exposes rows that match the Filter, so that's all the user sees. Change the filter as much as you like and the view will refresh almost instantly as there is no database query.

The value you assign to the Filter property is basically a SQL WHERE clause, although only a subset of SQL syntax is supported. If you need help then the documentation for the DataColumn.Expression property explains all the supported syntax.

Also, if you want to access the data in code, you should do that via the BindingSource too.

[echoUser]

So do I not need a separate function for filtering? Are you saying that I can use BindingSource.Filter to filter my dataGridView in my fetchData sub?
Also, how can bind the bindingSource to my control(s)?

[jmcilhinney]

So do I not need a separate function for filtering?

No.

Are you saying that I can use BindingSource.Filter to filter my dataGridView in my fetchData sub?

Yes.

Also, how can bind the bindingSource to my control(s)?

You bind the DataTable to the BindingSource the same way you bound it to the DataGridView. You bind the DataGridView to the BindingSource the same way you bound it to the DataTable.

[echoUser]

I am getting errors with my bindingsource.filter syntax and I cannot figure out why. Could you please take a look?

Private Sub fetchData(ByVal tableName As String)

        Try
            command = New MySqlCommand
            adapter = New MySqlDataAdapter
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            dt = New DataTable

            If (conn.State = ConnectionState.Closed) Then
                setConnection()
            End If

            command.Connection = conn
            command.CommandText = "select * from `" & tableName & "`"

        Catch ex As MySqlException
            MessageBox.Show("Error1: " & ex.Message)
        End Try

        adapter.SelectCommand = command
        reader = command.ExecuteReader

        dt.Load(reader)

        DataGridView1.DataSource = dt
        BindingSource.DataSource = dt
        DataGridView1.DataSource = BindingSource

        Dim search As String = KeywordTextbox.Text

        BindingSource.Filter = "[Book ID] = " & search & " or ISBN like '%" & search & "%' or Title like '%" & search & "%'"

        reader.Close()
        conn.Close()

    End Sub

[echoUser]

Oh nvm, I got it!!!!!! Thank you so much for your help!

[jmcilhinney]

You should get rid of this line:

DataGridView1.DataSource = dt
BindingSource.DataSource = dt
DataGridView1.DataSource = BindingSource

The next two lines do the binding now.

Also, I strongly suggest using String.Format. Long strings with multiple & operators get confusing very quickly. They are hard to read and therefore error-prone. I find something like this much clearer:

BindingSource.Filter = String.Format("[Book ID] = {0} or ISBN like '%{0}%' or Title like '%{0}%'", search)

You've got a problem with that code too. I'm not sure what the error was that you fixed because you didn't say. If you solve an issue yourself you should always provide your solution. It might help others and we can tell you how to improve it if it's not optimal. This may have been it but that filter will be fine when you enter a number but as soon as you enter a non-numeric value it will cause a syntax error. You should check whether the input is a number first and only include the [Book ID] column in the filter if it is.

[echoUser]

It was a syntax error, like you said. I got rid of the line DataGridView1.DataSource = dt, thank you for the advice!
After I changed my code to String.Format, I started getting errors, saying the application cannot find the "column" of my search...

What I tried though, was removing the int column (Book ID) from the bildingSource.Filter query, and it worked fine.
Could you suggest how I can fix this? Thank you.

[jmcilhinney]

Could you suggest how I can fix this?

I thought that I already had. Maybe if I knew what the actual value was when the syntax error occurred I could be more specific. You're not the only one but far too many people expect us to just know stuff about their project without actually giving us the information.

[echoUser]

Here is my code:

Private Sub searchData()

        Dim search As String = KeywordTextbox.Text
        Dim tableName As String = TableList.Text

        BindingSource.Filter = String.Format("ISBN like '%{0}%' or Title like '%{0}%' or Author like '%{0}%' or Publisher like '%{0}%'", search)

    End Sub

This works. But as soon as I insert the [Book ID] = {0} or into the query, I get this error: "Evaluate Exception was unhandled. Cannot find column [whatever I typed into the search box]."

[jmcilhinney]

Please read what I posted. I did not ask for the code. I asked for the VALUE.