When I scan a barcode that does not exist, the first product (sort alphabatically) is retrieve

Claude Larocque 666 Reputation points
2022-01-17T15:14:20.547+00:00

Hi everyone,
I have a form named FrmBilling and on that form I have a combobox named ProductCBX and a datagrid name OrderLinesDGV
That combobox has this for data:
Private Sub LoadProductCBX()
Try
'REFRESH COMBOBOX
ProductCBX.Items.Clear()
'RUN QUERY
SQL.ExecQuery("SELECT ProductID, ProductName, SellingPrice1,SellingPrice2,SellingPrice3,SellingPrice4,SellingPrice5 FROM Warehouse.ProductsActivated ORDER BY ProductName ASC")
If SQL.HasException(True) Then Exit Sub
'LOOP ROW & ADD TO COMBOBOX
For Each r As DataRow In SQL.DBDT.Rows
ProductCBX.Items.Add(r("ProductName").ToString)
Next
ProductCBX.Sorted = True
ProductCBX.AutoCompleteMode = AutoCompleteMode.None
ProductCBX.DropDownStyle = ComboBoxStyle.DropDown
ProductCBX.AutoCompleteSource = AutoCompleteSource.ListItems
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

The problem is when I scan a barcode that is not in the ProductName the first product is selected instead of having a message saying that this product does not exist...

