search filter for datagridview-VBForums
Results 1 to 11 of 11

Thread: search filter for datagridview

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    search filter for datagridview

    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:

    Code:
    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.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,600

    Re: search filter for datagridview

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: search filter for datagridview

    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)?
    Last edited by echoUser; Oct 31st, 2012 at 02:24 AM.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,600

    Re: search filter for datagridview

    Quote Originally Posted by echoUser View Post
    So do I not need a separate function for filtering?
    No.
    Quote Originally Posted by echoUser View Post
    Are you saying that I can use BindingSource.Filter to filter my dataGridView in my fetchData sub?
    Yes.
    Quote Originally Posted by echoUser View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: search filter for datagridview

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

    Code:
    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

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: search filter for datagridview

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

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,600

    Re: search filter for datagridview

    You should get rid of this line:
    Code:
    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:
    Code:
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: search filter for datagridview

    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.

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,600

    Re: search filter for datagridview

    Quote Originally Posted by echoUser View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  10. #10

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: search filter for datagridview

    Here is my code:

    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]."

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,600

    Re: search filter for datagridview

    Please read what I posted. I did not ask for the code. I asked for the VALUE.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.