After ExecuteScalar: Object reference not set etc

c#, sql

Solution

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;

Problem

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

Original source