Strange thing setting rowfilter

Question

Strange thing. I'm trying to update the rowfilter in a datagridview. I have a global variable called G_UpdateId that gets set from another form. Then when the search form is shown (That has the datagrid view) the form.show event runs the following code.

Though I can see that the variable has the correct value (835) the grid filters to a different value (1063)?

Private Sub FrmSearch_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        Stop
        If G_UpdateId > -1 Then
            dsSearchGrid.Tables("SEARCHGRID").DefaultView.RowFilter = "DETAILID =" & G_UpdateId
            G_UpdateId = -1
        End If

End Sub

If I hard code the value of the record I want to filter for:

Private Sub FrmSearch_Shown(sender As Object, e As EventArgs) Handles Me.Shown

        Stop
        If G_UpdateId > -1 Then
            dsSearchGrid.Tables("SEARCHGRID").DefaultView.RowFilter = "DETAILID =" & 835
            G_UpdateId = -1
        End If
    End Sub

Then it works correctly.
Anyone know what is causing this?
When I hover over the G_UpdateId variable in the rowfilter line it is definitely 835

Answer 1

Hello,

First off I'd suggest using a BindingSource which provides many benefits over simply using a DataTable. This way in tangent with language extensions you never have to touch the underlying DataTable or the actual DataGridView. Yes as you read through my reply there is a lot more code but less code is not always best. The code presented when I coded this way (using Entity Framework Core now) this code would be in a class project, the code has no attachments to any one project and is reusable. Also the code provided contains, starts with and ends with as provided in the following code.

Going with your current issue. if G_UpdateId is a Integer then treat it as an integer and if a string treat it like a string. For instance, the following two language extensions ensure the right data type is passed.

<Runtime.CompilerServices.Extension>  
Public Sub RowFilter(pSender As BindingSource, pField As String, pValue As String)  
    Dim dt = CType(pSender.DataSource, DataTable)  
    dt.DefaultView.RowFilter = $"{pField} = '{pValue.Trim().EscapeApostrophe()}'"  
End Sub  
<Runtime.CompilerServices.Extension>  
Public Sub RowFilter(pSender As BindingSource, pField As String, pValue As Integer)  
    Dim dt = CType(pSender.DataSource, DataTable)  
    dt.DefaultView.RowFilter = $"{pField} = {pValue}"  
End Sub  

Why? Because this makes sure values are formatted correctly, reusable. Then to get at underlying data.

<Runtime.CompilerServices.Extension()>  
Public Function DataTable(pSender As BindingSource) As DataTable  
    Return DirectCast(pSender.DataSource, DataTable)  
End Function  
<Runtime.CompilerServices.Extension()>  
Public Function CurrentRow(pSender As BindingSource) As DataRow  
    Return (CType(pSender.Current, DataRowView)).Row  
End Function  

Clear filter

<Runtime.CompilerServices.Extension>  
Public Sub RowFilterClear(pSender As BindingSource)  
    CType(pSender.DataSource, DataTable).DefaultView.RowFilter = ""  
End Sub  

Place the above into a code modules

Imports System.Windows.Forms  
  
Public Module Extensions  
    <Runtime.CompilerServices.Extension>  
    Public Sub RowFilter(pSender As BindingSource, pField As String, pValue As String)  
        Dim dt = CType(pSender.DataSource, DataTable)  
        dt.DefaultView.RowFilter = $"{pField} = '{pValue.Trim().EscapeApostrophe()}'"  
    End Sub  
    <Runtime.CompilerServices.Extension>  
    Public Sub RowFilter(pSender As BindingSource, pField As String, pValue As Integer)  
        Dim dt = CType(pSender.DataSource, DataTable)  
        dt.DefaultView.RowFilter = $"{pField} = {pValue}"  
    End Sub  
    <Runtime.CompilerServices.Extension>  
    Public Sub RowFilterClear(pSender As BindingSource)  
        CType(pSender.DataSource, DataTable).DefaultView.RowFilter = ""  
    End Sub  
    <Runtime.CompilerServices.Extension()>  
    Public Function DataTable(pSender As BindingSource) As DataTable  
        Return DirectCast(pSender.DataSource, DataTable)  
    End Function  
    <Runtime.CompilerServices.Extension()>  
    Public Function CurrentRow(pSender As BindingSource) As DataRow  
        Return (CType(pSender.Current, DataRowView)).Row  
    End Function  
    <Runtime.CompilerServices.Extension()>  
    Public Function Locate(pSender As BindingSource, pKey As String, pValue As String) As Integer  
  
        Dim position As Integer = -1  
  
        position = pSender.Find(pKey, pValue)  
        If position > -1 Then  
            pSender.Position = position  
        End If  
  
        Return position  
  
    End Function  
    <Runtime.CompilerServices.Extension>  
    Public Function EscapeApostrophe(pSender As String) As String  
        Return pSender.Replace("'", "''")  
    End Function  
End Module  
  

A non-working mockup

Public Class SomeClass  
    Public BindingSource As New BindingSource  
    Public DataGridView1 As New DataGridView  
    Private G_UpdateId As Integer  
    Public Sub Load()  
        BindingSource.DataSource = GetMockedData()  
        DataGridView1.DataSource = BindingSource  
    End Sub  
    Public Sub FilterData()  
        BindingSource.RowFilter("DETAILID", G_UpdateId)  
    End Sub  
    Public Sub GetCurrentDataRow()  
        Dim CurrentRow As DataRow = BindingSource.CurrentRow()  
    End Sub  
    Public Sub GetTable()  
        Dim Table As DataTable = BindingSource.DataTable()  
    End Sub  
  
    Public Function GetMockedData() As DataTable  
        Return New DataTable()  
    End Function  
End Class  
  

So this

If G_UpdateId > -1 Then  
    dsSearchGrid.Tables("SEARCHGRID").DefaultView.RowFilter = "DETAILID =" & 835  
    G_UpdateId = -1  
End If  

Changes to

If G_UpdateId > -1 Then  
    BindingSource.RowFilter("DETAILID", G_UpdateId)  
    G_UpdateId = -1  
End If  

Answer 2

Ok, I want to try your method. Unfortunately I don't understand how it works.
Currently I have:
An Access database
A connection string 'con'
A DataAdapter 'daSearchGrid'
A DataSet 'dSSearchGrid'
A DataTable ("SEARCHGRID")
The datagrid's datasource is set as below
DV1.DataSource = dsSearchGrid.Tables("SEARCHGRID")

So presumably I have to connect the BindingSource to my database somehow. Is it done in the GetMockedData

Public Function GetMockedData() As DataTable
         Return New DataTable()
     End Function

Is that done in the above function?

Does your extension enable me to apply filters to the DataGridView from anywhere in the project?