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 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,

VB.NET cmd.CommandText is not working properly with sql update

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"

​x
 
    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")

 
        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")​

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.

Advertisement

Answer