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

c++sql-server

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

Best Solution

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