SQL update statement to add a value

Question

   public void InsertUserReputation()
{
    StringBuilder sb = new StringBuilder();
    sb.Append("UPDATE u ");
    sb.Append(" SET u.Reputation = (u.Reputation + @Reputation)");//Problem is here u.Reputation is "Null" not 0... I think i need some if statement to check if it is a null and then update it to 0 and then add..what do you think?
    sb.Append(" FROM Users u");
    sb.Append(" INNER JOIN Comments c ON c.UsersID = u.UsersID");
    sb.Append(" WHERE c.CommentsID = @CommentsID");

    using (SqlConnection conn = new SqlConnection(AllQuestionsPresented.connectionString))
    {
        SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
        cmd.Parameters.Add("@Reputation", SqlDbType.Int).Value = 5;
        cmd.Parameters.Add("@CommentsID", SqlDbType.Int).Value = commentID;  
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

I want to add a reputation to the user of 5 points for the comment he leaves in the thread..but it fails to update why?/... commentID does get a value and so the reputation paramater

(you don't need a StringBuilder for that statement, btw - that is adding a tiny touch of overhead, but more importantly: it makes the code unnecessarily complex) — Marc Gravell
– Marc Gravell, Commented Jul 7, 2011 at 6:21
What's the error you are getting? Or it runs, but no error? More details, please. — Cortright
– Cortright, Commented Jul 7, 2011 at 6:22
The SQL looks good to me have you tried running SQLProfiler sites.google.com/site/sqlprofiler to see what the DB is actually running — Daveo
– Daveo, Commented Jul 7, 2011 at 6:34
Why not just change the field to NOT allow NULLS and set the default value = 0 — Daveo
– Daveo, Commented Jul 7, 2011 at 6:44

ypercubeᵀᴹ · Accepted Answer · 2011-07-07 07:20:05Z

3

Change

SET u.Reputation = (u.Reputation + @Reputation)

into:

SET u.Reputation = COALESCE(u.Reputation,0) + @Reputation

so NULLs in Reputation field are changed into 0 before adding @Reputation.

Alternatively, you can keep your code if you first set all NULL values to 0 and then make the field NOT NULL using statement ALTER TABLE. Execute the following, once:

UPDATE Users
SET Reputation = 0
WHERE Reputation IS NULL ;

ALTER TABLE Users 
ALTER COLUMN Reputation NOT NULL DEFAULT 0 ;

edited Jul 7, 2011 at 7:20

answered Jul 7, 2011 at 6:48

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

Comments

Arvo · Accepted Answer · 2011-07-07 06:49:29Z

1

sb.Append(" SET Reputation = (u.Reputation + @Reputation)");

edit: I missed your original note about u.Reputation possibly being Null. Try next:

sb.Append(" SET Reputation = (isnull(u.Reputation,0) + @Reputation)");

edited Jul 7, 2011 at 6:49

answered Jul 7, 2011 at 6:23

Arvo

10.6k1 gold badge34 silver badges36 bronze badges

1 Comment

Arvo Over a year ago

Edited my answer. Actually ypercube answer should give same result.

devjc · Accepted Answer · 2013-09-06 22:07:15Z

0

You could also change

SET u.Reputation = (u.Reputation + @Reputation)

to

SET u.Reputation = COALESCE(u.Reputation + @Reputation, @Reputation, 0)

but all the existing answers well-address your needs

The only benefit the above offers is that, in the event @Reputation is also NULL, it still works.

answered Sep 6, 2013 at 22:07

devjc

11110 bronze badges

Collectives™ on Stack Overflow

SQL update statement to add a value

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related