how to fill in datatable from selecting checkbox datagridview in form 2 to datagridview in form 1 with dapper in vb.net

Question

so the code below if the barcode character length is less than 11 then do it manually by selecting checkbox from datagridview in form 2 and then filldatatable to datagridview in form 1. But I haven't succeeded is there something wrong with my code. Please Guide

Thanks

code in form1

Public Class Form1
  Private Sub FillDataTable(iRow As Integer, ByVal Codeproduct As String, Barcode As String, ColorCode As String, Qty As Integer)

        Dim row As DataRow = _myTable.NewRow()
        row("No") = iRow
        row("Codeproduct") = Codeproduct
        row("Barcode") = Barcode
        row("ColorCode") = ColorCode
        row("Qty") = Qty
        _myTable.Rows.Add(row)
    End Sub
 Private Sub BtxtBarcode_KeyPress(sender As Object, e As KeyPressEventArgs) Handles BtxtBarcode.KeyPress
        Try
            If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Enter) Then
                If BtxtBarcode.Text = "" Then
                    MsgBox("Please enter item barcode.", MsgBoxStyle.Information, "Information")
                Else
                    If BtxtBarcode.TextLength <= 11 Then
                        GetItemData(BtxtBarcode.Text)
                        Dim iRow As Integer
                        If Grid.RowCount - 1 = 0 Then
                            iRow = 1
                        Else
                            iRow = Convert.ToInt32(Grid.Rows(Grid.RowCount - 2).Cells(0).Value.ToString()) + 1
                        End If
                        Dim Found As Boolean = False
                        Dim newValue As Integer = 1
                        For j = 0 To Me.Grid.Rows.Count - 1
                            If Convert.ToString(Me.Grid.Rows(j).Cells(1).Value) = BtxtBarcode.Text.ToString() Then
                                Found = True

                                If Integer.TryParse(Grid.Rows(j).Cells(4).Value, newValue) Then
                                    newValue += 1
                                Else
                                    newValue = 1
                                End If
                                Grid.Rows(j).Cells(4).Value = newValue.ToString()
                                BtxtBarcode.Clear()
                                Exit For
                            End If
                        Next
                        If Not Found Then
'below is the filldatatable method code and update code progress
 Dim f2 As New Form2
                            f2.ShowDialog()
                            Dim columntwo = f2.DataGridView1.CurrentRow.Cells(2).Value.ToString
                            Dim columnthree = f2.DataGridView1.CurrentRow.Cells(3).Value.ToString
                            FillDataTable(iRow, columntwo, _BBarcode, columnthree, newValue)
                            Grid.DataSource = _myTable
                            BtxtBarcode.Clear()
  End If
                    End If
                End If

                End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "POS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Form2.Show()
    End Sub
End Class
Public Class Stocks
    Public Property Process() As Boolean
    Public Property Id() As Integer
    Public Property CodeProduct() As String
    Public Property Colorcode() As String
    Public Property Size() As String
    Public Property Qty_Stock() As Integer
End Class
Public Class item
    Public Property No() As Integer
    Public Property CodeProduct() As String
    Public Property Barcode() As String
    Public Property Colorcode() As String
End Class

code in form2

Public Class Form2
  Private iService As New Itemservice2()
    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        DataGridView1.DataSource = iService.Getitem2(Form1.Btxtcodeproduct.Text)
        DataGridView1.Columns(0).Visible = False
    End Sub
End Class
Public Class Itemservice2

    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = DbContext.GetOledbConnectionString()
  Public Function Getitem2(ByVal code As String) As IEnumerable(Of Stocks)
        Dim sql = $"SELECT * FROM Stocks WHERE CodeProduct = '{code}'"
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            Return _conn.Query(Of Stocks)(sql).ToList()
        End Using
    End Function
    Public Function GetByCodeProductOrBarcode(ByVal code As String) As item
        Dim sql = $"SELECT Items.Barcode,Items.CodeProduct,Stocks.Colorcode,Stocks.Size FROM Items INNER JOIN Stocks ON Items.Barcode = Stocks.Barcode WHERE Items.CodeProduct = '{code}' or Items.Barcode = '{code}'"
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            Return _conn.Query(Of item)(sql).FirstOrDefault()
        End Using
    End Function
End Class

Desired Result in datagridview in form 1

No	CodeProduct	Barcode	Colorcode	Qty
1	KBE 0927	03-22164010	BHTF07KP	1

CODE IN FORM1

