Update record and display in DataGridView without reload (refresh) in VB.Net

Dear Sir,

I'm trying to updated the database then reflect it to the DataGridView without reloading in dapper VB.NET.

So it stays in the filter state in the DataGridView.

It's also possible that the code I posted is still wrong

Please Guide Me.

I'm using an Access database and I want to keep using the code I posted

Thanks

Private _criteriasBindingList As New SortableBindingList(Of Person)()
Private bindingSource As BindingSource = Nothing
Dim PersonService As New PersonService()

Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click
    Try
        Dim people As List(Of Person) = TryCast(bindingSource.DataSource, List(Of Person))
        Dim bs = TryCast(bindingSource.DataSource, SortableBindingList(Of Person))
        For Each person As Person In people
            Dim Update = New Person With {
                             .TIME = TXTTIME.Text,
                             .OTHERS1 = TXTOTHERS1.Text,
                             .OTHERS2 = TXTOTHERS2.Text,
                             .NAMEPERSON = person.NAMEPERSON,
                             .DATE = person.DATE
            PersonService.UpdatePerson(Update)
        Next
        MessageBox.Show("Person In successfully updated")
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Myapp", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    End Try
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    loaddata()
End Sub
Private Sub loaddata()
    _criteriasBindingList = New SortableBindingList(Of Person)(CType(PersonService.GetPERSON(), IList(Of Person)))
    bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
    DataGridView1.DataSource = bindingSource 'Set the data source.
End Sub

Private Sub myFilter(str1 As String, str2 As String)
    If (String.IsNullOrEmpty(str1) AndAlso String.IsNullOrEmpty(str2)) Then
        bindingSource.DataSource = _criteriasBindingList
    ElseIf (String.IsNullOrEmpty(str1)) Then
        bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.DATE.ToString("dd-MM-yyyy").Contains(str2)).ToList()
    ElseIf (String.IsNullOrEmpty(str2)) Then
        bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.NAMEPERSON.ToLower().Contains(str1)).ToList()
    Else
        bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.NAMEPERSON.ToLower().Contains(str1) AndAlso c.DATE.ToString("dd-MM-yyyy").Contains(str2)).ToList()
    End If
End Sub
Private Sub TextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles TXTNAME.KeyDown
    Dim str1 = TXTNAME.Text.Trim().ToLower()
    Dim str2 = TXTDATE.Text.Trim().ToLower()
    myFilter(str1, str2)
End Sub

Private Sub TextBox2_KeyDown(sender As Object, e As KeyEventArgs) Handles TXTDATE.KeyDown
    Dim str1 = TXTNAME.Text.Trim().ToLower()
    Dim str2 = TXTDATE.Text.Trim().ToLower()
    myFilter(str1, str2)
End Sub
Public Class Person
    Public Property ID As Integer
    Public Property NAMEPERSON As String
    Public Property AGE As Integer
    Public Property [POSITION] As String
    Public Property [DATE] As DateTime
    Public Property DAYS As String
    Public Property TIME() As String
    Public Property OTHERS1 As String
    Public Property OTHERS2 As String
    Public Property OTHERS3 As String
End Class
Public Class PersonService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Person.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function GetPERSON() As IEnumerable(Of Person)
        Dim sql = "SELECT PERSON.ID AS [ID],MASTERID.NAMEPERSON AS [NAMEPERSON],PERSON.AGE AS [AGE],[PERSON.POSITION] AS [POSITION],PERSON.DATE AS [DATE],format(PERSON.DATE,'dddd') AS [DAYS],PERSON.TIME AS [TIME],PERSON.Others1 AS [OTHERS1],PERSON.Others2 AS [OTHERS2],PERSON.Others3 AS [OTHERS3] FROM PERSON INNER JOIN MASTERID ON PERSON.ID = MASTERID.ID"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Person)(sql).ToList()
        End Using
    End Function
    Public Sub UpdatePerson(ByVal Obj As Person)
        Dim sql = $"UPDATE PERSON INNER JOIN MASTERID ON (PERSON.ID = MASTERID.ID) Set PERSON.TIME = '{Obj.TIME}',PERSON.OTHERS1 = '{Obj.OTHERS1}',PERSON.OTHERS2 = '{Obj.OTHERS2}' WHERE MASTERID.NAMEPERSON = '{Obj.NAMEPERSON}' AND PERSON.DATE = #{Obj.DATE}# ;"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub

