Filtering a dgv using its Datasource(dt).DefaultView.RowFilter with a concatenated string

Question

For some reason when I apply my dgvBarcodes.Datasource.Defaultview.Rowfilter = strDGVBarcodesFilter it's looking for (column "Test"), instead of filtering the rows on column "LongDescription=Test". This is unexpected behaviour. How do I get the .DefaultView.RowFilter to filter all rows based on "LongDescription=Test"?

the strDGVBarcodesFilter in this example = "LongDescription=Test", when moused over, and the error I'm getting is "System.Data.EvaluateException: 'Cannot find column [test].'"

Event handler:

  If txtLongDescription2.Text IsNot Nothing Then
        Dim strLongDescription As String = txtLongDescription2.Text 
        Dim strLongDescriptionFilter As String = ""
        If strLongDescription = "" Then
            strLongDescriptionFilter = ""
        Else
            strLongDescriptionFilter = "LongDescription=" & strLongDescription
        End If
        Dim strDGVBarcodesFilter As String = fnBuildFilterString(strMainBrandFilter, strSubBrandFilter, strLongDescriptionFilter)
        dgvBarcodes.DataSource.DefaultView.Rowfilter = strDGVBarcodesFilter
    End If

Filter string builder function;

Private Function fnBuildFilterString(strFilterComponent1 As String, strFilterComponent2 As String, Optional strFilterComponent3 As String = "", Optional strFilterComponent4 As String = "", Optional strFilterComponent5 As String = "") As String
    'Essentially it builds this; strMainFilter = strFilterComponent1 & " AND " & strFilterComponent2 & " AND " & strFilterComponent3 & " AND " & strFilterComponent4 & " AND " & strFilterComponent5
    Dim strMainFilter As String = "" 'Reset the masterfilter string to be empty

    If strFilterComponent1 <> "" Then  'If the filter component isn't empty
        If strMainFilter = "" Then 'And if the masterfilter is empty
            strMainFilter = strFilterComponent1 'Adds the filter component to the masterfilter string
        Else 'The masterfilter isn't empty
            strMainFilter &= " AND " & strFilterComponent1 'then it adds " AND " &field to the masterfilter string
        End If
    Else 'Filter component is empty, do nothing
    End If 'check next component

    If strFilterComponent2 <> "" Then  'If the filter component isn't empty
        If strMainFilter <> "" Then 'And if the masterfilter isn't empty
            strMainFilter &= " AND " & strFilterComponent2 'then it adds " AND " &field to the masterfilter string
        Else 'The masterfilter is empty
            strMainFilter = strFilterComponent2 'Adds the filter component to the masterfilter string
        End If
    Else 'Filter component is empty, do nothing
    End If 'check next component        

    If strFilterComponent3 <> "" Then  'If the filter component isn't empty
        If strMainFilter <> "" Then 'And if the masterfilter isn't empty
            strMainFilter &= " AND " & strFilterComponent3 'then it adds " AND " &field to the masterfilter string
        Else 'The masterfilter is empty
            strMainFilter = strFilterComponent3 'Adds the filter component to the masterfilter string
        End If
    Else 'Filter component is empty, do nothing
    End If 'check next component

    If strFilterComponent4 <> "" Then  'If the filter component isn't empty
        If strMainFilter <> "" Then 'And if the masterfilter isn't empty
            strMainFilter &= " AND " & strFilterComponent4 'then it adds " AND " &field to the masterfilter string
        Else 'The masterfilter is empty
            strMainFilter = strFilterComponent4 'Adds the filter component to the masterfilter string
        End If
    Else 'Filter component is empty, do nothing
    End If 'check next component

    If strFilterComponent5 <> "" Then  'If the filter component isn't empty
        If strMainFilter <> "" Then 'And if the masterfilter isn't empty
            strMainFilter &= " AND " & strFilterComponent5 'then it adds " AND " &field to the masterfilter string
        Else 'The masterfilter is empty
            strMainFilter = strFilterComponent5 'Adds the filter component to the masterfilter string
        End If
    Else 'Filter component is empty, do nothing
    End If 'check next component

    Return strMainFilter
End Function

