How to insert to a database without looping with vb.net and Have an error Value cannot be null in CellEndEdit

Question

I'm Trying to insert to a database without looping with vb.net and Have an error Value cannot be null in CellEndEdit

This post I refer to @dr.null

Actually, this is related to the previous post link

1. In this code I create an insert to the database by looping from the DatagridView in Event Button (btninsert) Maybe the way I did it was wrong so I want no looping

below code reference to save without looping

To get the underlying data source with the original ProductName values:

Dim displayList = DirectCast(DataGridView1.DataSource, BindingList(Of Product))
Dim outputList = displayList.
    Select(Function(p) New Product With {
    .ProductName = dictProducts(p.ProductName).ProductName,
    .Unit = p.Unit
})
' ... etc.

2. so I have a problem also after I change 'DataGridView1.AllowUserToAddRows=False' then I bind to the binding source on do add new row via binding source then at that time do esc on the keyboard then there is an error Value cannot be null

in line below code in event CellEndEdit

 If String.IsNullOrEmpty(boundItem.Unit) AndAlso
                   dictProducts.TryGetValue(boundItem.ProductName, unit) Then

Is it possible that the code I implemented was wrong?

Please Guide me

Thanks

Imports System.ComponentModel
Imports System.Data.OleDb
Imports Dapper

Public Class customtextboxdgvautocomplete
    Private dictProducts As Dictionary(Of String, String)
    'Private BindingSource1 As BindingSource = Nothing
    Private bindinglist1 As New BindingList(Of ProductFinal)
    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)
        dictProducts = ProductFinalService.GetDistinctProducts().
            ToDictionary(Function(p) p.ProductName, Function(p) p.Unit)
        'BindingSource1 = New BindingSource With {.DataSource = New BindingList(Of ProductFinal)}
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.AllowUserToAddRows = False
        'DataGridView1.DataSource = BindingSource1
        DataGridView1.DataSource = bindinglist1        
        DataGridView1.Columns("Unit").ReadOnly = True
        'BindingSource1.AddNew()
        bindinglist1.AddNew() 

    End Sub
  Private Sub DataGridView1_EditingControlShowing(
        sender As Object,
        e As DataGridViewEditingControlShowingEventArgs) Handles _
        DataGridView1.EditingControlShowing
        If TypeOf e.Control Is TextBox Then
            Dim dgv = DirectCast(sender, DataGridView)
            Dim tb = DirectCast(e.Control, TextBox)
            Dim cellRect As Rectangle = DataGridView1.GetCellDisplayRectangle(DataGridView1.CurrentCell.ColumnIndex, DataGridView1.CurrentCell.RowIndex, True)
            If dgv.CurrentCell.ColumnIndex = dgv.Columns("ProductName").Index Then
                If tb.AutoCompleteCustomSource.Count = 0 Then
                    tb.AutoCompleteSource = AutoCompleteSource.CustomSource
                    tb.AutoCompleteCustomSource.AddRange(dictProducts.Keys.ToArray())
                End If
                tb.AutoCompleteMode = AutoCompleteMode.Suggest
            Else
                tb.AutoCompleteMode = AutoCompleteMode.None
            End If
  End If
    End Sub
   Private Sub DataGridView1_CellValidating(
        sender As Object,
        e As DataGridViewCellValidatingEventArgs) _
        Handles DataGridView1.CellValidating
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
           e.RowIndex <> dgv.NewRowIndex Then
            Dim key = e.FormattedValue?.ToString()

            If String.IsNullOrEmpty(key) OrElse
               Not dictProducts.ContainsKey(key) Then
                Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, ProductFinal)

                boundItem.Unit = Nothing
                dgv.Rows(e.RowIndex).ErrorText = "Invalid Product!"
                dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                e.Cancel = True
            End If
        End If
    End Sub
    Private Sub DataGridView1_CellValidated(
        sender As Object,
        e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index Then
            Dim boundItem = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, ProductFinal)
            If boundItem IsNot Nothing Then
                Dim value = dictProducts(boundItem.ProductName)
                If value <> boundItem.Unit Then
                    boundItem.Unit = value
                    dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                End If
                dgv.Rows(e.RowIndex).ErrorText = Nothing
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellEndEdit(
        sender As Object,
        e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim dgv = DirectCast(sender, DataGridView)
        Dim src = DirectCast(dgv.DataSource, bindinglist1))
        'Dim src = DirectCast(dgv.DataSource, BindingSource)
        If src.Count = e.RowIndex Then Return
        If e.ColumnIndex = dgv.Columns("ProductName").Index Then
            If Not String.IsNullOrEmpty(dgv.Rows(e.RowIndex).ErrorText) Then
                Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, ProductFinal)
                Dim unit As String = Nothing

                If String.IsNullOrEmpty(boundItem.Unit) AndAlso
                   dictProducts.TryGetValue(boundItem.ProductName, unit) Then
                    boundItem.Unit = unit
                    dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                    dgv.Rows(e.RowIndex).ErrorText = Nothing
                End If
            End If
        End If
    End Sub

    Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load


    End Sub
