Share via

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,668 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  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