End Class

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel


''' <summary>
''' Provides a generic collection that supports data binding and additionally supports sorting.
''' See http://msdn.microsoft.com/en-us/library/ms993236.aspx
''' If the elements are IComparable it uses that; otherwise compares the ToString()
''' </summary>
''' <typeparam name="T">The type of elements in the list.</typeparam>
Public Class SortableBindingList(Of T As Class)
    Inherits BindingList(Of T)

    Private _isSorted As Boolean
    Private _sortDirection As ListSortDirection = ListSortDirection.Ascending
    Private _sortProperty As PropertyDescriptor

    ''' <summary>
    ''' Initializes a new instance of the <see cref="SortableBindingList{T}"/> class.
    ''' </summary>
    Public Sub New()
    End Sub

    ''' <summary>
    ''' Initializes a new instance of the <see cref="SortableBindingList{T}"/> class.
    ''' </summary>
    ''' <param name="list">An <see cref="T:System.Collections.Generic.IList`1" /> of items to be contained in the <see cref="T:System.ComponentModel.BindingList`1" />.</param>
    Public Sub New(ByVal list As IList(Of T))
        MyBase.New(list)
    End Sub

    ''' <summary>
    ''' Gets a value indicating whether the list supports sorting.
    ''' </summary>
    Protected Overrides ReadOnly Property SupportsSortingCore As Boolean
        Get
            Return True
        End Get
    End Property

    ''' <summary>
    ''' Gets a value indicating whether the list is sorted.
    ''' </summary>
    Protected Overrides ReadOnly Property IsSortedCore As Boolean
        Get
            Return _isSorted
        End Get
    End Property

    ''' <summary>
    ''' Gets the direction the list is sorted.
    ''' </summary>
    Protected Overrides ReadOnly Property SortDirectionCore As ListSortDirection
        Get
            Return _sortDirection
        End Get
    End Property

    ''' <summary>
    ''' Gets the property descriptor that is used for sorting the list if sorting is implemented in a derived class; otherwise, returns null
    ''' </summary>
    Protected Overrides ReadOnly Property SortPropertyCore As PropertyDescriptor
        Get
            Return _sortProperty
        End Get
    End Property

    ''' <summary>
    ''' Removes any sort applied with ApplySortCore if sorting is implemented
    ''' </summary>
    Protected Overrides Sub RemoveSortCore()
        _sortDirection = ListSortDirection.Ascending
        _sortProperty = Nothing
        _isSorted = False 'thanks Luca
    End Sub

    ''' <summary>
    ''' Sorts the items if overridden in a derived class
    ''' </summary>
    ''' <param name="prop"></param>
    ''' <param name="direction"></param>
    Protected Overrides Sub ApplySortCore(ByVal prop As PropertyDescriptor, ByVal direction As ListSortDirection)
        _sortProperty = prop
        _sortDirection = direction

        Dim list As List(Of T) = TryCast(Items, List(Of T))
        If list Is Nothing Then
            Return
        End If

        list.Sort(AddressOf Compare)

        _isSorted = True
        'fire an event that the list has been changed.
        OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
    End Sub
    Private Function Compare(ByVal lhs As T, ByVal rhs As T) As Integer
        Dim result = OnComparison(lhs, rhs)
        'invert if descending
        If _sortDirection = ListSortDirection.Descending Then
            result = -result
        End If
        Return result
    End Function

    Private Function OnComparison(ByVal lhs As T, ByVal rhs As T) As Integer
        Dim lhsValue As Object = If(lhs Is Nothing, Nothing, _sortProperty.GetValue(lhs))
        Dim rhsValue As Object = If(rhs Is Nothing, Nothing, _sortProperty.GetValue(rhs))
        If lhsValue Is Nothing Then
            Return If(rhsValue Is Nothing, 0, -1) 'nulls are equal
        End If
        If rhsValue Is Nothing Then
            Return 1 'first has value, second doesn't
        End If
        If TypeOf lhsValue Is IComparable Then
            Return DirectCast(lhsValue, IComparable).CompareTo(rhsValue)
        End If
        If lhsValue.Equals(rhsValue) Then
            Return 0 'both are the same
        End If
        'not comparable, compare ToString
        Return lhsValue.ToString().CompareTo(rhsValue.ToString())
    End Function
End Class