用于添加值的 SQL 更新语句

发布于 11-18 23:58 字数 957 浏览 1 评论 0 原文

   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();
    }
}

我想为用户在线程中留下的评论添加 5 分的声誉..但它无法更新为什么?/... commentID 确实获得了一个值,因此声誉参数

   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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

謌踐踏愛綪 2024-11-25 23:58:40

更改

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

为:

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

因此在添加@Reputation之前将Reputation字段中的NULL更改为0


或者,如果您首先将所有 NULL 值设置为 0,然后使用语句 ="">NOT NULL ,则可以保留代码。 “http://msdn.microsoft.com/en-us/library/ms190273.aspx”rel="nofollow">更改表。执行以下命令一次:

UPDATE Users
SET Reputation = 0
WHERE Reputation IS NULL ;

ALTER TABLE Users 
ALTER COLUMN Reputation NOT NULL DEFAULT 0 ;

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 ;
小ぇ时光︴ 2024-11-25 23:58:40
sb.Append(" SET Reputation = (u.Reputation + @Reputation)");

编辑:我错过了你关于 u.Reputation 可能为 Null 的原始注释。接下来尝试:

sb.Append(" SET Reputation = (isnull(u.Reputation,0) + @Reputation)");
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)");
苯莒 2024-11-25 23:58:40

您也可以更改

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

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

,但所有现有答案都可以很好地满足您的需求。

上述提供的唯一好处是,如果 @Reputation 也为 NULL,它仍然有效。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文