Private Sub Btninsert_Click(sender As Object, e As EventArgs) Handles Btninsert.Click

'With looping 
 'Try
            'Dim ProductFinalService As New ProductFinalService()
            'For Each row As DataGridViewRow In DataGridView1.Rows
                'Dim outputList = New ProductFinal With {
            '.ProductName = CStr(row.Cells(0).Value),
            '.Unit = CStr(row.Cells(1).Value)
            '}
                'ProductFinalService.InsertProductsoption3(CType(outputList, 'ProductFinal))
            'Next
            'MessageBox.Show(Me, "Successfull")
        'Catch err As Exception
            'MessageBox.Show(Me, err.Message)
        'End Try

`without looping Error  BC30456 Select is not a member of BindingSource
 'Try
            'Dim ProductFinalService As New ProductFinalService()
            'Dim displayList = 'DirectCast(DataGridView1.DataSource, BindingSource)
            'Dim outputList = displayList.
            'Select(Function(p) New ProductFinal With {
            '.ProductName = dictProducts(p.ProductName),
            '.Unit = p.Unit
        '})
            'ProductFinalService.InsertProducts(outputList)
            'MessageBox.Show(Me, "Successfull")
        'Catch err As Exception
            'MessageBox.Show(Me, err.Message)
        'End Try
    `without looping via bindinglist1 No errors and successful
Try
            Dim ProductFinalService As New ProductFinalService()
            Dim displayList = DirectCast(DataGridView1.DataSource, bindinglist1)
            Dim outputList = displayList.
            Select(Function(p) New ProductFinal With {
            .ProductName = dictProducts(p.ProductName),
            .Unit = p.Unit
        })
            ProductFinalService.InsertProducts(outputList)
            MessageBox.Show(Me, "Successfull")
        Catch err As Exception
            MessageBox.Show(Me, err.Message)
        End Try
    End Sub
  Protected Overrides Function ProcessCmdKey(ByRef msg As Message, ByVal keyData As Keys) As Boolean
        If keyData = Keys.Tab AndAlso CBool(Keys.Enter) Then
            If Me.DataGridView1.Rows.Count > 0 Then
                If DataGridView1.CurrentRow.Index = (DataGridView1.Rows.Count - 1) Then
                    bindingSource1.AddNew()
                End If
            Else
            End If
            Return True
        End If
        Return MyBase.ProcessCmdKey(msg, keyData)
    End Function


End Class
Public Class ProductFinalService
    Private Shared Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;
                    Data Source=|DataDirectory|\test03092024option1.accdb;
                    Persist Security Info=False;"
    End Function

    ' ...

    Friend Shared Function GetDistinctProducts() As IEnumerable(Of ProductFinal)
        Dim sql = "SELECT DISTINCT ProductName, Unit 
                       FROM Products 
                       ORDER BY ProductName"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ProductFinal)(sql)
        End Using
    End Function

    Public Sub InsertProductsoption3(ByVal Obj As ProductFinal)
        Dim sql = "INSERT INTO saveproducts (ProductName,Unit) VALUES (@ProductName,@Unit);"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql, New With {
    Key Obj.ProductName,
    Key Obj.Unit
})
        End Using
    End Sub
        Public Sub InsertProducts(Obj As IEnumerable(Of ProductFinal))
        Dim sql = "INSERT INTO saveproducts (ProductName,Unit) VALUES (@ProductName,@Unit);"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
_conn.Execute(sql, obj)
        End Using
    End Sub
End Class
Public Class ProductFinal
    '<Browsable(False)>
    'Public Property Font As String = "☑️"

    Public Property ProductName As String
    Public Property Unit As String

    Public Overrides Function ToString() As String
        Return $"{ProductName} {Unit}"
    End Function

End Class