Here an image:
165771-barcodedontexistproblem.jpg

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,562 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Claude Larocque 666 Reputation points
    2022-01-17T15:15:55.797+00:00

    Here is the code to help:
    Private Sub ProductCBX_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ProductCBX.SelectedIndexChanged
    Try
    Me.ProductDateSoldTB.Text = Now()
    UpdateOrderlinesDGVWithProduct()
    FormatAll()
    BtnLastAddedRow.PerformClick()
    Exit Sub
    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 UpdateOrderlinesDGVWithProduct()
            Try
                'CLEAR EXISTING RECORD
                If SQL.DBDS IsNot Nothing Then
                    SQL.DBDS.Clear()
                End If
                If Me.ProductCBX.Visible = True Then
                    SQL.RunQuery("SELECT * FROM Warehouse.ProductsActivated Where ProductName = '" & ProductCBX.Text & "' ")
                    If SQL.DBDS.Tables(0).Rows(0).Item(0) = 0 Then
                        MsgBox("There is no product as requested!")
                        Exit Sub
                    End If
                    If SQL.DBDS.Tables(0).Rows(0).Item(0) >= 1 Then
                        Me.InvoicedQuantityTB.Text = 0
                        Me.BackOrderYesNoCB.Checked = False
                        Me.BackorderQuantityTB.Text = 0
                        Me.ExtendedPriceTB.Text = QtyTB.Text * UnitPriceTB.Text
                        Me.SelectedProductID.Text = SQL.DBDS.Tables(0).Rows(0).Item("ProductID")
                        Me.ValidFromTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ValidFrom")
                        Me.ValidToTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ValidTo")
                        Me.ProductNameTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ProductNameOnly")
                        Me.DiscountRateTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("DiscountRate")
                        Me.DeliveryDateExpectedTB.Text = Now()
                        Me.SellingPrice1TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice1")
                        Me.SellingPrice2TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice2")
                        Me.SellingPrice3TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice3")
                        Me.SellingPrice4TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice4")
                        Me.SellingPrice5TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice5")
                        Me.TaxRate1TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("TaxRate1")
                        Me.TaxRate2TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("TaxRate2")
                        Me.TaxRate3TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("TaxRate3")
                        SQL.AddParam("@OrderNo", Me.SelectedOrderTB.Text)
                        SQL.AddParam("@ProductID", SQL.DBDS.Tables(0).Rows(0).Item("ProductID"))
                        SQL.AddParam("@ProductName", SQL.DBDS.Tables(0).Rows(0).Item("ProductNameOnly"))
                        SQL.AddParam("@Quantity", QtyTB.Text)
                        If Me.SellingPrice1CB.Checked = True Then
                            SQL.AddParam("@UnitPrice", SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice1"))
                            Me.UnitPriceTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice1")
                            Me.DiscountAmountTB.Text = Math.Round(QtyTB.Text * UnitPriceTB.Text) * DiscountRateTB.Text / 100
                            Me.LineTotalTB.Text = QtyTB.Text * UnitPriceTB.Text - DiscountAmountTB.Text
                        ElseIf SellingPrice2CB.Checked = True Then
                            SQL.AddParam("@UnitPrice", SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice2"))
                            Me.UnitPriceTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice2")
                            Me.DiscountAmountTB.Text = Math.Round(QtyTB.Text * UnitPriceTB.Text) * DiscountRateTB.Text / 100
                            Me.LineTotalTB.Text = QtyTB.Text * UnitPriceTB.Text - DiscountAmountTB.Text
                        ElseIf SellingPrice3CB.Checked = True Then
                            SQL.AddParam("@UnitPrice", SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice3"))
                            Me.UnitPriceTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice3")
                            Me.DiscountAmountTB.Text = Math.Round(QtyTB.Text * UnitPriceTB.Text) * DiscountRateTB.Text / 100
                            Me.LineTotalTB.Text = QtyTB.Text * UnitPriceTB.Text - DiscountAmountTB.Text
                        ElseIf SellingPrice4CB.Checked = True Then
                            SQL.AddParam("@UnitPrice", SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice4"))
                            Me.UnitPriceTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice4")
                            Me.DiscountAmountTB.Text = Math.Round(QtyTB.Text * UnitPriceTB.Text) * DiscountRateTB.Text / 100
                            Me.LineTotalTB.Text = QtyTB.Text * UnitPriceTB.Text - DiscountAmountTB.Text
                        ElseIf SellingPrice5CB.Checked = True Then
                            SQL.AddParam("@UnitPrice", SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice5"))
                            Me.UnitPriceTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice5")
                            Me.DiscountAmountTB.Text = Math.Round(QtyTB.Text * UnitPriceTB.Text) * DiscountRateTB.Text / 100
                            Me.LineTotalTB.Text = QtyTB.Text * UnitPriceTB.Text - DiscountAmountTB.Text
                        End If
                        SQL.AddParam("@TaxRate1", Me.TaxRate1TB.Text)
                        SQL.AddParam("@TaxRate2", Me.TaxRate2TB.Text)
                        SQL.AddParam("@TaxRate3", Me.TaxRate3TB.Text)
                        SQL.AddParam("@ExtendedPrice", Me.QtyTB.Text * Me.UnitPriceTB.Text)
                        SQL.AddParam("@DiscountRate", SQL.DBDS.Tables(0).Rows(0).Item("DiscountRate"))
                        SQL.AddParam("@DiscountAmount", Me.DiscountAmountTB.Text)
                        SQL.AddParam("@ValidFrom", Me.ValidFromTB.Text)
                        SQL.AddParam("@ValidTo", Me.ValidToTB.Text)
                        SQL.AddParam("@InvoicedQuantity", Me.InvoicedQuantityTB.Text)
                        SQL.AddParam("@BackOrderYesNo", Me.BackOrderYesNoCB.Checked)
                        SQL.AddParam("@BackorderQuantity", Me.BackorderQuantityTB.Text)
                        SQL.AddParam("@TaxAmount1", (Me.QtyTB.Text * Me.UnitPriceTB.Text - DiscountAmountTB.Text) * TaxRate1TB.Text)
                        SQL.AddParam("@TaxAmount2", (Me.QtyTB.Text * Me.UnitPriceTB.Text - DiscountAmountTB.Text) * TaxRate2TB.Text)
                        SQL.AddParam("@TaxAmount3", (Me.QtyTB.Text * Me.UnitPriceTB.Text - DiscountAmountTB.Text) * TaxRate3TB.Text)
                        SQL.AddParam("@LineTotal", Me.QtyTB.Text * Me.UnitPriceTB.Text - DiscountAmountTB.Text)
                        SQL.AddParam("@DeliveryDate", Me.DeliveryDateExpectedTB.Text)
                        SQL.AddParam("@OrderPaid", 0)
                        SQL.ExecQuery("INSERT INTO Sales.OrderLines(OrderNo,ProductID,ProductName,Quantity,UnitPrice,ExtendedPrice,DiscountRate,DiscountAmount,ValidFrom,ValidTo,InvoicedQuantity,BackOrderYesNo, " &
                                      "BackOrderQuantity,LineTotal,DeliveryDate,TaxRate1,TaxRate2,TaxRate3,TaxAmount1,TaxAmount2,TaxAmount3,OrderPaid) " &
                                      "VALUES (@OrderNo,@ProductID,@ProductName,@Quantity,@UnitPrice,@ExtendedPrice,@DiscountRate,@DiscountAmount,@ValidFrom,@ValidTo,@InvoicedQuantity,@BackOrderYesNo, " &
                                      "@BackorderQuantity,@LineTotal,@DeliveryDate,@TaxRate1,@TaxRate2,@TaxRate3,@TaxAmount1,@TaxAmount2,@TaxAmount3,@OrderPaid) ", True)
                        RecordsCounted()
                        Me.QtyTB.Text = 1
                        If Me.RecordsCountTB.Text >= 1 Then
                            Dim i As Integer
                            i = OrderLinesDGV.CurrentRow.Index
                            Me.OrderLineIDTB.Text = OrderLinesDGV.Item(2, i).Value
                            Me.LastProductIDTB.Text = OrderLinesDGV.Item(3, i).Value
                        End If
                    End If
                    'If SQL.DBDS.Tables(0).Rows(0).Item(0) = 0 Then
                    'MsgBox("There is no product as requested!")
                    'Exit Sub
                    '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
    
        Public Sub FormatAll()
            Try
                Dim NumberExtendedPrice As Double = ExtendedPriceTB.Text
                Me.ExtendedPriceTB.Text = NumberExtendedPrice.ToString("$###,###,###.00")
                Dim NumberDiscountRate As Decimal = DiscountRateTB.Text
                Me.DiscountRateTB.Text = NumberDiscountRate.ToString("###.00000")
                Dim NumberDiscountAmount As Double = DiscountAmountTB.Text
                Me.DiscountAmountTB.Text = NumberDiscountAmount.ToString("$###,###,###.00")
                Dim NumberLineTotal As Double = LineTotalTB.Text
                Me.LineTotalTB.Text = NumberLineTotal.ToString("$###,###,###.00")
                OrderLinesDGV.Columns("UnitPrice").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                OrderLinesDGV.Columns("ExtendedPrice").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                OrderLinesDGV.Columns("DiscountRate").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                OrderLinesDGV.Columns("DiscountAmount").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                OrderLinesDGV.Columns("LineTotal").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                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 BtnLastAddedRow_Click(sender As Object, e As EventArgs) Handles BtnLastAddedRow.Click
            If OrderLinesDGV.Rows.Count >= 1 Then
                OrderLinesDGV.CurrentCell = Me.OrderLinesDGV.Rows(Me.OrderLinesDGV.Rows.Count - 1).Cells(0)
                OrderLinesDGV.Rows(Me.OrderLinesDGV.Rows.Count - 1).Selected = True
                OrderLineIDTB.Text = Me.OrderLinesDGV.CurrentRow.Cells("OrderLineID").Value.ToString()
                LastProductIDTB.Text = Me.OrderLinesDGV.CurrentRow.Cells("ProductID").Value.ToString()
                ProductDateSoldTB.Text = Me.OrderLinesDGV.CurrentRow.Cells("SellingDate").Value.ToString()
                ValidFromTB.Text = Me.OrderLinesDGV.CurrentRow.Cells("ValidFrom").Value.ToString()
                ValidToTB.Text = Me.OrderLinesDGV.CurrentRow.Cells("ValidTo").Value.ToString()
                Me.ProductCBX.Select()
            Else
                Exit Sub
            End If
        End Sub
    
    0 comments No comments