ERROR ON: numApprovals = (int)cmd.ExecuteScalar();

Question

public static int AwaitingApprovals()
{
    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

It seems your query is incorrect.

At the outset, I cannot understand the need of group by & you are missing a ")" towards the end of the SQL.

Answer 2

The SQL syntax is wrong. You have an opening parenthesis after HAVING that doesn't have any closing parenthesis. Add a closing parenthesis or just remove the opening parenthesis.

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

Answer 3

You seem to be missing a closing parenthesis at the end of your query statement. Wouldn't that cause cmd.ExecuteScalar to raise an exception?

Answer 4

You're missing a bracket, but also, you don't need a GROUP BY and a HAVING, you can just have an additional part of your WHERE clause.

string sql = "SELECT COUNT(Type) AS OpenforApproval FROM dbo.LeaveRequest WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) AND MgtApproval IS NULL";