Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
See more:
Greetings all,

am trying to create a point of sale system using vb.net and sql database. so far am able to add items to the system and even sale. The challenge i have is that even if goods finish from the database, its still allowing selling. I would love to stop items being sold once they finish from the Database.

What I have tried:

Imports MySql.Data.MySqlClient
Public Class frmPos
    Dim totalprice As Double
    Dim totalCost As Double
    Dim getProdtoDelete As Integer
    Dim Firstnum As Decimal
    Dim secondnum As Decimal
    Dim Operations As Integer
    Dim Operator_Selector As Boolean = False
    Dim result As Integer
    Dim totStockOut As Double
    Dim totSales As Double
    Dim strMonthNo As String
    Dim y As Integer
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        PictureBox1.ImageLocation = My.Settings.Logo
        PicBarCode.BackgroundImage = Code128(txtSearch.Text, "A")

        Panel1.Height = GVReceipt.Height + 471

        GridSettings()

        GVReceipt.Columns(0).Name = "Item Description"
        GVReceipt.Columns(1).Name = "Qty"
        GVReceipt.Columns(2).Name = "@"
        GVReceipt.Columns(3).Name = "Amount"
        GVReceipt.Columns(0).Width = 120
        GVReceipt.Columns(1).Width = 50
        GVReceipt.Columns(2).Width = 50
        GVReceipt.Columns(3).Width = 100
    End Sub



    Private Sub resizeDGV()
        GVReceipt.Height = GVReceipt.ColumnHeadersHeight + GVReceipt.Rows.Cast(Of DataGridViewRow).Sum(Function(r) r.Height)

        Panel1.Height = GVReceipt.Height + 471
        GVReceipt.ClearSelection()

    End Sub

    Private Sub Calc()
        Dim total As Integer = 0
        For index As Integer = 0 To GVReceipt.RowCount - 1
            total += Convert.ToDouble(GVReceipt.Rows(index).Cells(3).Value.ToString)
        Next
        Label12.Text = total.ToString()
        Label34.Text = total.ToString()
    End Sub



    Private Sub GVReceipt_RowsAdded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsAddedEventArgs) Handles GVReceipt.RowsAdded
        resizeDGV()
    End Sub

    Private Sub GVReceipt_RowsRemoved(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles GVReceipt.RowsRemoved
        resizeDGV()
    End Sub

    Private Sub PrintDoc_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDoc.PrintPage
        Dim bm As New Bitmap(Me.Panel1.Width, Me.Panel1.Height)

        Panel1.DrawToBitmap(bm, New Rectangle(0, 0, Me.Panel1.Width, Me.Panel1.Height))

        e.Graphics.DrawImage(bm, 0, 0)

        Dim aPS As New PageSetupDialog
        aPS.Document = PrintDoc
    End Sub

    '   Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
    '       PrintDoc.PrinterSettings.PrinterName = My.Settings.Printer
    '       PrintDoc.Print()

    '       GVReceipt.Rows.Clear()
    '       GVProduct.Rows.Clear()

    '       Calc()
    '   End Sub

    Private Sub PrinterSetupToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrinterSetupToolStripMenuItem.Click
        frmPrinter.ShowDialog()
    End Sub

    Private Sub LogoSetupToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LogoSetupToolStripMenuItem.Click
        frmLogo.ShowDialog()
    End Sub
    '''start here
    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Private Sub GetItemInfo()
        Try
            sqL = "SELECT Barcode, Description, UnitPrice, ProductNo FROM product Where Description = '" & Val(txtSearch.Text) & "'"
            ConnDB()
            cmd = New MySqlCommand(sqL, conn)
            dr = cmd.ExecuteReader()

            If dr.Read = True Then
                productCode = dr("Barcode")
                itemDesc = dr("Description")
                itemPrice = dr("UnitPrice")
                itemNum = dr("ProductNo")
                itemDesc2 = dr("Description")
            End If
            txtSearch.Text = ""
        Catch ex As Exception
            '         MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub
    <CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2202:Do not dispose objects multiple times")>
    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Public Sub addtolist()
        Dim sql As String
        Dim cmd As New MySqlCommand
        Dim dr As MySqlDataReader

        '''''''''''''''''''''

        Try
            sql = "SELECT * FROM product where Barcode = '" & txtSearch.Text & "'"

            ConnDB()
            With cmd
                .CommandText = sql
                .Connection = conn
            End With
            dr = cmd.ExecuteReader
            While dr.Read()
                If txtSearch.Text = "" Then

                Else
                    ' Create the new row.
                    Dim price As Decimal = dr("UnitPrice")
                    Dim stockonhand As Decimal = dr("StocksOnHand")
                    Dim itemcode As String = dr("Barcode")
                    Dim itemdesc As String = dr("Description")
                    Dim itemDesc2 As String = dr("Description")

                    'add transactiondetail
                    itemNum = dr("ProductNo")
                    itemPrice = dr("UnitPrice")

                    Try
                        sql = "INSERT INTO transactiondetails(InvoiceNo, ProductNo, ItemPrice, Quantity, Discount, user) Values('" & lblInvoiceNo.Text & "', '" & itemNum & "', '" & itemPrice & "', '" & txtQuantity.Text & "', '" & txtdiscount.Text & "', '" & tsslUser.Text & "')"
                        ConnDB()
                        cmd = New MySqlCommand(sql, conn)
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        '                      MsgBox(ex.Message)
                    Finally
                        cmd.Dispose()
                        conn.Close()
                    End Try

                    totalprice = Val(txtQuantity.Text) * price

                    dgvItems.Rows.Add(txtQuantity.Text, itemcode, itemdesc, price, totalprice)
                    GVReceipt.Rows.Add(itemDesc2, txtQuantity.Text, price, totalprice)
                    GridSettings()
                    Calc()
                    'update stockonhand per item
                    'receipt datagrid'' '''''''''''''''''''

                    'end'''''''''''''''''''''''
                    UpdateDecreaseQuantity()
                    txtSearch.Clear()
                End If
            End While
        Catch ex As Exception
            '           MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try

    End Sub
    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        '      lbltime1.Text = Date.Now.ToString("hh:mm:ss")

        Label38.Text = Date.Now.ToString("hh:mm:ss")
        Label36.Text = Date.Now.ToString("dd/MM/yyyy")
    End Sub


    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        frmProduct.Show()
    End Sub

    Private Sub txtSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtSearch.KeyDown
        If txtSearch.Text = Nothing Then

            '''''''''''''''''''''''
        Else
            If e.KeyCode = Keys.Enter Then

                e.SuppressKeyPress = True
                '       txtdescription.Text = itemDesc
                txtQuantity.Enabled = True

                txtQuantity.Focus()

            End If
        End If
    End Sub

    Private Sub autocomplete()
        Try
            ConnDB()
            Dim dt As New DataTable
            Dim ds As New DataSet
            ds.Tables.Add(dt)

            Dim da As New MySqlDataAdapter("Select Barcode from product", conn)

            da.Fill(dt)
            Dim r As DataRow
            txtSearch.AutoCompleteCustomSource.Clear()
            For Each r In dt.Rows

                txtSearch.AutoCompleteCustomSource.Add(r.Item(0).ToString)
            Next
            conn.Close()

        Catch ex As Exception
            '            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub frmPos_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
        If (e.Control AndAlso (e.KeyCode = Keys.P)) Then
            ' When Control + P is pressed
            'button perfom click
            btnPayment.PerformClick()
        ElseIf (e.Control AndAlso (e.KeyCode = Keys.N)) Then
            btnNewTransacation.PerformClick()
        ElseIf (e.Control AndAlso (e.KeyCode = Keys.R)) Then
            btnRemove.PerformClick()
            '


            '
        ElseIf (e.Control AndAlso (e.KeyCode = Keys.Escape)) Then
            frmMain.ClosePosToolStripMenuItem.PerformClick()
        End If

    End Sub

    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Private Sub frmPos_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '   Me.WindowState = FormStartPosition.CenterScreen
        Me.WindowState = FormWindowState.Maximized

        Me.KeyPreview = True
        btnNewTransacation.PerformClick()
        getinvoiceNo()
        Timer1.Start()
        autocomplete()
        txttotalcost.Text = txtsubtotal.Text
        txtQuantity.Enabled = False
        txtrecieved.Enabled = False
        'txttotalcost.Text = FormatCurrency(Val(txttotalcost.Text))
        txtSearch.Select()
        change.txtChange.Text = "0.00"
        '      lblDate1.Text = Date.Now.ToString("MM/dd/yyyy")

        'Receipt Generation On Payment

        PictureBox1.ImageLocation = My.Settings.Logo
        PicBarCode.BackgroundImage = Code128(txtSearch.Text, "A")

        Panel1.Height = GVReceipt.Height + 471



        GVReceipt.Columns(0).Name = "Item Description"
        GVReceipt.Columns(1).Name = "Qty"
        GVReceipt.Columns(2).Name = "@"
        GVReceipt.Columns(3).Name = "Amount"
        GVReceipt.Columns(0).Width = 120
        GVReceipt.Columns(1).Width = 50
        GVReceipt.Columns(2).Width = 50
        GVReceipt.Columns(3).Width = 100
        'End of Line

        Try

            sqL = "SELECT ProductCode, P.Description, TDate, SUM(TD.Quantity) as totalQuantity, TD.ItemPrice FROM Product as P, Transactions as T, TransactionDetails as TD WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND  TDate LIKE '" & frmFilterDailySales.DateTimePicker1.Value.ToString("MM/dd/yyyy") & "' GROUP BY P.ProductNo, TDate ORDER By TDate"


            ConnDB()
            cmd = New MySqlCommand(sqL, conn)
            dr = cmd.ExecuteReader

            frmReportDailySales.dgw.Rows.Clear()
            totStockOut = 0.0
            totSales = 0.0
            y = 0
            Do While dr.Read = True
                frmReportDailySales.dgw.Rows.Add(dr("ProductCode"), dr("Description"), dr("TDate"), dr("totalQuantity"), Format(dr("ItemPrice"), "#,##0.00"), Format((dr("ItemPrice") * dr("TotalQuantity")), "#,##0.00"))
                y += 17
                totStockOut += dr("totalQuantity")
                totSales += (dr("ItemPrice") * dr("TotalQuantity"))
            Loop
            frmReportDailySales.dgw.Height += y
            frmReportDailySales.lblTotalStocksIn.Text = totStockOut
            frmMain.lblSales.Text = Format(totSales, "0.00")
            change.txtChange.Text = Format("0.00")
            'lblInvoiceNo.Text = Label41.Text
            Label41.Text = lblInvoiceNo.Text
            Panel3.Location = New Point(Me.Panel3.Location.X, Me.Panel3.Location.Y + y)
            ' InitializeComponent()
            ' frmMain.txtWordNumber.Text = UCase(SpellNumber(Val(lblSales.Text.Replace(",", ""))))
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            cmd.Dispose()
            conn.Close()
        End Try

    End Sub
    'receipt code
    Private Sub GridSettings()
        GVReceipt.ScrollBars = ScrollBars.None
        GVReceipt.ClearSelection()

        GVReceipt.RowHeadersVisible = False
        GVReceipt.ColumnCount = 4

        GVReceipt.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        'DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.SingleHorizontal
        GVReceipt.CellBorderStyle = DataGridViewCellBorderStyle.None

    End Sub

    'end here
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Me.Close()
        frmMain.ToolStrip1.Enabled = True
        frmMain.MenuStrip1.Enabled = True

    End Sub

    Private Sub txtQuantity_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtQuantity.KeyDown
        If txtQuantity.Text = Nothing Then
            ''''''''''''''''''''''''''''''''
        Else
            If e.KeyCode = Keys.Enter Then
                e.SuppressKeyPress = True

                addtolist()
                totalCost += totalprice

                txtsubtotal.Text = Format(totalCost, "0.00")
                txtrecieved.Enabled = True
                '  lblGrandTotal.Text = Format(totalCost, "#,##0.00")
                GetItemInfo()



                'end
                txtQuantity.Clear()
                txtSearch.Focus()
            End If
        End If
    End Sub

    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Private Sub UpdateDecreaseQuantity()
        Try
            sqL = "Update product SET StocksOnHand = stocksOnHand - " & Val(txtQuantity.Text) & " WHERE Barcode = '" & txtSearch.Text & "'"
            ConnDB()
            cmd = New MySqlCommand(sqL, conn)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            '            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            conn.Close()
        End Try

    End Sub

    Private Sub btnNewTransacation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewTransacation.Click
        dgvItems.Rows.Clear()
        txtsubtotal.Text = "0.00"
        '   lblGrandTotal.Text = "0.00"
        totalCost = 0
        txtrecieved.Enabled = False
        txtQuantity.Enabled = False
        txtSearch.Focus()
    End Sub

    Private Sub btnRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemove.Click
        If dgvItems.Rows.Count = 0 Then
            MsgBox("No Transaction", MsgBoxStyle.Exclamation, "Remove Item")
            txtSearch.Focus()
            Exit Sub
        Else
            If MsgBox("Are you sure you want to Delete?", MsgBoxStyle.YesNo, "Delete Item") = MsgBoxResult.Yes Then
                RemoveItem()

                txtSearch.Focus()
            Else
                Exit Sub
            End If

        End If
    End Sub


    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Private Sub UpdateIncreaseQuantity()
        Try
            sqL = "Update product SET StocksOnHand = stocksOnHand + '" & dgvItems.CurrentRow.Cells(0).Value & "' WHERE ProductNo = " & getProdtoDelete & ""
            ConnDB()
            cmd = New MySqlCommand(sqL, conn)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            '            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub
    Private Sub RemoveItem()
        txtsubtotal.Text = Format(txtsubtotal.Text - dgvItems.CurrentRow.Cells(4).Value, "0.00")
        GetProductIDToDelete()
        UpdateIncreaseQuantity()
        dgvItems.Rows.Remove(dgvItems.SelectedRows.Item(0))
        totalCost = txtsubtotal.Text
    End Sub
    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Private Sub GetProductIDToDelete()
        Try
            sqL = "SELECT ProductNo FROM product Where Barcode = '" & dgvItems.CurrentRow.Cells(1).Value & "'"
            ConnDB()
            cmd = New MySqlCommand(sqL, conn)
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read = True Then
                getProdtoDelete = dr("ProductNo")


                'delete transactiondetail
                Try
                    sqL = "DELETE FROM transactiondetails WHERE ProductNo = " & getProdtoDelete & " AND InvoiceNo = '" & lblInvoiceNo.Text & "'"
                    ConnDB()
                    cmd = New MySqlCommand(sqL, conn)
                    Dim i As Integer
                    i = cmd.ExecuteNonQuery
                    If i > 0 Then
                        'MsgBox("Item Deleted", MsgBoxStyle.Information, "Delete Item")
                    Else
                        ' MsgBox("Failed to Delete item", MsgBoxStyle.Critical, "Delete Item")
                    End If
                Catch ex As Exception
                    '                   MsgBox(ex.Message)
                End Try
            End If
        Catch ex As Exception
            '            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub

    Private Sub btnPayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPayment.Click
        '       frmpayment.ShowDialog()
        'frmPrintReceipt.ShowDialog()
        txtrecieved.Focus()

    End Sub

    Private Sub bn1_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "1"
        Else
            txtSearch.Text += "1"
        End If
    End Sub

    Private Sub bn2_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "2"
        Else
            txtSearch.Text += "2"
        End If
    End Sub

    Private Sub bn3_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "3"
        Else
            txtSearch.Text += "3"
        End If
    End Sub

    Private Sub bn4_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "4"
        Else
            txtSearch.Text += "4"
        End If
    End Sub

    Private Sub bn5_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "5"
        Else
            txtSearch.Text += "5"
        End If
    End Sub

    Private Sub bn6_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "6"
        Else
            txtSearch.Text += "6"
        End If
    End Sub

    Private Sub bn7_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "7"
        Else
            txtSearch.Text += "7"
        End If
    End Sub

    Private Sub bn8_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "8"
        Else
            txtSearch.Text += "8"
        End If
    End Sub

    Private Sub bn9_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "9"
        Else
            txtSearch.Text += "9"
        End If
    End Sub

    Private Sub bn0_Click(sender As Object, e As EventArgs)
        If txtSearch.Text <> "0" Then
            txtSearch.Text += "0"
        Else
            txtSearch.Text += "0"
        End If
    End Sub


    Private Sub lblTotalCost_TextChanged(sender As Object, e As EventArgs) Handles txtsubtotal.TextChanged
        Try
            txtSubtotal1.Text = txtsubtotal.Text
            txttotalcost.Text = Val(txtsubtotal.Text) - Val(txtdiscount.Text)
            'retotal.Text = "0.00"
            ' retotal.Text = txttotalcost.Text
            totaldue = txttotalcost.Text.Replace(",", "")
            Format(totaldue, "0.00")
            'txtsubtotal.Text = Label12.Text

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        If CheckBox1.Checked = True Then
            txtdiscount.Text = Val(txtSubtotal1.Text) * 0.1
            txttotalcost.Text = Val(txtsubtotal.Text) - Val(txtdiscount.Text) - Val(txtTax.Text)

            txtsubtotal.Text = Val((txtSubtotal1.Text) + Val(txtTax.Text)) - Val(txtdiscount)
            discount = txtdiscount.Text.Replace(",", "")
            Format(discount, "0.00")
        Else
            txtdiscount.Text = "0.00"
            txttotalcost.Text = Val(txtsubtotal.Text)
            totaldue = txttotalcost.Text.Replace(",", "")
            Format(totaldue, "0.00")
            discount = txtdiscount.Text.Replace(",", "")
            Format(discount, "0.00")
        End If
    End Sub

    Private Sub CheckBox2_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox2.CheckedChanged
        Try
            If CheckBox2.Checked = True Then
                txtTax.Text = Val(txtSubtotal1.Text) * 0.16
                txttotalcost.Text = Val(txtsubtotal.Text) - Val(txtdiscount.Text) - Val(txtTax.Text)
                'calculating value payable
                txtsubtotal.Text = Val((txtSubtotal1.Text) + Val(txtTax.Text)) - Val(txtdiscount.Text)
                vat = txtTax.Text.Replace(",", "")
                ' vat = Label35.Text.Replace(",", "")
                Format(vat, "0.00")
            Else
                txtTax.Text = "0.00"
                txttotalcost.Text = Val(txtsubtotal.Text)
                totaldue = txttotalcost.Text.Replace(",", "")
                Format(totaldue, "0.00")
                vat = txtTax.Text.Replace(",", "")
                ' vat = Label35.Text.Replace(",", "")
                Format(vat, "0.00")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
    Private Sub txtrecieved_KeyDown(sender As Object, e As KeyEventArgs) Handles txtrecieved.KeyDown

        If e.KeyCode = Keys.Enter Then
            e.SuppressKeyPress = True
            change.ShowDialog()

            Try
                ConnDB()

                Dim cmd As New MySqlCommand
                If e.KeyCode = Keys.Enter Then
                    e.SuppressKeyPress = True
                    sqL = "INSERT INTO transactions (InvoiceNo, TDate, TTime, NonVatTotal, VatAmount, TotalAmount, StaffID) VALUES ('" & lblInvoiceNo.Text & "' , '" & Date.Now.ToString("MM/dd/yyyy") & "' , '" & frmMain.lblTimer.Text & "' , '" & txtSubtotal1.Text & "' , '" & txtTax.Text & "' ,'" & txttotalcost.Text & "' , '" & frmMain.tsslUser.Text & "')"
                    ' sqL = "INSERT INTO payment (InvoiceNo, CashTendered, PChange, Username, Totalamount, trdate) VALUES ('" & lblInvoiceNo.Text & "' , '" & txtrecieved.Text & "' , '" & change.txtChange.Text & "', '" & frmMain.tsslUser.Text & "', '" & txtsubtotal.Text & "', '" & frmMain.lblDate.Text & "')"

                Else

                    sqL = "INSERT INTO payment (InvoiceNo, CashTendered, PChange) VALUES ('" & lblInvoiceNo.Text & "' , '" & txtrecieved.Text & "' , '" & change.txtChange.Text & "')"
                End If
                With cmd
                    .CommandText = sqL
                    .Connection = conn
                End With
                result = cmd.ExecuteNonQuery
                If result > 0 Then
                    '''''printing receipt

                    PrintDoc.PrinterSettings.PrinterName = My.Settings.Printer
                    PrintDoc.Print()

                    GVReceipt.Rows.Clear()
                    GVProduct.Rows.Clear()

                    Calc()



                    '''''''''''''''''''
                    MsgBox("Thank you for transacting!")
                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''         frmPrintReceipt.ShowDialog()
                    ' frmPrintReceipt.ShowDialog()
                    'change.txtChange = ""
                    txtrecieved.Clear()
                    txttotalcost.Clear()
                    GVReceipt.Rows.Clear()
                    ' Label41.Text = ""
                    '   lblInvoiceNo.Text = ""
                    getinvoiceNo()
                    btnNewTransacation.PerformClick()
                    conn.Close()
                    '  '''''''''''''''' refresh form so all the textboxes are upto date ''''''''''''
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    frmPos_Load(Me, New System.EventArgs)
                    txtSearch.Focus()
                Else
                    MsgBox("Transaction not Saved!")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Private Sub txtrecieved_TextChanged(sender As Object, e As EventArgs) Handles txtrecieved.TextChanged

        '  change.txtChange.Text = Val(Label23.Text)

        ' txtrecieved.Text = Label15.Text
        Label15.Text = txtrecieved.Text
        change.txtChange.Text = Val(txtrecieved.Text) - Val(txttotalcost.Text)
        ' Label23.Text = Val(Label12.Text) - Val(Label15.Text)
        Label23.Text = Val(Label15.Text) - Val(Label12.Text)
        ' Label1.Text = Label34.Text
        If Val(txtrecieved.Text) < Val(txttotalcost.Text) Then



        End If
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        BackupModule.ShowDialog()
    End Sub


End Class
Posted
Updated 19-Jun-20 5:11am
Comments
CHill60 19-Jun-20 6:44am
   
No-one is going to read through all of that code. Edit your question (using the Improve Question link when you hover over it) and remove anything that isn't relevant to how much stock is left on the database or how you sell it.
A couple of other points before someone posts a non-answer telling you about it - your code is vulnerable to SQL Injection. Never concatenate strings to create SQL queries - use Parameterized Queries
EDIT: Here are some resources about SQL Injection SQL injection - Wikipedia[^]
SQL Injection | OWASP[^]

EDIT 2 - I've just spotted
<CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")>
Don't be silly - fix the problem. Ignoring warnings will only lead to heartache later
Member 13600825 19-Jun-20 6:49am
   
thanx chill, i will improve on my mistakes, kindly help.
Maciej Los 19-Jun-20 6:44am
   
I would love to stop items being sold once they finish from the Database
So, before you start selling specific item, check if it's available.
0x01AA 19-Jun-20 6:47am
   
Optional: Increase delivery time :-)
Maciej Los 19-Jun-20 7:14am
   
:laugh:
F-ES Sitecore 19-Jun-20 7:12am
   
Check if the quantity is more than zero before you process the sale, or don't give the option to buy if it is zero. As others have commented, there is too much code there for anyone to understand how it works, cut it back to just the relevant bits.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Not a solution, but a problem you have.
Instead of suppressing serious warnings about security vulnerabilities, don't write code that has them. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
   
v2
Comments
Member 13600825 23-Jun-20 7:52am
   
wow well taken care of, thanks for the above suggestions. guess i was just being lazy at it.
OriginalGriff 23-Jun-20 8:29am
   
It's a whole lot lazier to not have to sort out the mess SQL Injection can cause! :laugh:

You are speaking to a world-class lazy person, here ... :D
Rate this:
Please Sign up or sign in to vote.

Solution 2

Here is the fix for the first instance of SQL Injection Vulnerability
sqL = "SELECT Barcode, Description, UnitPrice, ProductNo FROM product Where Description = @Description"
ConnDB()
cmd = New MySqlCommand(sqL, conn)
cmd.Parameters.AddWithValue("@Description", Val(txtSearch.Text))

As for the issue of selling more than what is on hand... what I would do would be grab the StocksOnHand with your SELECT statements that are being used to populate your item displays and don't allow purchases with higher quantities than what you have on hand.
"SELECT Barcode, Description, UnitPrice, ProductNo, StocksOnHand FROM product..."
   
Comments
Member 13600825 23-Jun-20 7:47am
   
hi, how would you advise i archive that?
Rate this:
Please Sign up or sign in to vote.

Solution 3

Quote:
The challenge i have is that even if goods finish from the database, its still allowing selling. I would love to stop items being sold once they finish from the Database.

From this sentence, and without even reading your code, you have about 3 solutions:
- You can add a field (goods table) saying if an item is in sale or not, and check it every time you do a sale. problem, a lot of manual house keeping.

- Keep track of stock of each goods. In goods table, you have a field showing number in stock, and every operation (input or output) will update stock.
A Goods is finished when there is not stock, you have to check if there is enough stock before a sale.
Every time you receive new goods, stock rise, every sale reduce stock.

- Another approach is that most shops are self service and when a customer show to the desk with goods, chances are that they in stock and thus not finished, even is database says otherwise.

sql = "INSERT INTO transactiondetails(InvoiceNo, ProductNo, ItemPrice, Quantity, Discount, user) Values('" & lblInvoiceNo.Text & "', '" & itemNum & "', '" & itemPrice & "', '" & txtQuantity.Text & "', '" & txtdiscount.Text & "', '" & tsslUser.Text & "')"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

Another problem you are going to encounter is in that set of Subs to handle the Click events for bn1, bn2, ... ,bn9, bn0
Private Sub bn1_Click(sender As Object, e As EventArgs)
    If txtSearch.Text <> "0" Then
        txtSearch.Text += "1"
    Else
        txtSearch.Text += "1"
    End If
End Sub
You are doing exactly the same thing whether the textbox contains "0" or not. That code is identical in behaviour to
Private Sub bn1_Click(sender As Object, e As EventArgs)
    txtSearch.Text += "1"
End Sub
To make matters worse you are going to have to make that fix 10 times - once per button click.

When you see yourself repeating code like that, where perhaps only 1 or 2 things change between each repetition, ask yourself if you could put that code into another subroutine or function.

For example the 70 lines of code that handle the the button clicks could be reduced to 37 - i.e. nearly halved - by doing something like this
Private Sub DealWithButtonClick(buttonNumber As String)
    If txtSearch.Text <> "0" Then
        txtSearch.Text += buttonNumber
    Else
        txtSearch.Text += buttonNumber
    End If
End Sub
		
Private Sub bn1_Click(sender As Object, e As EventArgs)
    DealWithButtonClick "1"
End Sub

. . .

Private Sub bn9_Click(sender As Object, e As EventArgs)
    DealWithButtonClick "9"
End Sub

Private Sub bn0_Click(sender As Object, e As EventArgs)
    DealWithButtonClick "0"
End Sub
It's not just about reducing the "bloat" in your code, although it will make it a bit easier to read, it means you only have to fix that but once - and that has to be a good thing right?
Now I'm looking at my code and thinking, there is still a repeating pattern there, is there anything more I can do?
Well, I could use the Tag property [^] of each of the buttons - I can either enter that at design time or add it later in code
bn1.Tag = "1"
bn2.Tag = "2" 
etc etc 
and make all of the buttons use exactly the same click event like this
Private Sub AButton_Click(sender As Object, e As EventArgs) _
        Handles bn1.Click, bn2.Click, bn3.Click, bn4.Click, bn5.Click, bn6.Click, bn7.Click, bn8.Click, bn9.Click, bn0.Click

    Dim TheButton = DirectCast(sender, Button)

	If txtSearch.Text <> "0" Then
		txtSearch.Text += TheButton.Tag.ToString
	Else
		txtSearch.Text += TheButton.Tag.ToString
	End If    
End Sub
I can even take it a step further and use the name of the button to determine what to do ...
Private Sub AButton_Click(sender As Object, e As EventArgs) _
        Handles bn1.Click, bn2.Click, bn3.Click, bn4.Click, bn5.Click, bn6.Click, bn7.Click, bn8.Click, bn9.Click, bn0.Click

    Dim TheButton = DirectCast(sender, Button)

	If txtSearch.Text <> "0" Then
		txtSearch.Text += TheButton.Name.Substring(TheButton.Name.Length - 1) 
	Else
		txtSearch.Text += TheButton.Name.Substring(TheButton.Name.Length - 1)
	End If    
End Sub
Warning - this last one will only work for single digit names like bn1, bn2 ... bn9,bn0. It won't work as it stands for bn10, bn11, etc
But hey, look at that. I've gone from 70 lines of code to 8, the code is still easy to read and I can fix that bug in one place and one place only.

What I did is called "Refactoring" and it's a good tool to have in your arsenal.

Here are a few articles on the topic (some are in C# but the principles are the same)
C# Bad Practices: Learn How to Make Good Code by Using Examples of Bad Code[^]
Code Metrics, Code Smells, and Refactoring in Practice[^]
Refactoring source code in Visual Studio Code[^]
There are several books on the subject too - some of which are available via Google Books https://www.amazon.co.uk/s?k=refactoring&ref=nb_sb_noss_1[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100