执行标量之后：未设置对象引用等

我应该添加什么代码来接受 WHERE 语句中的 null 。

{
    int numApprovals = 0;
    string sql = "SELECT COUNT(Type) AS OpenforApproval " +
       "FROM dbo.LeaveRequest " +
       "WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) " +
       "GROUP BY MgtApproval " +
       "HAVING MgtApproval IS NULL";
       //"SELECT COUNT(EffectiveDate) AS OpenforApproval FROM LeaveRequest GROUP BY TimeStampApproval HAVING (TimeStampApproval IS NULL)";

    using (cn = new SqlConnection(ConnectionString()))
    {
        cn.Open();
        using (cmd = new SqlCommand(sql, cn))
        {
            cmd.CommandType = CommandType.Text;
            numApprovals = (int)cmd.ExecuteScalar();
        }
    }

    return numApprovals;
}

Just:

WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) OR Type IS NULL

但我完全不相信这就是你真正想要的，或者问题的原因。

如果您在 C# 代码中遇到异常，它不会来自 where 子句。

顺便说一句，我担心您的连接似乎正在重用现有变量。馊主意。几乎可以肯定它应该是一个局部变量。您还可以通过从中间返回来使代码更简单：

string sql = ...;

using (var cn = new SqlConnection(ConnectionString()))
{
    cn.Open();
    using (cmd = new SqlCommand(sql, cn))
    {
        cmd.CommandType = CommandType.Text;
        return (int) cmd.ExecuteScalar();
    }
}    

如果问题如 Jamie 所说，ExecuteScalar 返回 null，最简单的解决方法是将其转换为可为 null 的 int 并使用 null 合并运算符：

return (int?) cmd.ExecuteScalar() ?? 0;