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

[euchan09]

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

Continue reading...