Friday, April 12, 2013

There is already an open DataReader associated with this Command which must be closed first

Every so often in our ASP.Net website we would get the error, "There is already an open DataReader associated with this Command which must be closed first" referring to a SqlDataReader.  We would tweak code here and there and get it to go away.  Later another code tweak would bring it back.  We have no idea what is causing it.  


It is in a block of code that creates a SQLCommand, creates a SQLDataReader, and destroys the SQLDataReader and SQLCommand.  We are not sure how it is possible that another reader is executing on that command.


As a work around until we can find the issue we have added this attribute

MultipleActiveResultSets=True

to our connection string and it has seemed to have bypassed the problem.  I know it likely causes other problems, but due to the environment we work it that's what we have to do.

Installing Spring...



Installing Spring...

50% ready...

! Installation failed.  Error 404 Spring not found.  Spring is not available in your state.  Please try Florida.

ReportViewer Control ASP.Net Disabled When Routing

I was experimenting with possibly using Microsoft's Report Builder tool to create reports and then use the ReportViewer WebForms control to display them on a web page.

When I would create a small test site everything was great.  When I would add a report to our existing site it would show me a disabled ReportViewer task bar and no data from the report.

It took me forever to figure out why this was happening and longer to track down solutions.

Everywhere I went I saw people saying that it was something in my web config.  They sited that SQL Server 6 needed a different section in the web config than SQL Server 7 needs.  I saw that my web config had both and at one point I removed the older declaration and things started to work.

Then it happened.  The other day I was adding controls and experimenting with the page and all of a sudden I was back to square one.  I did ctrl-Z until I had removed all the changes I had been making on the page and got everything back to the way they were in code.  It didn't fix the display of the page.

This was horrible.  I was so close to delivering something and I had even told my superiors it was just about done and now I was stuck with a broken page I had no idea how to fix.  Nothing logically made sense.

I knew that one of the big differences with our site and a quick and dirty site with one page was routing.  We have a file filled with different routings.  I decided I would try disabling them and see what happened.  Sure enough it worked.  The problem was that the rest of the site was broken without the routings being turned on.

I searched and searched and kept finding the same old things about the web config.  Then I found a block of code like this.

  <handlers>
    <remove name="WebServiceHandlerFactory-Integrated"/>
    <remove name="ScriptHandlerFactory"/>
    <remove name="ScriptHandlerFactoryAppServices"/>
    <remove name="ScriptResource"/>
    <add name=": Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified" preCondition="integratedMode" />
    <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
    <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
    <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
    <add name="UrlRoutingHandler" preCondition="integratedMode" verb="*" path="UrlRouting.axd" type="System.Web.HttpForbiddenHandler, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </handlers>
This is much like the web config stuff I had seen elsewhere, but it had a bunch of other stuff around it. I have no idea if they relate, but I plugged them all in wholesale and decided I would yank out what didn't compile.
I also noticed that the line starting with <add name=": Reserved-ReportViewerWebControl-axd" had more attributes assigned than what the ones in my code had.

Everything worked. No compile errors. The control displayed the report and was not disabled.

I had no time to figure out if it was the extra attributes or the extra lines of code. All I know is it works. I hope this post will help someone else climb out of what they are struggling with because this was horrendously frustrating.

Update:
After adding all that and getting ready to deploy, I moved the site to a different location on the same machine. Not even a different machine. The original location continued to work fine, but the new location gave the disable ReportViewer control. I searched and search again.

When I had the problem previously I tried disabling routing for the control with the follow 2 lines of code:
RouteTable.Routes.Add(new Route("{resource}.axd/{*pathInfo}", new StopRoutingHandler()));
RouteTable.Routes.Ignore("{resource}.axd/{*pathInfo}");
Neither worked.  When I ran into the problem again I seemed to recall one post saying that the order of routes mattered.  Previously I had tried to remove the routes with these lines at the end of all the routing, thinking it would remove whatever got added.  It appears that it is the opposite.  If you tell it not to route from the start then nothing gets applied to it in the other routes.

I moved the lines to the top of the routing code and tested each line.  I found that either of them worked.