This is my subtract button, it will just subtract the qty in my database but the math logic is wrong, for example, If I said 12-2 it updates the qty to 6
If con.State = ConnectionState.Open Then con.Close() End If con.Open() Try If con.State = ConnectionState.Open Then con.Close() End If con.Open() cmd = con.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "UPDATE tablekongbago SET qty = qty + 2 WHERE Id = 2" cmd.CommandType = "UPDATE tablekongbago SET qty = (qty - 2) WHERE Id = 2" cmd.CommandText = "update tablekongbago set qty = qty - " + Convert.ToInt32(qtybox1.Text) + "WHERE Id = 2" cmd.CommandText = "UPDATE tablekongbago SET qty = qty - 2 WHERE Id = " & 2 & "" cmd.CommandText = "UPDATE tablekongbago SET qty = qty - " + Convert.ToInt32(2) + " WHERE Id = 2"
Any of this 5 “cmd.CommandText is not working properly, it add and subtracts but the qty it updates to the database is wrong, it produces a wrong output, e.g (12 – 2 = 6)
cmd.ExecuteNonQuery() Dim dt As New DataTable() Dim da As New SqlDataAdapter(cmd) da.Fill(dt) Dim dr As SqlClient.SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection) While dr.Read qty = dr.GetInt32(3).ToString() End While Catch ex As Exception End Try MessageBox.Show("Subtracted Successfully")
Advertisement
Answer
You first call ExecuteNonQuery
on the command, which will subtract 2 from the current value. You next create a data adapter with the same command in its SelectCommand
and then call Fill
, which will execute the same command again and subtract another 2. Finally, you call ExecuteReader
on the same command again, which will subtract another 2. Tell me, what’s (12 – 2 – 2 – 2)? The command is working exactly as it should. You’re just executing two extra times for no reason.
Why would you be using a data adapter and a data reader in that situation at all? There is no SELECT
statement anywhere and both Fill
and ExecuteReader
are intended for executing queries, i.e. SELECT
statements.