Don't bind and filter like that. Add a `BindingSource` to your form, bind the `DataTable` to the `BindingSource` and the `BindingSource` to the grid, then set the `Filter` property of the `BindingSource`. It ends up doing the same thing but is less messy. — jmcilhinney, Jun 01 '21 at 11:33
don't bind like what exactly? I haven't shown how I bound my dgv to the dt? Can you elaborate why adding a BindingSource is a superior method over binding the dgv to the dt directly, and just changing the defaultview property of the dt? That's what I've been advised before and I do not understand whats the difference or it's pros/cons. IF it works as intended, it should be just as simple/clean? Understanding the distinction would help me along on my coding journey. — Polardog, Jun 01 '21 at 12:15

score 0 · Answer 1 · answered Jun 01 '21 at 11:42

0

Think about what you're doing there. If you did this in your VB code:

Dim someStringVariable As String

'...

If someStringVariable = Test Then

would you expect that to compare that variable to the literal text "Test"? Of course not, because there's no literal text there. You have to wrap text in double-quotes to indicate that it's a literal String. If you expect to use literal text in your SQL code (a filter is a SQL WHERE clause) then you need to do the equivalent. How do you denote literal text in SQL code? With single-quotes.

Here's the proper way to do what you want to do:

strLongDescriptionFilter = $"LongDescription = '{strLongDescription}'"

and:

Dim criteria As New List(Of String)

If Not String.IsNullOrWhitespace(strFilterComponent1) Then
    criteria.Add(strFilterComponent1)
End If

'Etc.

Return String.Join(" AND ", criteria)

There are other improvements you could make to but that's probably enough for a first step.

answered Jun 01 '21 at 11:42

jmcilhinney

50,448
5
26
46

I would expect the dgv to show the filtered rows where (column"LongDescription") = strLongDescription. thats how it works when I put the dt into a dv first, but for some reason when I use the defaultview it is looking for column strLongdescription and I don't understand why it doesn't behave the same way Also, I have no clue what your code example is supposed to represent `strLongDescriptionFilter = $"LongDescription = '{strLongDescription}'"` AFAIK, $ is not used in VB, and you haven't provided a value for the filter string, because it's commented out so now it only says "LongDescription=" – Polardog Jun 01 '21 at 12:03
@Polardog, I'm not inclined to fight against such a strong determination to be wrong. The information I have provided will fix your issue and simplify your code significantly. It is your prerogative to not use it. – jmcilhinney Jun 01 '21 at 12:29
@Polardog, for the record, `$"LongDescription = '{strLongDescription}'"` is functionally equivalent to `String.Format("LongDescription = '{0}'", strLongDescription)`, which is functionally equivalent to `"LongDescription = '" & strLongDescription & "'"`, which you would have discovered if you had tried it as suggested instead of telling me that, AFAYK, I'm wrong. – jmcilhinney Jun 01 '21 at 12:31
I found out why the value for the filter string was commented out, I misplaced the ". Also, the reason why it used to work with dv's and not a default view was due to the datatype used and had nothing to do with the dv; strLongDescriptionFilter = "LongDescription=" & intLongDescription works if intLongDescription is an integer, but not if its a string. I don't know anything about SQL, which is why your initial explanation went right over my head. still don't understand what the $ is used for but apparantly its crucial because without it the whole thing turns into a string. – Polardog Jun 04 '21 at 10:24
.... Rather than a string with a variable in it. I also now appreciate your second code block, it looks a lot cleaner and shorter than my string builder. Although I don't understand yet why you use a list instead of a stringarray. @jmcilhinney Thank you for your contribution. it was helpful – Polardog Jun 04 '21 at 10:41
The use of the $ indicates string interpolation. You can read the documentation for the `String.Format` method to see how it is used to perform composite formatting. String interpolation is basically native language support for that `String.Format` method. That's why i said that they are functionally equivalent in an earlier comment. String interpolation is just "syntactic sugar" and the compiler treats it as a call to `String.Format`. – jmcilhinney Jun 04 '21 at 13:20
The reason I used a `List(Of String)` is that, based on your code, you won't know how many items it will contain. It starts empty and then you add zero to five items to it in `If` blocks. When done, those items are combined into a single `String` with and `AND` operator between each pair, regardless of how many there are. If there's none then you end up with an empty `String`. If there's one then you get just that value with no `AND` operators. If there are two or more then you get an `AND` operator between each pair. – jmcilhinney Jun 04 '21 at 13:23

Filtering a dgv using its Datasource(dt).DefaultView.RowFilter with a concatenated string

1 Answers1