how to increment an integer in sql c#

Question

I am making a voting system. I have a problem adding a count(increment each time the candidate is voted) to each candidate that they voted.

My Vote Button code is :

    sc.Open();
        try
        {
            cmd = new SqlCommand("UPDATE TableVote SET Vcount=Vcount+1 WHERE id=@count", sc);

            cmd.Parameters.AddWithValue("@count", TxtPresID.Text);

            int res = cmd.ExecuteNonQuery();
            if (res > 0)
            {
                MessageBox.Show(TxtPresID.Text.ToString() + " Saved!");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            sc.Close();
        }

and my table is

my sql

the result of Vcount is just NULL..

null

What am i doing wrong? or is my Sql statement correct?

Don't allow NULL at all. Something that doesn't have any votes against it would have a vote count of 0, not null! — Arran
– Arran, Commented Oct 28, 2013 at 16:00
What "result" do you mean - in the table, or are you trying to pull the new vote count back to the user? — D Stanley
– D Stanley, Commented Oct 28, 2013 at 16:00

unlimit · Accepted Answer · 2013-10-28 16:01:41Z

4

Looks like your Vcount is NULL. As others have pointed out, you should make that column default to 0 or you could do this

UPDATE TableVote SET Vcount=ISNULL(Vcount, 0) + 1 WHERE id=@count

answered Oct 28, 2013 at 16:01

unlimit

3,7622 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kishore Kumar · Accepted Answer · 2013-10-28 18:22:02Z

3

Set the default Value of Vcount to be 0. It shouldn't be a null column.

Here is the query

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

ALTER TABLE TableVote 
ADD Vcount int NOT NULL 
CONSTRAINT  tablevote_vcount_default DEFAULT 0

Another option is to check, whether Vcount is NULL

UPDATE TableVote SET Vcount=ISNULL(Vcount, 0) + 1 WHERE id=@count

edited Oct 28, 2013 at 18:22

answered Oct 28, 2013 at 16:01

Kishore Kumar

12.9k27 gold badges99 silver badges158 bronze badges

1 Comment

Lyndon Broz Tonelete Over a year ago

Column names in each table must be unique. Column name 'Vcount' in table 'TableVote' is specified more than once.

Nzall · Accepted Answer · 2013-10-28 16:04:10Z

3

You shouldn't be updating the count row every time. if you have 2 votes incoming at the same time, it might have unpredictable consequences.

A better way would be to have a table "votes" and then insert a row for every vote, including the candidate ID, the voter ID and the date of vote. also means you can have historical data for different voters, and that you can easily change votes if needed.

answered Oct 28, 2013 at 16:04

Nzall

3,5855 gold badges36 silver badges66 bronze badges

2 Comments

crthompson Over a year ago

This is a very good point, so I'll give it a +1, but it does not answer the question.

unlimit Over a year ago

It does not answer the question, but I think it is a very valid point. Nice catch. +1

Cyril Gandon · Accepted Answer · 2013-10-28 15:59:52Z

2

Learn that

Null + 1 = Null

So your Vcount will always be null when adding 1.
Vcount must be not nullable, and it's default value muste be 0. Then when you will add 1 to 0, it will give you the good result.

answered Oct 28, 2013 at 15:59

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Comments

Markus · Accepted Answer · 2013-10-28 16:02:44Z

1

You can also Change your SQL Statement so that zero is used if the VCOUNT-Column is null:

"UPDATE TableVote SET Vcount=ISNULL(Vcount, 0)+1 WHERE id=@count"

This is especially useful if you cannot change the database schema to be Not-Null and have a Default value.

answered Oct 28, 2013 at 16:02

Markus

22.7k4 gold badges34 silver badges61 bronze badges

Comments

Stefano Bafaro · Accepted Answer · 2013-10-28 16:03:48Z

1

Vcount must be have a default 0 value, and Not Null, cause is an Int Field.

answered Oct 28, 2013 at 16:03

Stefano Bafaro

91310 silver badges20 bronze badges

Comments

Suraj Singh · Accepted Answer · 2013-10-28 16:24:31Z

1

If i am not wrong why not just use your primary key for it ,

UPDATE TableVote SET Vcount= max(id)  WHERE id=@count

OR

If some id's are deleted and you don't want to keep records or count deleted votes then

  UPDATE TableVote SET Vcount= COUNT(id)  WHERE id=@count

answered Oct 28, 2013 at 16:24

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

Collectives™ on Stack Overflow

how to increment an integer in sql c#

7 Answers 7

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related