none
how to compare my database column value to textbox if its higher or lower than to each other vb.net RRS feed

  • Question

  • i have a problem on my code whenever the total cost is higher than the card balance the value in my database becomes negative. how will i check the card load balance in my database if sufficient or not first before inserting the values in my database.  this is my code: 

     Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            If TextBox1.TextLength = 10 Then
    
                Dim command As New MySqlCommand("select * from tbl_member where CardNO = '" & TextBox1.Text & "'", con)
                Dim adapter As New MySqlDataAdapter(command)
                Dim table As New DataTable()
                adapter.Fill(table)
                If table.Rows.Count > 0 Then
                    Label1.Text = table.Rows(0)(1).ToString()
                    Label2.Text = table.Rows(0)(2).ToString()
                    Label3.Text = table.Rows(0)(3).ToString()
                    Label5.Text = "---"
                    If Val(table.Columns(6).ToString) <= Val(CashRegister.Label2.Text) Then    <------ **the problem is here when i change it to > it always says insufficient balance even the balance id enough
                        Dim query As New MySqlCommand("update tbl_member set CardLoad = CardLoad -" & Val(CashRegister.Label2.Text) & " WHERE CardNO = '" & TextBox1.Text & "'", con)
                        Dim query1 As New MySqlCommand("Update  tbl_member SET reward = reward +  ('" & CashRegister.Label2.Text & "'/40) WHERE CardNO = '" & TextBox1.Text & "'", con)
                        con.Open()
                        query1.ExecuteNonQuery()
                        query.ExecuteNonQuery()
                        con.Close()
                        For Each row As DataGridViewRow In CashRegister.BunifuCustomDataGrid2.Rows
    
                            Dim cmd As New MySqlCommand("INSERT INTO tbl_records (Name, LastName, Item, Price, total,EmplyoyeeID,qty,Date) VALUES ('" & Label1.Text & "' , '" & Label2.Text & "' ,@item, @price, @total,'" & Label3.Text & "'  , @qty,'" & Date.Today & "')", con)
                            cmd.Parameters.AddWithValue("@item", row.Cells("item").Value)
                            cmd.Parameters.AddWithValue("@price", row.Cells("price").Value)
                            cmd.Parameters.AddWithValue("@total", row.Cells("total").Value)
                            cmd.Parameters.AddWithValue("@qty", row.Cells("qty").Value)
                            con.Open()
                            cmd.ExecuteNonQuery()
                            con.Close()
    
                        Next
                    Else
                        Label5.Text = "isufficient balance"
                    End If
                
                Else
                    Label5.Text = "Card Not Registered"
                    Label1.Text = "-"
                    Label2.Text = "-"
                    Label3.Text = "-"
                End If
    
    
                TextBox1.Text = ""
            End If
        End Sub

    please help me i need an urgent answer. thanks


    • Edited by euchan09 Thursday, August 23, 2018 6:15 AM wrong spelling
    Thursday, August 23, 2018 6:13 AM

Answers

  • Hi,

    try to modify the code

    If Val(table.Rows(TextBox1.Text)("CardLoad").ToString()) <= Val(CashRegister.Label2.Text) Then    <------ **the problem is here when i change it to > it always says insufficient balance even the balance id enough
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by euchan09 Thursday, August 23, 2018 10:04 AM
    Thursday, August 23, 2018 9:50 AM

All replies

  • Hi,

    What is table.Columns(6) in the code below? Is this correct?

    Val converts a string to a number, and the result of the unconvertible is 0.

        If Val(table.Columns(6).ToString) <= Val(CashRegister.Label2.Text) Then    <------ **the problem is here when i change it to > it always says insufficient balance even the balance id enough
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 23, 2018 7:04 AM
  • table.Columns(6) is the column from my database where the card load is.

    Thursday, August 23, 2018 7:26 AM
  • Hi,

    table.Columns(6).ToString should be a column name,What do you mean by comparing column names with CashRegister.Label2.Text?

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 23, 2018 8:16 AM
  • the cashregister.label2.text (total cost ) value will deduct to the table.Columns("CardLoad").ToString (column for Card Load) value. So if cashregister.label2.text value is higher than table.Columns("CardLoad").ToString the column value will become negative in the database. what i mean in comparing is to check if the CardLoad (column from table) value is sufficient or insufficient for the total cost which is cashregister.label2.text (total cost ). please help me how will I prevent the Cardload column to become negative.
    • Edited by euchan09 Thursday, August 23, 2018 9:19 AM
    Thursday, August 23, 2018 9:02 AM
  • Hi,

    try to modify the code

    If Val(table.Rows(TextBox1.Text)("CardLoad").ToString()) <= Val(CashRegister.Label2.Text) Then    <------ **the problem is here when i change it to > it always says insufficient balance even the balance id enough
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by euchan09 Thursday, August 23, 2018 10:04 AM
    Thursday, August 23, 2018 9:50 AM
  • it works! thank you so much! <3

    I just made my code like this

     If Val(table.Rows(0)("CardLoad").ToString) >= Val(CashRegister.Label2.Text) Then


    Thursday, August 23, 2018 10:03 AM