lundi 28 décembre 2020

Problem on adding quantity in existing item in DataGridView - vb.net

I am creating a shopping system as my university mini project, where the user can enter the barcode of an item they wanted and the entered item will be added to the grid on the right and also as a new item to the grid on the left. Each item has a unique barcode and can have the same product code. When the user enters an item with the same product code then quantity will be added on the grid on the left. I am having a problem where when the second item quantity need to be added, the system is detecting it as a new item instead of adding the quantity

eg: Example of products and items

    Public Sub addRecordTo2ndDGV()
    Try
        dbconnection()
        sql = "SELECT * FROM items_database WHERE Item_Barcode = @ItemBarcode;"
        cmd = New MySqlCommand
        With cmd
            .Connection = conn
            .CommandText = sql
            .Parameters.Clear()
            .Parameters.AddWithValue("@ItemBarcode", formUser.barcodeTB.Text)
        End With
        da = New MySqlDataAdapter
        dt = New DataTable
        da.SelectCommand = cmd
        da.Fill(dt)
        If dt.Rows.Count > 0 Then
            Dim ItemProductCode, ItemBarcode As String
            Dim repeated As Boolean = False
            ItemProductCode = dt.Rows(0).Item(1)
            ItemBarcode = dt.Rows(0).Item(2)
            formUser.itemproductcodeTB.Text = ItemProductCode
            formUser.itembarcodetb.Text = ItemBarcode
            
            For Each row2 As DataGridViewRow In formUser.ItemBarcodeDGV.Rows
                If Convert.ToString(row2.Cells(1).Value) = formUser.itembarcodetb.Text AndAlso Convert.ToString(row2.Cells(0).Value) = formUser.itemproductcodeTB.Text Then
                    MsgBox("QUANTITY WILL MINUS")
                    minusQuantity()
                    formUser.ItemBarcodeDGV.Rows.Remove(row2)
                    MsgBox("QUANTITY HAS MINUS")
                    Exit For

                ElseIf Convert.ToString(row2.Cells(1).Value) <> formUser.itembarcodetb.Text AndAlso Convert.ToString(row2.Cells(0).Value) = formUser.itemproductcodeTB.Text Then
                    MsgBox("QUANTITY WILL ADD")
                    formUser.ItemBarcodeDGV.Rows.Add(New String() {formUser.itemproductcodeTB.Text, formUser.itembarcodetb.Text})
                    addQuantity()
                    MsgBox("QUANTITY HAS ADDED")
                    Exit For

                ElseIf Convert.ToString(row2.Cells(1).Value) <> formUser.itembarcodetb.Text AndAlso Convert.ToString(row2.Cells(0).Value) <> formUser.itemproductcodeTB.Text Then
                    MsgBox("NEW ITEM")
                    formUser.ItemBarcodeDGV.Rows.Add(New String() {formUser.itemproductcodeTB.Text, formUser.itembarcodetb.Text})
                    addRecord()
                    MsgBox("REGISTERED SUCCESSFULL")
                    Exit For
                End If
                Exit For
            Next
        Else
            MsgBox("Barcode not registered!")
            formUser.barcodeTB.Text = ""
            formUser.barcodeTB.Focus()
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conn.Close()
        da.Dispose()
        formUser.barcodeTB.Text = ""
        clearUserForm()
        retrieveSubTotal()
    End Try
End Sub

Public Sub addRecord()
    If formUser.itemproductcodeTB.Text = "" Then
        MsgBox("Scan a barcode")
    Else
        Try
            dbconnection()
            sql = "SELECT * FROM products_database WHERE Product_Code = @ProductCode;"
            cmd = New MySqlCommand
            With cmd
                .Connection = conn
                .CommandText = sql
                .Parameters.Clear()
                .Parameters.AddWithValue("@ProductCode", formUser.itemproductcodeTB.Text)
            End With
            da = New MySqlDataAdapter
            dt = New DataTable
            da.SelectCommand = cmd
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                Dim productcode, itemdescription, unitprice As String
                Dim repeated As Boolean = False
                productcode = dt.Rows(0).Item(1)
                itemdescription = dt.Rows(0).Item(3)
                unitprice = dt.Rows(0).Item(4)
                formUser.productcodeTB.Text = productcode
                formUser.itemdescriptionTB.Text = itemdescription
                formUser.unitpriceTB.Text = unitprice
                formUser.unitQuantityTB.Text = "1"

                For Each row As DataGridViewRow In formUser.ProductAddToCartDGV.Rows
                    If Convert.ToString(row.Cells(0).Value) = formUser.productcodeTB.Text AndAlso Convert.ToString(row.Cells(2).Value) = formUser.unitpriceTB.Text Then
                        row.Cells(4).Value = Convert.ToString(row.Cells(3).Value * row.Cells(2).Value)
                    End If
                Next
                formUser.ProductAddToCartDGV.Rows.Add(New String() {formUser.productcodeTB.Text, formUser.itemdescriptionTB.Text, formUser.unitpriceTB.Text, formUser.unitQuantityTB.Text, formUser.unitpriceTB.Text})
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
            da.Dispose()
        End Try
    End If
