How to reset BindingSource/BindingList sequence number update to database with VB.NET

Question

I'm trying to reset BindingSource/BindingList sequence number update to database with VB.NET

or something went wrong in my code implementation.

actually this refers to the link to this post a link! but the difference is that this post updates to the database.

Please guide me

Thanks

Public Class Formwithdb
    Private bindingSource As BindingSource = Nothing
    Dim productservice As New PurchaseService()
    Private Function GetProduct() As Product
        Return If(DataGridView1.SelectedCells.Count = 0, Nothing, TryCast(DataGridView1.SelectedCells(0).OwningRow.DataBoundItem, Product))
    End Function
    Private Sub Formwithdb_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        bindingSource = New BindingSource With {.DataSource = New BindingList(Of Product)(CType(productservice.Getpurchase(), IList(Of Product)))}
        DataGridView1.DataSource = bindingSource
        DataGridView1.Columns("No").ReadOnly = True
        DataGridView1.Columns("Codeproduct").ReadOnly = True
        DataGridView1.Columns("Qty").ReadOnly = True
        DataGridView1.Columns("Qty_Stock").ReadOnly = True
        DataGridView1.Columns("Coldel").ReadOnly = True
    End Sub
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim dgv = DirectCast(sender, DataGridView)
        If e.RowIndex >= 0 AndAlso
        dgv.Columns("ColDel") Is dgv.Columns(e.ColumnIndex) Then
            Dim item = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If item IsNot Nothing AndAlso
                MessageBox.Show("Confirm ...", "App", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                Try
                    Dim i = 1
                    Dim bs = TryCast(bindingSource.DataSource, BindingList(Of Product))
                    Dim codeproduct = bs.FirstOrDefault(Function(x) x.Codeproduct = GetProduct.Codeproduct)
                    productservice.Deleteproduct(codeproduct)
                    bs.Remove(item)
                    For Each row As Product In bs
                        row.No = i
                        i += 1

'in the line of code below that I implemented incorrectly

                        Dim codeproductdetail = bs.FirstOrDefault(Function(x) row.Codeproduct = GetProduct.Codeproduct)
                        productservice.Updateproductno(codeproductdetail)
                    Next
                    DataGridView1.Refresh()
                    MessageBox.Show("Successfully")
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "App", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End Try
            End If
        End If
    End Sub
End Class
Public Class PurchaseService
   Public Sub Updateproductno(ByVal Obj As Product)
        Dim sql = $"UPDATE Product Set [No] = {Obj.No} WHERE Codeproduct ='{Obj.Codeproduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class

according to the guide from @dr.null and below code problem solved

Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim dgv = DirectCast(sender, DataGridView)
        If e.RowIndex >= 0 AndAlso
        dgv.Columns("ColDel") Is dgv.Columns(e.ColumnIndex) Then
            Dim item = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If item IsNot Nothing AndAlso
                MessageBox.Show("Confirm ...", "App", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                Try
                    Dim i = 1
                    Dim bs = TryCast(bindingSource.DataSource, BindingList(Of Product))
                    Dim codeproduct = bs.FirstOrDefault(Function(x) x.Codeproduct = GetProduct.Codeproduct)
                    productservice.Deleteproduct(codeproduct)
                    bs.Remove(item)
                    For Each row As Product In bs
                        row.No = i
                        i += 1
                        Dim Update = New Product With {
                                 .No = row.No,
                                 .Codeproduct = row.Codeproduct}
                        productservice.Updateproductno(Update)
                    Next
                    DataGridView1.Refresh()
                    MessageBox.Show("Successfully")
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "App", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End Try
            End If
        End If
    End Sub
Public Class PurchaseService
   Public Sub Updateproductno(ByVal Obj As Product)
        Dim sql = $"UPDATE Product Set [No] = {Obj.No} WHERE Codeproduct ='{Obj.Codeproduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class

Answer 1

This is due to help and recommendations from @dr.null . I thank you very much

Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim dgv = DirectCast(sender, DataGridView)
        If e.RowIndex >= 0 AndAlso
        dgv.Columns("ColDel") Is dgv.Columns(e.ColumnIndex) Then
            Dim item = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If item IsNot Nothing AndAlso
                MessageBox.Show("Confirm ...", "App", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                Try
                    Dim i = 1
                    Dim bs = TryCast(bindingSource.DataSource, BindingList(Of Product))
                    Dim codeproduct = bs.FirstOrDefault(Function(x) x.Codeproduct = GetProduct.Codeproduct)
                    productservice.Deleteproduct(codeproduct)
                    bs.Remove(item)
                    For Each row As Product In bs
                        row.No = i
                        i += 1
                        Dim Update = New Product With {
                                 .No = row.No,
                                 .Codeproduct = row.Codeproduct}
                        productservice.Updateproductno(Update)
                    Next
                    DataGridView1.Refresh()
                    MessageBox.Show("Successfully")
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "App", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End Try
            End If
        End If
    End Sub
Public Class PurchaseService
   Public Sub Updateproductno(ByVal Obj As Product)
        Dim sql = $"UPDATE Product Set [No] = {Obj.No} WHERE Codeproduct ='{Obj.Codeproduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class