Public Class Form1
 Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)
        _myTable = New DataTable
        _myTable.Columns.AddRange({
        New DataColumn("No", GetType(Integer)),
        New DataColumn("Codeproduct", GetType(String)),
        New DataColumn("Barcode", GetType(String)),
        New DataColumn("ColorCode", GetType(String)),
        New DataColumn("Qty", GetType(Integer))
    })
        Grid.DataSource = _myTable
    End Sub
Private Sub BtxtBarcode_KeyPress(sender As Object,
                                 e As KeyPressEventArgs) _
                                 Handles BtxtBarcode.KeyPress
        If e.KeyChar = Convert.ToChar(Keys.Enter) Then
            AddEntry()
        End If
    End Sub
Private Sub AddEntry()
        If String.IsNullOrWhiteSpace(BtxtBarcode.Text) OrElse
           String.IsNullOrWhiteSpace(Btxtcodeproduct.Text) Then
            MsgBox("Please enter item barcode or product code...")
            Return
        End If
        Dim productCode = Btxtcodeproduct.Text.Trim()
        Dim row As DataRow
        Dim _BBarcode = BtxtBarcode.Text.Trim()
        If _myTable.Rows.Cast(Of DataRow).Any() Then
            If BtxtBarcode.TextLength <= 10 Then
                Using frm = New Form2(productCode)
                    If frm.ShowDialog() = DialogResult.OK Then
                    End If
                End Using
                row = _myTable.
                    Select($"Codeproduct = '{productCode}'").
                    FirstOrDefault()
            Else
                row = _myTable.
                    Select($"Barcode = '{_BBarcode}'").
                   FirstOrDefault()
            End If
            ' Change the length as needed...
            If BtxtBarcode.TextLength >= 11 Then
                row = _myTable.
                    Select($"Codeproduct = '{productCode}'").
                    FirstOrDefault()
            Else
                row = _myTable.
                    Select($"Barcode = '{_BBarcode}'").
                   FirstOrDefault()
            End If
            If row IsNot Nothing Then
                Dim qty = If(row("Qty") Is DBNull.Value, 1, row.Field(Of Integer)("Qty"))
                row.SetField("Qty", qty + 1)
                Return
            End If
        End If
        Try
            Dim service = New Itemservice2()
            Dim stock = service.Getitem2(productCode).FirstOrDefault()
            If stock Is Nothing Then
                MessageBox.Show("Unknown product!")
                Return
            End If
            ' Where does _BBarcode come from?
            _myTable.Rows.Add(_myTable.Rows.Count + 1, productCode,
                                  _BBarcode, stock.Colorcode, 1)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

CODE IN FORM2

Public Class Form2
   Sub New()
        InitializeComponent()
    End Sub

    ' A constructor with a parameter...
    Public Sub New(productCode As String)
        Me.New
        ' Moved from Form2_Load event.
        DataGridView1.DataSource = iService.Getitem2(productCode)
        DataGridView1.Columns(0).Visible = False
    End Sub
    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
        DialogResult = DialogResult.OK
    End Sub
End Class

Answer 1

It turns out that you are trying in Form1 to increase the quantities of the products rather than adding new entries. Searching the grid for products either by a valid Barcode or Codeproduct. But that doesn't work of course if the grid is empty and therefore the Form2 part comes up. You want to show Form2, populate its grid with a List<Stocks>, find a product by Codeproduct (the Stocks model doesn't contain Barcode property), and extract the product's properties from the grid itself.

No, you don't need to do this. You don't need the Form2 part at all. You have the Itemservice2 class which exposes the Getitem2 method which executes a database query to select a recode by the CodeProduct field. So, why don't you do that in Form1 itself?

For example, in Form1

Private _myTable As DataTable

Protected Overrides Sub OnLoad(e As EventArgs)
    MyBase.OnLoad(e)

    _myTable = New DataTable
    _myTable.Columns.AddRange({
        New DataColumn("No", GetType(Integer)),
        New DataColumn("Codeproduct", GetType(String)),
        New DataColumn("Barcode", GetType(String)),
        New DataColumn("ColorCode", GetType(String)),
        New DataColumn("Qty", GetType(Integer))
    })
    Grid.DataSource = _myTable
End Sub

Private Sub BtxtBarcode_KeyPress(sender As Object,
                                 e As KeyPressEventArgs) _
                                 Handles BtxtBarcode.KeyPress
    If e.KeyChar = Convert.ToChar(Keys.Enter) Then
        AddEntry()
    End If
End Sub

