After ExecuteScalar: Object reference not set etc

Question

What code I should add to accept null from 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

Just:

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's what you really want, or the cause of the problem.

If you're getting an exception in the C# code, it's not going to be from the where clause.

I'm concerned by the fact that your connection seems to be reusing an existing variable, by the way. Bad idea. It should almost certainly be a local variable. You can also make your code simpler 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();
    }
}

If the problem is as Jamie says, that ExecuteScalar is returning null, the easiest way of getting round that is to cast it to a nullable int and use the null coalescing operator:

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

Answer 2

The problem is probably the direct cast to int. This throws an exception if cmd.ExecuteScalar() returns null. You need to decide what to return in that case. For this example I am returning 0 if cmd.ExecuteScalar() returns 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's much easier/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";