CODEWITHSUNDEEP
vb.netdatatabledatagridviewrowfilterdefaultview
Polardog
Polardog

Reputation: 1

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

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

Upvotes: 0

Views: 197

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

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.

Upvotes: 0

Related Questions