'Specified cast is not valid' error in ASP.net MVC application. Works locally, and for a time after publishing, but then throws the error

My MVC application has worked fine for over a year, but it has now started causing the error below when run online. It works fine locally in debug though.

I made some changes on a Thursday, and it suddenly started causing the error the following Monday (changes were minor, HTML table layout changes). The error is intermittent, and seems to go away for a while (a day or so) if we restart the server. Any ideas what it could be?

Please let me know if you need any more information.

Thanks

Chris

Server Error in '/ISIS' Application.
--------------------------------------------------------------------------------

Specified cast is not valid. 
Description: An unhandled exception occurred during the execution of th开发者_开发百科e current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[InvalidCastException: Specified cast is not valid.]
   System.Data.SqlClient.SqlBuffer.get_Int32() +4838981
   System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) +38
   Read_Target(ObjectMaterializer`1 ) +275
   System.Data.Linq.SqlClient.ObjectReader`2.MoveNext() +29
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +7667556
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +61
   ISIS2.Controllers.HomeController.Index() in C:\VS2008\VisualStudio\Projects\ISIS2\ISIS2\Controllers\HomeController.cs:66
   lambda_method(ExecutionScope , ControllerBase , Object[] ) +39
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +178
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +24
   System.Web.Mvc.<>c__DisplayClassa.<InvokeActionMethodWithFilters>b__7() +53
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +258
   System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +20
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +193
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +395
   System.Web.Mvc.Controller.ExecuteCore() +123
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +23
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +145
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +54
   System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +7
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

EDIT here is the code for the index in HomeController:

 public ActionResult Index()
        {
            try
            {
            var user = (Staff)Session["CurrentUser"];

                int reminder = 14;
                if (Request.Form["AlertPeriod"] != null)
                {
                    reminder = int.Parse(Request.Form["AlertPeriod"]);
                }
                if (Request.Form["AlertPeriodLower"] != null)
                {
                    reminder -= 14;
                }
                else if (Request.Form["AlertPeriodHigher"] != null)
                {
                    reminder += 14;
                }
                ViewData["AlertPeriod"] = reminder;
                ViewData["Alerts"] = (from a in db.Alerts
                                      where a.AlertType.Text == "StaffNews" &&
                                      ((a.StartDate >= DateTime.Today && a.StartDate <= DateTime.Today.AddDays(reminder)) ||
                                       (a.EndDate >= DateTime.Today && a.EndDate <= DateTime.Today.AddDays(reminder)) ||
                                       (a.StartDate <= DateTime.Today && a.EndDate >= DateTime.Today.AddDays(reminder)))
                                      orderby a.EndDate
                                      select a).ToList();
                ViewData["Targets"] = (from t in db.Targets
                                       where t.StaffID == user.StaffID &&
                                       ((t.TargetContextType.Description == "StudentSet" && !t.Read) || (t.TargetDiscussions.Any(td => td.AuthorIsStudent && !td.Read)))
                                       orderby (t.Enrolment != null ? t.Enrolment.Course.Name : ""), t.DateDue
                                       select t).ToList();

                ViewData["Trips"] = (from t in db.Trips
                                     where t.TripStartDate >= DateTime.Today && t.TripStartDate <= DateTime.Today.AddDays(14) && t.StaffID == user.StaffID
                                     orderby t.TripStartDate
                                     select t);

                ViewData["Attending"] = (from a in db.TripAttendings
                                         where a.Trip.StaffID == user.StaffID
                                         select a);





                return View("Index", user);
            }
            catch (NullReferenceException e)
            {
                return RedirectToAction("NonUserIndex");
            }
        }

It turns out that this wasn't a code problem at all - it was to do with IIS.

We found that these errors were only happening when many staff or students were logging on, so it must be a resource problem, either memory or number of concurrent users.

After several failed attempts of playing around with setting in the IIS application pool, we tried moving the offending web app into its own dedicated application pool. This seems to have solved the problem - no errors on the error log at all.

EDIT: The above isn't entirely true. After sifting through hundreds of error logs, I found that the first error before everything started going wrong was always about "There is already an open data reader". And it was always occuring on the "Attendance" page, which is a slow one to load (takes about a minute or so).

After a bit of googling I found that eneabling Multiple Active Result Sets in the conncetions string could solve this. I think the reason that the error was occuring in the first place was that before the command was compelted, and the data reader closed, people were hitting refresh which meant lots of data readers were left open. We still get errors if people refresh whilst attendance is loading, but it doesn't bring the whole site down.

It looks like on Line 66 of your HomeController class you are running a SQL query (via Linq) which is getting a DBNull when it expects an Int32