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

---

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.

Quote:

---

Originally Posted by jmcilhinney

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

Quote:

---

Originally Posted by seek4code

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.

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.

Quote:

---

Originally Posted by seek4code

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.

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

Quote:

---

Originally Posted by seek4code

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.

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.

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.

Quote:

---

Originally Posted by seek4code

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.

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

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

---

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.

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

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)

---