Add a BatchID and BatchDateTime to a table

Question

Hi everyone,
I have a form named FrmSalespersonsCommissions with a datagridview named CommissionsToBePaidDGV that shows the commissions owe to a specific salesperson.

It works pretty well but I have a problem when trying to apply a batchid and batchdatetime to the CommissionsTable, the message is shown below.

Imports System.ComponentModel  
Imports System.IO  
Public Class FrmSalespersonsCommissions  
    Private ReadOnly SQL As New SQLControl  
    Private ReadOnly BaseFormClass As New BaseFormClass  
    Private IgnoreEvent As Boolean = True  
    Private Sub FrmSalespersonsCommissions_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
        Me.IgnoreEvent = True  
        Dim DBDT As New DataTable("Commissions")  
        'TODO: This line of code loads data into the 'ACDataDataSet.CommissionsToBePaid' table. You can move, or remove it, as needed.  
        Me.CommissionsToBePaidTableAdapter.Fill(Me.ACDataDataSet.CommissionsToBePaid)  
        Try  
            BaseFormClass.SetEventHandlersTextBox(Me)  
            BaseFormClass.SetEventHandlersComboBox(Me)  
            BaseFormClass.SetEventHandlersButton(Me)  
            BtnUpdateDGV_Click(Me, Nothing)  
            Me.LanguageIDTB.Text = FrmLanguages.strLanguageID  
            Me.LanguageDescTB.Text = FrmLanguages.strLanguageDesc  
            Me.LanguageCodeTB.Text = FrmLanguages.strLanguageCode  
            Me.CommissionsToBePaidDGV.Columns(3).Visible = False  
            If Me.CommissionPaidToTB.Text <> "" Then  
                If SQL.DBDS IsNot Nothing Then  
                    SQL.DBDS.Clear()  
                End If  
                SQL.RunQuery("SELECT SalespersonID,Username FROM Application.CommissionsToBePaid Where Username = '" & CommissionPaidToTB.Text & "' ")  
                If SQL.DBDS.Tables(0).Rows.Count <> 0 Then  
                    Me.CommissionPaidToIDTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SalespersonID")  
                End If  
            End If  
            If Application.OpenForms().OfType(Of FrmSalespersons).Any Then  
                FrmSalespersons.Close()  
            End If  
            If Application.OpenForms().OfType(Of FrmCashDrawer).Any Then  
                'Dim FormMain As New FrmMainForm  
                FrmMainForm.Visible = True  
                'FrmCashDrawer.Close()  
            End If  
            RenameColumnDGV()  
            BtnShowDGVButtons()  
            Me.AuthorizedUserID.Text = FrmCashDrawer.AuthorizedUserID.Text  
            Me.AuthorizedUser.Text = FrmCashDrawer.AuthorizedUser.Text  
            Me.StationIDTB.Text = FrmCashDrawer.StationNumberTB.Text  
            Me.IgnoreEvent = False  
            RetrieveCommissionsInformation()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click  
        Try  
            Me.Close()  
            Me.Dispose()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Public Sub LoadCommissionsToBePaidDGV()  
        Try  
            SQL.ExecQuery("SELECT OrderDate,OrderNo,SalespersonID,Username,LineTotal,PercentageOnCategories,PercentageOnProducts,  
                           CommissionPayableOnCategories,CommissionPayableOnProducts,PayWithCash,PayWithCheck,BatchID,BatchDateTime,CommissionPaid   
                           FROM Application.CommissionsToBePaid Where SalespersonID = '" & CommissionPaidToIDTB.Text & "' ")  
            CommissionsToBePaidDGV.DataSource = SQL.DBDT  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Public Sub BtnUpdateDGV_Click(sender As Object, e As EventArgs) Handles BtnUpdateDGV.Click  
        LoadCommissionsToBePaidDGV()  
    End Sub  
  
    Private Sub CommissionPaidToIDTB_TextChanged(sender As Object, e As EventArgs) Handles CommissionPaidToIDTB.TextChanged  
        Me.BtnUpdateDGV.PerformClick()  
    End Sub  
  
    Private Sub RenameColumnDGV()  
        Try  
            With CommissionsToBePaidDGV  
                .RowHeadersVisible = False  
                .Columns(0).HeaderCell.Value = "Order date"  
                .Columns(0).Width = 105  
                .Columns(1).HeaderCell.Value = "Order number"  
                .Columns(1).Width = 130  
                .Columns(2).HeaderCell.Value = "ID"  
                .Columns(2).Width = 50  
                .Columns(3).HeaderCell.Value = "Name"  
                .Columns(3).Width = 200  
                .Columns(4).HeaderCell.Value = "Line total"  
                .Columns(4).Width = 105  
                .Columns(5).HeaderCell.Value = "% category"  
                .Columns(5).Width = 75  
                .Columns(6).HeaderCell.Value = "% product"  
                .Columns(6).Width = 75  
                .Columns(7).HeaderCell.Value = "$ on category"  
                .Columns(7).Width = 90  
                .Columns(8).HeaderCell.Value = "$ on product"  
                .Columns(8).Width = 90  
                .Columns(9).HeaderCell.Value = "Pay with cash"  
                .Columns(9).Width = 65  
                .Columns(10).HeaderCell.Value = "Pay by check"  
                .Columns(10).Width = 65  
                .Columns(11).HeaderCell.Value = "Batch ID"  
                .Columns(11).Width = 50  
                .Columns(12).HeaderCell.Value = "Batch Date"  
                .Columns(12).Width = 105  
                .Columns(13).HeaderCell.Value = "PAID"  
                .Columns(13).Width = 50  
  
            End With  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub ShowFrmMsgSmallerSaved()  
        Try  
            Dim SmallerMsg As New FrmMsgSmaller  
            FrmMsgSmaller.Close()  
            FrmMsgSmaller.TextBox1.Text = ""  
            FrmMsgSmaller.TextBox1.Text = "YOUR RECORD WAS SAVED!"  
            FrmMsgSmaller.Show()  
            Dim SW2 As New Stopwatch  
            SW2.Start()  
            Do  
                Application.DoEvents()  
            Loop Until SW2.ElapsedMilliseconds >= 1000  
            FrmMsgSmaller.Hide()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnShowDGVButtons()  
        Try  
            Dim BtnPayCash As New DataGridViewButtonColumn With {  
            .Text = "PAY CASH",  
            .FlatStyle = FlatStyle.Popup  
        }  
            CommissionsToBePaidDGV.Columns.Insert(8, BtnPayCash)  
            With BtnPayCash  
                .HeaderText = "CASH"  
                .Text = " $ Cash $"  
                .Name = "BtnPayCash"  
                .UseColumnTextForButtonValue = True  
                .Width = "85"  
            End With  
            BtnPayCash.DefaultCellStyle.BackColor = Color.Yellow  
            BtnPayCash.DefaultCellStyle.ForeColor = Color.Black  
  
            Dim BtnPayByCheck As New DataGridViewButtonColumn With {  
            .Text = "CHECK",  
            .FlatStyle = FlatStyle.Popup  
        }  
            CommissionsToBePaidDGV.Columns.Insert(10, BtnPayByCheck)  
            With BtnPayByCheck  
                .HeaderText = "CHECK"  
                .Text = "Check"  
                .Name = "BtnPayByCheck"  
                .UseColumnTextForButtonValue = True  
                .Width = "85"  
            End With  
            BtnPayByCheck.DefaultCellStyle.BackColor = Color.Lime  
            BtnPayByCheck.DefaultCellStyle.ForeColor = Color.Black  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnPayAllInCash_Click(sender As Object, e As EventArgs) Handles BtnPayAllInCash.Click  
        Try  
            Dim row As DataGridViewRow  
            For Each row In CommissionsToBePaidDGV.Rows  
                row.Cells(11).Value = 1  
                row.Cells(15).Value = 1  
                CountCashCheckedRows()  
            Next  
            AddSelectedCash()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnPayAllByCheck_Click(sender As Object, e As EventArgs) Handles BtnPayAllByCheck.Click  
        Try  
            Dim row As DataGridViewRow  
            For Each row In CommissionsToBePaidDGV.Rows  
                row.Cells(12).Value = 1  
                row.Cells(15).Value = 1  
                CountCheckCheckedRows()  
            Next  
            AddSelectedCheck()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnCancelAllCash_Click(sender As Object, e As EventArgs) Handles BtnCancelAllCash.Click  
        Try  
            Dim i As Integer  
            i = CommissionsToBePaidDGV.CurrentRow.Index  
            Dim row As DataGridViewRow  
            For Each row In CommissionsToBePaidDGV.Rows  
                row.Cells(11).Value = 0  
                row.Cells(15).Value = 0  
            Next  
            Me.TotalAmountSelectedTB.Text = ("0.00")  
            Me.CashSelectedTB.Text = 0  
            Me.CommissionsToBePaidDGV.Columns("PayWithCheck").Visible = True  
            Me.BtnPayAllByCheck.Visible = True  
            Me.BtnCancelAllChecks.Visible = True  
            Me.CommissionsToBePaidDGV.Columns(10).Visible = True  
            CommissionsToBePaidDGV.Rows(i).DefaultCellStyle.BackColor = Color.White  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnCancelAllChecks_Click(sender As Object, e As EventArgs) Handles BtnCancelAllChecks.Click  
        Try  
            Dim row As DataGridViewRow  
            For Each row In CommissionsToBePaidDGV.Rows  
                row.Cells(12).Value = 0  
                row.Cells(15).Value = 0  
            Next  
            Me.TotalAmountSelectedTB.Text = ("0.00")  
            Me.CheckSelectedTB.Text = 0  
            Me.CommissionsToBePaidDGV.Columns("PayWithCash").Visible = True  
            Me.BtnPayAllInCash.Visible = True  
            Me.BtnCancelAllCash.Visible = True  
            Me.CommissionsToBePaidDGV.Columns(8).Visible = True  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub AddSelectedCash()  
        Try  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
                Dim IsSelected = row.Field(Of Boolean)("PayWithCash")  
                If IsSelected Then  
                    TotalAmountSelectedTB.Text = CDbl(DBDT.Compute("SUM(CommissionPayableOnProducts)", String.Empty)).ToString("0.00")  
                Else  
                    TotalAmountSelectedTB.Text = ToString("0.00")  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub AddSelectedCheck()  
        Try  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
                Dim IsSelected = row.Field(Of Boolean)("PayWithCheck")  
                If IsSelected Then  
                    TotalAmountSelectedTB.Text = CDbl(DBDT.Compute("SUM(CommissionPayableOnProducts)", String.Empty)).ToString("0.00")  
                Else  
                    TotalAmountSelectedTB.Text = ToString("0.00")  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub Prc_CalcSum()  
        Try  
            If (Me.IgnoreEvent) Then  
                Exit Sub    ' -->> exit  
            End If  
            ' ---  
            Me.TotalAmountSelectedTB.Text = "0.00"  
            Dim mySum As Double = 0.00  
            ' ---  
            'Dim i As Integer  
            For myRow As Integer = 0 To Me.CommissionsToBePaidDGV.Rows.Count - 1  
                If (Me.CommissionsToBePaidDGV(11, myRow).Value = True) And (Me.CommissionsToBePaidDGV(12, myRow).Value = True) Then  
                    MessageBox.Show("You can't choose pay with cash AND pay by check at the same time.",  
                    "Auto Cash Register Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)  
                    Exit Sub  
                End If  
                If (Me.CommissionsToBePaidDGV(11, myRow).Value = True) Then  
                    mySum += CSng(Me.CommissionsToBePaidDGV(9, myRow).Value).ToString("0.00")  
                    Me.TotalAmountSelectedTB.Text = mySum.ToString("0.00")  
                End If  
            Next  
            For myRow As Integer = 0 To Me.CommissionsToBePaidDGV.Rows.Count - 1  
                If (Me.CommissionsToBePaidDGV(12, myRow).Value = True) Then  
                    mySum += CSng(Me.CommissionsToBePaidDGV(9, myRow).Value).ToString("0.00")  
                    Me.TotalAmountSelectedTB.Text = mySum.ToString("0.00")  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
  
    End Sub  
  
    Private Sub CountCashCheckedRows()  
        Try  
            Dim CountCashRows As Integer  
            For Each row As DataGridViewRow In CommissionsToBePaidDGV.Rows  
                Dim CheckedCashRows As Boolean = Convert.ToBoolean(row.Cells("PayWithCash").Value)  
                If CheckedCashRows Then  
                    CountCashRows += 1  
                End If  
            Next  
            CashSelectedTB.Text = CountCashRows  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub CountCheckCheckedRows()  
        Try  
            Dim CountCheckRows As Integer  
            For Each row As DataGridViewRow In CommissionsToBePaidDGV.Rows  
                Dim CheckedCheckRows As Boolean = Convert.ToBoolean(row.Cells("PayWithCheck").Value)  
                If CheckedCheckRows Then  
                    CountCheckRows += 1  
                End If  
            Next  
            CheckSelectedTB.Text = CountCheckRows  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub CommissionsToBePaidDGV_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles CommissionsToBePaidDGV.CellValueChanged  
        Try  
            Call Me.Prc_CalcSum()  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            If CommissionsToBePaidDGV.RowCount() > 0 Then  
                Dim Count As Integer = 0  
                If CommissionsToBePaidDGV.Columns(e.ColumnIndex).Name = "PayWithCash" Then  
                    For i As Integer = 0 To CommissionsToBePaidDGV.RowCount() - 1  
                        If CommissionsToBePaidDGV.Rows(i).Cells("PayWithCash").Value Then  
                            Count += 1  
                        End If  
                    Next  
                    LblRecordsSelectedPayWithCash.Text = String.Format("{0} record(s) to pay with cash", Count.ToString("0.00"))  
                End If  
                If CommissionsToBePaidDGV.Columns(e.ColumnIndex).Name = "PayWithCheck" Then  
                    For i As Integer = 0 To CommissionsToBePaidDGV.RowCount() - 1  
                        If CommissionsToBePaidDGV.Rows(i).Cells("PayWithCheck").Value Then  
                            Count += 1  
                        End If  
                    Next  
                    LblRecordsSelectedPayWithCheck.Text = String.Format("{0} record(s) to pay with check", Count.ToString("0.00"))  
                End If  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
  
    End Sub  
  
    Private Sub CommissionsToBePaidDGV_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles CommissionsToBePaidDGV.CurrentCellDirtyStateChanged  
        Try  
            If (CommissionsToBePaidDGV.IsCurrentCellDirty) Then  
                ' -- commit  
                CommissionsToBePaidDGV.CommitEdit(DataGridViewDataErrorContexts.Commit)  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub CommissionsToBePaidDGV_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles CommissionsToBePaidDGV.CellContentClick  
        Try  
            If e.ColumnIndex = 8 Then  
                Dim i As Integer  
                i = CommissionsToBePaidDGV.CurrentRow.Index  
                If CommissionsToBePaidDGV.Item(11, i).Value = 0 Then  
                    Me.TotalAmountSelectedTB.Text = CommissionsToBePaidDGV.Item(9, i).Value  
                    CommissionsToBePaidDGV.Item(11, i).Value = 1  
                    CommissionsToBePaidDGV.Item(15, i).Value = 1  
                    CountCashCheckedRows()  
                Else  
                    CommissionsToBePaidDGV.Item(11, i).Value = 0  
                    CommissionsToBePaidDGV.Item(15, i).Value = 0  
                    CountCashCheckedRows()  
                End If  
            End If  
            If e.ColumnIndex = 10 Then  
                Dim i As Integer  
                i = CommissionsToBePaidDGV.CurrentRow.Index  
                If CommissionsToBePaidDGV.Item(12, i).Value = 0 Then  
                    Me.TotalAmountSelectedTB.Text = CommissionsToBePaidDGV.Item(9, i).Value  
                    CommissionsToBePaidDGV.Item(12, i).Value = 1  
                    CommissionsToBePaidDGV.Item(15, i).Value = 1  
                    CountCheckCheckedRows()  
                Else  
                    CommissionsToBePaidDGV.Item(12, i).Value = 0  
                    CommissionsToBePaidDGV.Item(15, i).Value = 0  
                    CountCheckCheckedRows()  
                End If  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub RetrieveCommissionsInformation()  
        Try  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            SQL.RunQuery("SELECT EmployeeID,Username,CommissionsOnCategories,PercentageOnCategories,CommissionsOnProducts,PercentageOnProducts,   
                     BasedOnProfitAmount FROM Application.Employees Where Username = '" & CommissionPaidToTB.Text & "' ")  
            If SQL.DBDS.Tables(0).Rows.Count <> 0 Then  
                Me.CommPercOnCategoriesTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("PercentageOnCategories")  
                Me.CommPercOnProductsTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("PercentageOnProducts")  
                Me.CommissionsOnCategoriesCB.Checked = SQL.DBDS.Tables(0).Rows(0).Item("CommissionsOnCategories")  
                Me.CommissionsOnProductsCB.Checked = SQL.DBDS.Tables(0).Rows(0).Item("CommissionsOnProducts")  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub CashSelectedTB_TextChanged(sender As Object, e As EventArgs) Handles CashSelectedTB.TextChanged  
        Try  
            If Me.CashSelectedTB.Text = 0 Then  
                Exit Sub  
            End If  
            If Me.CashSelectedTB.Text >= 1 Then  
                Me.CommissionsToBePaidDGV.Columns("PayWithCheck").Visible = False  
                Me.BtnPayAllByCheck.Visible = False  
                Me.BtnCancelAllChecks.Visible = False  
                Me.CommissionsToBePaidDGV.Columns(10).Visible = False  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub CheckSelectedTB_TextChanged(sender As Object, e As EventArgs) Handles CheckSelectedTB.TextChanged  
        Try  
            If Me.CheckSelectedTB.Text = 0 Then  
                Exit Sub  
            End If  
            If Me.CheckSelectedTB.Text >= 1 Then  
                Me.CommissionsToBePaidDGV.Columns("PayWithCash").Visible = False  
                Me.BtnPayAllInCash.Visible = False  
                Me.BtnCancelAllCash.Visible = False  
                Me.CommissionsToBePaidDGV.Columns(8).Visible = False  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub CreateABatch()  
        Try  
            SQL.AddParam("@BatchDateTime", Now)  
            SQL.ExecQuery("INSERT INTO Application.Batches(BatchDateTime) " &  
            "VALUES (@BatchDateTime) ", True)  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnAcceptTotal_Click(sender As Object, e As EventArgs) Handles BtnAcceptTotal.Click  
        Try  
            CreateABatch()  
            UpdateBatchInfoInCommissionsTable()  
            If CashSelectedTB.Text > 0 Then  
                UpdateBatchesIfPayCash()  
            End If  
            If CheckSelectedTB.Text > 0 Then  
                UpdateBatchesIfPayByCheck()  
            End If  
            If Me.CashSelectedTB.Text <> 0 Then  
                SQL.AddParam("@EmployeeNumber", AuthorizedUserID.Text)  
                SQL.AddParam("@Username", AuthorizedUser.Text)  
                SQL.AddParam("@StationNumber", StationIDTB.Text)  
                SQL.AddParam("@CommissionsPaid", 1)  
                SQL.AddParam("@CommissionPaidTo", CommissionPaidToTB.Text)  
                SQL.AddParam("@UniqueAmount", TotalAmountSelectedTB.Text)  
                SQL.AddParam("@TotalValue", TotalAmountSelectedTB.Text)  
                SQL.ExecQuery("INSERT INTO Application.CashCount(EmployeeNumber,Username,StationNumber,CommissionsPaid,CommissionPaidTo,UniqueAmount,TotalValue) " &  
                "VALUES (@EmployeeNumber,@Username,@StationNumber,@CommissionsPaid,@CommissionPaidTo,@UniqueAmount,@TotalValue) ", True)  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateBatchInfoInCommissionsTable()  
        Try  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            SQL.RunQuery("SELECT BatchID, BatchDateTime FROM Application.Batches WHERE BatchID = (SELECT MAX(BatchID) FROM Application.Batches)")  
            If SQL.DBDS.Tables(0).Rows.Count = 0 Then  
                Exit Sub  
            End If  
            If SQL.DBDS.Tables(0).Rows.Count > 0 Then  
                Me.BatchIDTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("BatchID")  
                Me.BatchDateTimeTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("BatchDateTime")  
            End If  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateBatchesIfPayCash()  
        Try  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
                Dim IsSelected = row.Field(Of Boolean)("PayWithCash")  
                If IsSelected Then  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchID").Value = Me.BatchIDTB.Text  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchDateTime").Value = Me.BatchDateTimeTB.Text  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateBatchesIfPayByCheck()  
        Try  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
                Dim IsSelected = row.Field(Of Boolean)("PayWithCheck")  
                If IsSelected Then  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchID").Value = Me.BatchIDTB.Text  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchDateTime").Value = Me.BatchDateTimeTB.Text  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateTSB_Click(sender As Object, e As EventArgs) Handles UpdateTSB.Click  
        Try  
            Me.Validate()  
            Me.CommissionsTableBS.EndEdit()  
            Me.CommissionsTableTableAdapter.Update(ACDataDataSet.CommissionsTable)  
            ShowFrmMsgSmallerSaved()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
End Class  

Answer 1

Hi @Claude Larocque ,
You can refer to the code below.

        For Each r As DataGridViewRow In DataGridView1.Rows  
            Dim r0 As DataGridViewCheckBoxCell = r.Cells(0)  
            Dim r1 As DataGridViewCheckBoxCell = r.Cells(1)  
            Dim r2 As DataGridViewTextBoxCell = r.Cells(2)  
            If Convert.ToBoolean(r0.EditingCellFormattedValue) OrElse Convert.ToBoolean(r1.EditingCellFormattedValue) Then  
                r2.Value = BatchDateTimeTB.Text  
            End If  
        Next  

Best Regards.
Jiachen Li

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you Jiachen Li, when I saw your answer I told myself that should work, but I have an error message telling me this:

So here is the code that I use to receive this message, please note that I did some comments and that the data in the datagridview is a query in my SQL server database that I did put on top of the code of that form.

    SELECT Application.CommissionsTable.OrderNo, Application.CommissionsTable.OrderDate, Application.CommissionsTable.OrderLineID, Application.CommissionsTable.SalespersonID, Application.Salespersons.Username,   
                         Application.CommissionsTable.CashierID, Application.CommissionsTable.ProductID, Warehouse.ProductsBasicView.ProductName, Application.CommissionsTable.LineTotal,   
                         Application.CommissionsTable.PercentageOnCategories, Application.CommissionsTable.PercentageOnProducts, Application.CommissionsTable.CommissionPayableOnCategories,   
                         Application.CommissionsTable.CommissionPayableOnProducts, Application.CommissionsTable.PayWithCash, Application.CommissionsTable.PayWithCheck, Application.CommissionsTable.BatchID,   
                         Application.CommissionsTable.BatchDateTime, Application.CommissionsTable.CommissionPaid  
	FROM Application.CommissionsTable LEFT OUTER JOIN  
                         Application.Salespersons ON Application.CommissionsTable.SalespersonID = Application.Salespersons.EmployeeID LEFT OUTER JOIN  
                         Warehouse.ProductsBasicView ON Application.CommissionsTable.ProductID = Warehouse.ProductsBasicView.ProductID  
	  
	'This is the query in SQL server management studio  
		  
	  
	  
	  
	Public Sub LoadCommissionsToBePaidDGV()  
        Try  
            SQL.ExecQuery("SELECT OrderDate,OrderNo,SalespersonID,Username,LineTotal,PercentageOnCategories,PercentageOnProducts,  
                           CommissionPayableOnCategories,CommissionPayableOnProducts,PayWithCash,PayWithCheck,BatchID,BatchDateTime,CommissionPaid   
                           FROM Application.CommissionsToBePaid Where SalespersonID = '" & CommissionPaidToIDTB.Text & "' ")  
            CommissionsToBePaidDGV.DataSource = SQL.DBDT  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
	'This above code is taking 13 fields from the query CommissionsToBePaid the fields to update the batches are 11 and 12  
	  
	Private Sub BtnAcceptTotal_Click(sender As Object, e As EventArgs) Handles BtnAcceptTotal.Click  
        Try  
            CreateABatch()  
            UpdateBatchInfoInCommissionsTable()  
            UpdateBatches()  
            UpdateTSB.PerformClick()  
  
            If Me.CashSelectedTB.Text <> 0 Then  
                SQL.AddParam("@EmployeeNumber", AuthorizedUserID.Text)  
                SQL.AddParam("@Username", AuthorizedUser.Text)  
                SQL.AddParam("@StationNumber", StationIDTB.Text)  
                SQL.AddParam("@CommissionsPaid", 1)  
                SQL.AddParam("@CommissionPaidTo", CommissionPaidToTB.Text)  
                SQL.AddParam("@UniqueAmount", TotalAmountSelectedTB.Text)  
                SQL.AddParam("@TotalValue", TotalAmountSelectedTB.Text)  
                SQL.ExecQuery("INSERT INTO Application.CashCount(EmployeeNumber,Username,StationNumber,CommissionsPaid,CommissionPaidTo,UniqueAmount,TotalValue) " &  
                "VALUES (@EmployeeNumber,@Username,@StationNumber,@CommissionsPaid,@CommissionPaidTo,@UniqueAmount,@TotalValue) ", True)  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateBatchInfoInCommissionsTable()  
        Try  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            SQL.RunQuery("SELECT BatchID, BatchDateTime FROM Application.Batches WHERE BatchID = (SELECT MAX(BatchID) FROM Application.Batches)")  
            If SQL.DBDS.Tables(0).Rows.Count = 0 Then  
                Exit Sub  
            End If  
            If SQL.DBDS.Tables(0).Rows.Count > 0 Then  
                Me.BatchIDTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("BatchID")  
                Me.BatchDateTimeTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("BatchDateTime")  
            End If  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateBatchesIfPayCash()  
        Try  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
                Dim IsSelected = row.Field(Of Boolean)("PayWithCash") = True  
                If IsSelected Then  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchID").Value = Me.BatchIDTB.Text  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchDateTime").Value = Me.BatchDateTimeTB.Text  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateBatchesIfPayByCheck()  
        Try  
            Dim DBDT = CommissionsToBePaidDGV.DataSource  
            For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
                Dim IsSelected = row.Field(Of Boolean)("PayWithCheck")  
                If IsSelected Then  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchID").Value = Me.BatchIDTB.Text  
                    CommissionsToBePaidDGV.Rows(IsSelected).Cells("BatchDateTime").Value = Me.BatchDateTimeTB.Text  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub UpdateTSB_Click(sender As Object, e As EventArgs) Handles UpdateTSB.Click  
        Try  
            Me.Validate()  
            Me.CommissionsTableBS.EndEdit()  
            Me.CommissionsTableTableAdapter.Update(ACDataDataSet.CommissionsTable)  
            ShowFrmMsgSmallerSaved()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Public Sub UpdateBatches()  
        Try  
            For Each r As DataGridViewRow In CommissionsToBePaidDGV.Rows  
                Dim r0 As DataGridViewCheckBoxCell = r.Cells(11)  
                Dim r1 As DataGridViewCheckBoxCell = r.Cells(12)  
                Dim r2 As DataGridViewTextBoxCell = r.Cells(13)  
                Dim r3 As DataGridViewTextBoxCell = r.Cells(14)  
                If Convert.ToBoolean(r0.EditingCellFormattedValue) OrElse Convert.ToBoolean(r1.EditingCellFormattedValue) Then  
                    r2.Value = BatchIDTB.Text  
                    r3.Value = BatchDateTimeTB.Text  
                End If  
            Next  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
	  
	ERROR MESSAGE WHEN CLICKING ON BtnAcceptTotal  
	  

Thanks for your time

Claude from Quebec, Canada

Answer 3

Private Sub UpdateBatchInfoInCommissionsTable()  
    Try  
        If SQL.DBDS IsNot Nothing Then  
            SQL.DBDS.Clear()  
        End If  
        SQL.RunQuery("SELECT BatchID, BatchDateTime FROM Application.Batches WHERE BatchID = (SELECT MAX(BatchID) FROM Application.Batches)")  
        If SQL.DBDS.Tables(0).Rows.Count = 0 Then  
            Exit Sub  
        End If  
        If SQL.DBDS.Tables(0).Rows.Count > 0 Then  
            Me.BatchIDTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("BatchID")  
            Me.BatchDateTimeTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("BatchDateTime")  
        End If  
        If SQL.DBDS IsNot Nothing Then  
            SQL.DBDS.Clear()  
        End If  
        If SQL.HasException(True) Then Exit Sub  
    Catch ex As Exception  
        MsgBox(ex.Message)  
        System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
    End Try  
End Sub  

Private Sub UpdateBatchesIfPayCash()  
    Try  
        Dim i As Integer  
        i = CommissionsToBePaidDGV.CurrentRow.Index  
        Dim DBDT = CommissionsToBePaidDGV.DataSource  
        For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
            Dim IsSelected = row.Field(Of Boolean)("PayWithCash")  
            If IsSelected Then  
                Dim r As DataGridViewRow  
                For Each r In CommissionsToBePaidDGV.Rows  
                    CommissionsToBePaidDGV.Rows(i).Cells("BatchID").Value = Me.BatchIDTB.Text  
                    CommissionsToBePaidDGV.Rows(i).Cells("BatchDateTime").Value = Me.BatchDateTimeTB.Text  
                    CommissionsToBePaidDGV.Rows(i).Cells("CommissionPaid").Value = 1  
                Next  
            End If  
        Next  
        If SQL.HasException(True) Then Exit Sub  
    Catch ex As Exception  
        MsgBox(ex.Message)  
        System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
    End Try  
End Sub  

Private Sub UpdateBatchesIfPayByCheck()  
    Try  
        Dim i As Integer  
        i = CommissionsToBePaidDGV.CurrentRow.Index  
        Dim DBDT = CommissionsToBePaidDGV.DataSource  
        For Each row As DataRow In DirectCast(CommissionsToBePaidDGV.DataSource, DataTable).Rows  
            Dim IsSelected = row.Field(Of Boolean)("PayWithCheck")  
            If IsSelected Then  
                Dim r As DataGridViewRow  
                For Each r In CommissionsToBePaidDGV.Rows  
                    CommissionsToBePaidDGV.Rows(i).Cells("BatchID").Value = Me.BatchIDTB.Text  
                    CommissionsToBePaidDGV.Rows(i).Cells("BatchDateTime").Value = Me.BatchDateTimeTB.Text  
                    CommissionsToBePaidDGV.Rows(i).Cells("CommissionPaid").Value = 1  
                Next  
            End If  
        Next  
        If SQL.HasException(True) Then Exit Sub  
    Catch ex As Exception  
        MsgBox(ex.Message)  
        System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
    End Try  
End Sub  

Private Sub BtnUpdateBatches_Click(sender As Object, e As EventArgs) Handles BtnUpdateBatches.Click  

    If CashSelectedTB.Text > 0 Then  
        UpdateBatchesIfPayCash()  
    End If  
    If CheckSelectedTB.Text > 0 Then  
        UpdateBatchesIfPayByCheck()  
    End If  
End Sub