Incrementing a value of a field in the table by button click

Question

I am currently working on a project in which i have implemented voting.

now, in my database I have a table named options, having 2 columns optionId(bigint) and count(bigint), default value of the count is 0 in all rows. I want to increment a specific count field with the button click.

For eg, I have to increment the value of the count having optionId = (some number passed) (for voting a specific option)

I am using a stored procedure to do this, but unfortunately all the rows are being incremented,

I want a single row to be updated only

this is the code which triggers on button click

Int32 optionid = Convert.ToInt32(RadioButtonList1.SelectedValue);
        using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ManagerConnectionString"].ConnectionString))
        {
            Conn.Open();
            incrementTheVote.incrementVote(optionid);
        }

this is the definiton of the class(incrementTheVote) in which I am calling the stored procedure

public class incrementTheVote
{
    public static int incrementVote(int optionid)
    {
        int rowsaffected = 0;
        using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ManagerConnectionString"].ConnectionString))
        {
            Conn.Open();
            SqlCommand cmd = new SqlCommand("incrementVote", Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@optionid", SqlDbType.Int).Value = optionid;
            rowsaffected = cmd.ExecuteNonQuery();
        }
        return rowsaffected;
    }
}

This is the stored procedure

    ALTER PROCEDURE incrementVote
        (
        @optionid bigint
        )
    AS
    BEGIN TRANSACTION
    SELECT votes from options where optionid = @optionid
    UPDATE options
    SET votes = votes + 1 
    COMMIT TRANSACTION

Cheruvian Cheruvian · Accepted Answer · 2014-07-07T17:15:52

You were so close! You need to add a where clause to your update statement.
Also, updates can reference their old values, so there is no need to create the intermediary variable @votes.

ALTER PROCEDURE incrementVote
    (
    @optionid bigint
    )
AS
BEGIN TRANSACTION

UPDATE options
SET votes = votes + 1 
Where optionid = @optionid

COMMIT TRANSACTION

Incrementing a value of a field in the table by button click

1 Answers