Results 1 to 16 of 16

Thread: Filtering DataGridView from textboxes

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Filtering DataGridView from textboxes

    Hello all.

    I have a problem with filtering a DataGridView.

    I have a database in MySQL.
    Also i have a form that has a DataGridView1 that is showing all the columns from one table from database. That table have the first column with ID Primary Key.
    In that form I have 6 text boxes that I use to filter the DataGridView1.
    When I enter a word in one of the text boxes and the I press the Search button, everything works great, the DataGridView1 was filtered ok less then the first column (ID).
    Also, when I double-click one cell from DataGridView1 V.S. shows me what data I have entered in the database.
    The problem I have is that I don't know how to filter also the first column ID of the DataGridView1.
    This is the code I use:

    Code:
    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
    
            Dim DV As New DataView(dt_interv)
            DV.RowFilter = String.Format("line like '%{0}%' AND station like '%{1}%' AND error like '%{2}%' AND by like '%{3}%' AND problem like '%{4}%' AND data like '%{5}%'", Me.txtLine.Text, Me.txtsearchStation.Text, Me.txtError.Text, Me.txtBy.Text, Me.txtProblem.Text, Me.txtData.Text)
            DataGridView1.DataSource = DV
    
        End Sub

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    Firstly, don't keep creating a new DataView each time. A DataTable already has an associated DataView and, when you bind the DataTable, that's where the data comes from. What you should do is add a BindingSource to your form in the designer and then, when you populated the DataTable, bind it to the BindingSource and bind the BindingSource to the grid. Each time you want to change the filter, you simply set the Filter property of the BindingSource.

    As for filtering by ID, it's pretty much the same as what you have now, i.e. you specify the column to filter by, the operator to compare with and the value to compare to. Obviously you can't use LIKE because that's just for text. You'll probably use = but any numerical comparison operator is valid. If you're not using LIKE then obviously wildcards aren't going to be used and also numerical values are not wrapped in single quotes the way text values are. Other than that, it's the same.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    Quote Originally Posted by jmcilhinney View Post
    Firstly, don't keep creating a new DataView each time. A DataTable already has an associated DataView and, when you bind the DataTable, that's where the data comes from. What you should do is add a BindingSource to your form in the designer and then, when you populated the DataTable, bind it to the BindingSource and bind the BindingSource to the grid. Each time you want to change the filter, you simply set the Filter property of the BindingSource.

    As for filtering by ID, it's pretty much the same as what you have now, i.e. you specify the column to filter by, the operator to compare with and the value to compare to. Obviously you can't use LIKE because that's just for text. You'll probably use = but any numerical comparison operator is valid. If you're not using LIKE then obviously wildcards aren't going to be used and also numerical values are not wrapped in single quotes the way text values are. Other than that, it's the same.
    Forgive me jmcilhinney but I can not figure out

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    Quote Originally Posted by seek4code View Post
    Forgive me jmcilhinney but I can not figure out
    What exactly can't you figure out? Replace the LIKE operator, remove the wildcards and remove the single quotes. I'm not sure which is the hard part of that.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    The problem is that I don't have a text box set for ID only for other columns but I whant to filter the ID column also.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    Quote Originally Posted by seek4code View Post
    The problem is that I don't have a text box set for ID only for other columns but I whant to filter the ID column also.
    The solution to that seems fairly obvious, doesn't it? Add a TextBox for ID. If you want to filter by ID then obviously you need an ID value to filter by. It's not for us to tell you how that value is to be obtained. It's for you to decide. Do you want the user to enter it into a TextBox? Then add a TextBox. If you want to do some other way then do that. It's your choice. If you don;t know how to do what you want then we can help you with the HOW but we need to know the WHAT first.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    Code:
    DV.RowFilter = String.Format("ID = 0, line like '%{1}%' AND station like '%{2}%' AND error like '%{3}%' AND by like '%{4}%' AND problem like '%{5}%' AND data like '%{6}%'", Me.txtId.Text, Me.txtLine.Text, Me.txtsearchStation.Text, Me.txtError.Text, Me.txtBy.Text, Me.txtProblem.Text, Me.txtData.Text)
    I created a textbox ID and put also in the code but it gives an error "Syntax error in the expression."

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    Quote Originally Posted by seek4code View Post
    Code:
    DV.RowFilter = String.Format("ID = 0, line like '%{1}%' AND station like '%{2}%' AND error like '%{3}%' AND by like '%{4}%' AND problem like '%{5}%' AND data like '%{6}%'", Me.txtId.Text, Me.txtLine.Text, Me.txtsearchStation.Text, Me.txtError.Text, Me.txtBy.Text, Me.txtProblem.Text, Me.txtData.Text)
    I created a textbox ID and put also in the code but it gives an error "Syntax error in the expression."
    If every other pair of criteria is separated by an AND operator, why should the new one be any different?

    I said to remove the single quotes and the wildcards but you have removed more than that.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    Code:
    DV.RowFilter = String.Format("ID = {0} AND line like '%{1}%' AND station like '%{2}%' AND error like '%{3}%' AND by like '%{4}%' AND problem like '%{5}%' AND data like '%{6}%'", Me.txtId.Text, Me.txtLine.Text, Me.txtsearchStation.Text, Me.txtError.Text, Me.txtBy.Text, Me.txtProblem.Text, Me.txtData.Text)
    Now I'm having this error: Syntax error: Missing operand before 'And' operator.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    I realized that the problem I have is not from the filter is from double-click DataGridView1.
    The filter is working as you said. My DataGridView1 it's loading one tabel from the MySQL database by default when I open de Form. The columns are:

    ID line station error by problem data
    1..............f
    2..............a
    3..............y
    4..............x
    5..............k
    6..............w
    7..............u


    Let us presume that on the column ID we have ID 1, 2, 3, 4, 5, 6, 7 .....
    My problem is that when I'm filtering the DataGrid by station x and that station x has ID 4, after I double-clicking that line to see the entry, it's showing me allways ID 1, 2, 3, 4 and so on ...
    I don't know if I made ​​it clear.
    Last edited by seek4code; Sep 4th, 2014 at 08:07 PM. Reason: more details

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    Quote Originally Posted by seek4code View Post
    I realized that the problem I have is not from the filter is from double-click DataGridView1.
    The filter is working as you said. My DataGridView1 it's loading one tabel from the MySQL database by default when I open de Form. The columns are:

    ID line station error by problem data
    1..............f
    2..............a
    3..............y
    4..............x
    5..............k
    6..............w
    7..............u


    Let us presume that on the column ID we have ID 1, 2, 3, 4, 5, 6, 7 .....
    My problem is that when I'm filtering the DataGrid by station x and that station x has ID 4, after I double-clicking that line to see the entry, it's showing me allways ID 1, 2, 3, 4 and so on ...
    I don't know if I made ​​it clear.
    No, I'm afraid that that's not clear. It sounds like you're saying that double-clicking a row removes the filtering, but that would only be the case if you actually have code to remove the filtering I would have thought. Perhaps you should describe EXACTLY what you do and EXACTLY what you see, step by step, including relevant code and screenshots if appropriate.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    Let me please try again.

    I have a Main Form with one of the buttons called Viewing the database. When I press this button Form 2 is loaded. On this Form I have a DataGridView who show's me all the entry from a tabel from the database. The DataGridView it look's like I described you on the last post.
    On the Form 2 I have text boxes for all this: line, station, error, by, problem and data (txtline, txtstation, txterror, txtby, txtproblem, txtdata). Also have one button called Search.
    Let us presume that i'm entering on txtstation letter X and then I press Search button. The DataGridView is filtered correctly and it's showing me the ID 4 and station X (please see tabel from the post above).
    When I'm double-clicking ID 4 to see that entry, Form 3 it's loading with that entry. The problem is that the Form 3 is loading ID 1 not 4.
    I hope it's clear now

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    This is the code I use for DataGridView1_CellDoubleClick Event:
    Code:
    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
    
            Try
    
    
                Form3.Show()
    
                Form3.txtdata.Text = ""
                Form3.cboline.Text = ""
                Form3.cbostation.Text = ""
                Form3.rtferror.Text = ""
                Form3.rtfproblem.Text = ""
    			Form3.txtby.Text = ""
    
           
            	Form3.tip_operation = "editing"
    			
    			Form3.txtid.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("id").ToString
                Form3.txtdata.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("data").ToString
                Form3.cboline.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("line").ToString
                Form3.cbostation.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("station").ToString
                Form3.rtferror.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("error").ToString
                Form3.rtfproblem.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("problem").ToString
    			Form3.txtby.Text = dt_interv.Rows(Me.DataGridView1.CurrentRow.Index).Item("by").ToString
    
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
        End Sub

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    That's because you are using the index of the grid row as an index into the DataTable. If you have filtered the grid down to one row then obviously the index of the current grid row is going to be zero. If you then use that as an index into the DataTable you're going to get the row at index zero in the DataTable, which is NOT the same row as is displayed in the grid. This is a perfect example of why you should be using a BindingSource, which I advocated back in post #2. You use that as the only point of access to the bound data and then you can't make silly mistakes like this. You would get the current row like so:
    Code:
    Dim currentRow = DirectCast(myBindingSource.Current, DataRowView)
    and then get each field from that `currentRow`.

    By the way, you should be configuring all the controls on Form3 first and then displaying it as the very last step. I'm also guessing that ShowDialog would be more appropriate than Show.

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Feb 2014
    Posts
    22

    Re: Filtering DataGridView from textboxes

    I will see if I can get it work
    Thanks for the advice.

  16. #16
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Filtering DataGridView from textboxes

    If you don't want to use a BindingSource for some unfathomable reason () then you can get the bound DataRowView like this:
    Code:
    Dim currentRow = DirectCast(myDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView)

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
  •  



Click Here to Expand Forum to Full Width