End Sub

Public Sub addQuantity()
    If formUser.itemproductcodeTB.Text = "" Then
        MsgBox("Scan a barcode")
    Else
        Try
            dbconnection()
            sql = "SELECT * FROM products_database WHERE Product_Code = @ProductCode;"
            cmd = New MySqlCommand
            With cmd
                .Connection = conn
                .CommandText = sql
                .Parameters.Clear()
                .Parameters.AddWithValue("@ProductCode", formUser.itemproductcodeTB.Text)
            End With
            da = New MySqlDataAdapter
            dt = New DataTable
            da.SelectCommand = cmd
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                Dim productcode, itemdescription, unitprice As String
                Dim repeated As Boolean = False
                productcode = dt.Rows(0).Item(1)
                itemdescription = dt.Rows(0).Item(3)
                unitprice = dt.Rows(0).Item(4)

                formUser.productcodeTB.Text = productcode
                formUser.itemdescriptionTB.Text = itemdescription
                formUser.unitpriceTB.Text = unitprice
                formUser.unitQuantityTB.Text = "1"

                For Each row As DataGridViewRow In formUser.ProductAddToCartDGV.Rows
                    If Convert.ToString(row.Cells(0).Value) = formUser.productcodeTB.Text AndAlso Convert.ToString(row.Cells(2).Value) = formUser.unitpriceTB.Text Then
                        row.Cells(3).Value = Convert.ToString(Convert.ToInt16(row.Cells(3).Value + 1))
                        row.Cells(4).Value = Convert.ToString(row.Cells(3).Value * row.Cells(2).Value)
                    End If
                Next
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
            da.Dispose()
        End Try
    End If
End Sub

Public Sub minusQuantity()
    If formUser.itemproductcodeTB.Text = "" Then
        MsgBox("Scan a barcode")
    Else
        Try
            dbconnection()
            sql = "SELECT * FROM products_database WHERE Product_Code = @ProductCode;"
            cmd = New MySqlCommand
            With cmd
                .Connection = conn
                .CommandText = sql
                .Parameters.Clear()
                .Parameters.AddWithValue("@ProductCode", formUser.itemproductcodeTB.Text)
            End With
            da = New MySqlDataAdapter
            dt = New DataTable
            da.SelectCommand = cmd
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                Dim productcode, itemdescription, unitprice As String
                Dim repeated As Boolean = False
                productcode = dt.Rows(0).Item(1)
                itemdescription = dt.Rows(0).Item(3)
                unitprice = dt.Rows(0).Item(4)

                formUser.productcodeTB.Text = productcode
                formUser.itemdescriptionTB.Text = itemdescription
                formUser.unitpriceTB.Text = unitprice
                formUser.unitQuantityTB.Text = "1"

                For Each row As DataGridViewRow In formUser.ProductAddToCartDGV.Rows
                    If Convert.ToString(row.Cells(0).Value) = formUser.productcodeTB.Text AndAlso Convert.ToString(row.Cells(2).Value) = formUser.unitpriceTB.Text Then
                        row.Cells(3).Value = Convert.ToString(Convert.ToInt16(row.Cells(3).Value - 1))
                        row.Cells(4).Value = Convert.ToString(row.Cells(3).Value * row.Cells(2).Value)
                    End If
                Next
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
            da.Dispose()
        End Try
    End If
End Sub

Here is the output image

Aucun commentaire:

Enregistrer un commentaire