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.