After ExecuteScalar: no object reference specified, etc.

Question

What code should I add to accept null from the WHERE statement.

{
    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;
}

Answer 1

Simply:

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

But I'm not at all convinced that you really want, or the cause of the problem.

If you get an exception in C # code, this will not be from the where clause.

I am concerned about the fact that your connection seems to be reusing an existing variable. Bad idea. This will almost certainly be a local variable. You can also make your code easier by returning from the middle of it:

string sql = ...;

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

, , ExecuteScalar null, , int :

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

Answer 2

, , int. , cmd.ExecuteScalar() null. , . 0, cmd.ExecuteScalar() null

using (cn = new SqlConnection(ConnectionString()))
{
    cn.Open();
    using (cmd = new SqlCommand(sql, cn))
    {
        cmd.CommandType = CommandType.Text;
        object result = cmd.ExecuteScalar();
        numApprovals = result == null ? 0 : (int)result;
    }
}

return numApprovals;

Answer 3

As an aside, it is much simpler / clearer to format multi-line strings as:

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";