Datagridview - Search and filter particular column(s) only

Apologies if this has been answered in a similar post, I did check but could not find a solution to my specific problem.

I have a form, button, datagridview, textbox.

DataGridview has 5 columns already added to it, but no data. The data is being pulled from a .CSV and then populating the datagridview.

• Actor Name
• Movie Name
• Year Released
• Movie Rating
• Review Score

I'm currently pulling the data by using the command button (code) as below.

Code:

---

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        DataGridView1.Rows.Clear()

        Dim TextFieldParser1 As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Data\VB.net\Actors.csv")
        TextFieldParser1.Delimiters = New String() {","}

        While Not TextFieldParser1.EndOfData
            Dim Row1 As String() = TextFieldParser1.ReadFields()
            DataGridView1.Rows.Add(Row1)
        End While
End Sub

---

I want to search within a particular column and only show the data relevant. For example I want to find all the movies released in 2020, the datagrid shows me that. I don't want to search all the columns matching 2020, I only want to focus on the column "Year Released" and retrieve that information. Eventually I'm going to have a range of multiple actors names as well as movie release dates to search and retrieve data. I want to start nice and easy so that I can understand this further.

Can anyone assist or advise ?

Many thanks!

Don't add the data directly to the grid. Populate a DataTable with the data, bind the DataTable to a BindingSource and bind that to the grid. You can then filter the data via the BindingSource like so:

vb.net Code:

BindingSource1.Filter = $"[Year Released] = {TextBox1.Text}"

There are a few specific points to note. Firstly, you should add the BindingSource to the form in the designer, just as you did the grid.

Secondly, you should give some thought to data types. As it stands, you are treating all the data as text. When you create your DataTable and add the DataColumns to it, you get to specify the data type for each. It should be obvious that the Year Released column represents numbers, not text, so the data should be actual numbers. That means that, after reading the text values from the file, you should convert them to numbers before adding them to the DataTable. You can then treat them as numbers, which the code I provided does. If you were to leave the data as text then you would have to treat it as text for the purposes of filtering, which would mean doing this instead:

vb.net Code:

BindingSource1.Filter = $"[Year Released] = '{TextBox1.Text}'"

A small difference but an important one. For columns that are text, you need to wrap the filter value in single quotes while numbers you do not. Text columns can be filtered using wildcards where other data types cannot. You might filter on multiple columns like so:

vb.net Code:

BindingSource1.Filter = $"[Actor Name] LIKE '{TextBox1.Text}%' AND [Year Released] = {TextBox2.Text}"

Thank you for giving me further direction. I wasn't aware of bindingsource but will check the forums for me to learn from.

Hi

I managed to get this work however when trying the filter on Movie Rating it returns the error ;
"System.Data.EvaluateException: 'Cannot perform '=' operation on System.String and System.Int32.'"
which means it's trying to process something which contains string and integer and causing it to crash.

What do I need to change for the GetType;

dt.Columns.Add("Movie Rating", GetType(String))


Much appreciated for any input.

Is there a way I can filter by multiple "Released Year". For example if a movie was released in 2010, 2015 and also 2020.

How could I code that ?

Would I need to use a textbox box but in multi mode ?

Quote:

---

Originally Posted by Serial

Hi

I managed to get this work however when trying the filter on Movie Rating it returns the error ;
"System.Data.EvaluateException: 'Cannot perform '=' operation on System.String and System.Int32.'"
which means it's trying to process something which contains string and integer and causing it to crash.

What do I need to change for the GetType;

dt.Columns.Add("Movie Rating", GetType(String))


Much appreciated for any input.

---

Grrr! I addressed this specifically in my post and you've just basically ignored it. I told you to use data types properly. If you are specifying the Movie Rating column as containing String values then you have to use String values in the filter and I specifically told you that, in the filter expression, String values must be wrapped in single quotes while numbers are not. I don't mind you not knowing things but when I explain them specifically and then you ask me to repeat what I just explained then I'm not going let that go without comment.

Quote:

---

Originally Posted by Serial

Is there a way I can filter by multiple "Released Year". For example if a movie was released in 2010, 2015 and also 2020.

How could I code that ?

Would I need to use a textbox box but in multi mode ?

---

The BindingSource.Filter property supports a small subset of SQL-like syntax in this context and you can use anything supported. You can learn what's supported here:

https://docs.microsoft.com/en-au/dot...umn_Expression

As explained there, you can use an IN clause. How you get the multiple year values is up to you - maybe you have a multiselect ListBox or maybe you have a TextBox and expect the user to enter a comma-delimited list - but the final filter expression might look something like this:

Quote:

---

[Release Year] IN (2010, 2015, 2020)

---

If you were to use a TextBox then the code might look like this:

vb.net Code:

BindingSource1.Filter = $"[Year Released] IN ({TextBox1.Text})"

If you were to use a ListBox then it might look like this:

vb.net Code:

BindingSource1.Filter = $"[Year Released] IN ({String.Join(",", ListBox1.SelectedItems.Cast(Of String)()})"

Thank you for helping me, I'm ever so grateful for your help jmcilhinney