Private Sub AddEntry()
    If String.IsNullOrWhiteSpace(BtxtBarcode.Text) OrElse
       String.IsNullOrWhiteSpace(BtxtCodeProduct.Text) Then
        MsgBox("Please enter item barcode or product code...", ...
        Return
    End If

    Dim productCode = BtxtCodeProduct.Text.Trim()

    If _myTable.Rows.Cast(Of DataRow).Any() Then
        Dim row As DataRow

        ' Change the length as needed...
        If BtxtBarcode.TextLength <> 11 Then
            row = _myTable.
                Select($"Codeproduct = '{productCode}'").
                FirstOrDefault()
        Else
            Dim barcode = BtxtBarcode.Text.Trim()
            row = _myTable.
                Select($"Barcode = '{barcode}'").
                FirstOrDefault()
        End If

        If row IsNot Nothing Then
            Dim qty = If(row("Qty") Is DBNull.Value, 1, row.Field(Of Integer)("Qty"))
            row.SetField("Qty", qty + 1)
            Return
        End If
    End If

    Try
        Dim service = New Itemservice2()
        Dim stock = service.Getitem2(productCode).FirstOrDefault()

        If stock Is Nothing Then
            MessageBox.Show("Unknown product!")
            Return
        End If

        ' Where does _BBarcode come from?
        _myTable.Rows.Add(_myTable.Rows.Count + 1, productCode,
                          _BBarcode, stock.Colorcode, 1)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Note here, when you have a data source, then deal with it to manipulate the data not with the control itself. The control's business is to visualize what's in that data source in a way or another.

Accordingly, we call here the DataTable.Select method to search the Barcode or Codeproduct fields - if the DataTable is not empty - for matches and select the first DataRow if any to increase the Qty field.

If the DataTable is empty or the Select method returns nothing for some reason, the next piece of code within the Try ... End Try block will run and search the database for that product. If any, you'll get a Stocks object, get the required details from it and add a new DataRow to your _myTable.

---

As for your code snippet, to auto populate the grid of Form2 when you show it, add a constructor overload that takes a product identifier:

Public Class Form2 

    ' The default constructor...
    Sub New()
        InitializeComponent()
    End Sub

    ' A constructor with a parameter...
    Public Sub New(productCode As String)
        Me.New
        ' Moved from Form2_Load event.
        DataGridView1.DataSource = iService.Getitem2(productCode)
        DataGridView1.Columns(0).Visible = False
    End Sub

    ' ...etc.
End Class

In Form1, create a new Form2 and pass that product code to the constructor. For example:

Dim productCode = BtxtCodeProduct.Text.Trim()

Using frm = New Form2(productCode)
    If frm.ShowDialog() = DialogResult.OK Then
        ' ...etc.
    End If
End Using

Form2 must return DialogResult.OK value to Form1 to proceed or DialogResult.Cancel to Return or Exit Sub. Add OK and Canel buttons for that.

Private Sub btnOK_Click(sender As Object, e As EventArgs) _
    Handles btnOK.Click
    DialogResult = DialogResult.OK
End Sub

Now, the AddEntry method in Form1 should be something like this.

Private Sub AddEntry()
    If String.IsNullOrWhiteSpace(BtxtBarcode.Text) AndAlso
        String.IsNullOrWhiteSpace(BtxtCodeProduct.Text) Then
        MsgBox("Please enter item barcode or product code...", ...
        Return
    End If

    Dim productCode = BtxtCodeProduct.Text.Trim()
    Dim _BBarcode = BtxtBarcode.Text.Trim()

    If (_BBarcode.Length < 11) Then
        Using frm = New Form2(productCode)
            If frm.ShowDialog() = DialogResult.OK Then
            ' Get what you want from Form2...
            End If
        End Using
    Else ' _BBarcode.Length >= 11
        Dim row As DataRow = Nothing

        If _myTable.Rows.Cast(Of DataRow).Any() Then
            row = _myTable.Select($"Barcode = '{_BBarcode}' Or Codeproduct = '{productCode}'").FirstOrDefault()
        End If

        If row IsNot Nothing Then
            Dim qty = If(row("Qty") Is DBNull.Value, 0, row.Field(Of Integer)("Qty"))
            row.SetField("Qty", qty + 1)
        Else
            ' No matches were found in Form1's grid. What now?
            ' Exit Sub ?
            ' row = _myTable.NewRow and add some values then
            ' _myTable.Rows.Add(row) ?
            ' It's up to you!
        End If
    End If
End Sub