Geeks With Blogs

Tim Huffam Dotting the I and crossing the T of I.T.

The following error occured when 2 requests hit an ASP.NET (2.0) web app (using SQL Server 2005) at the same time:

The connection was not closed. The connection's current state is open

Normally this only occurs if you have a connection that is not being closed.  However in our case all connections where being closed using a try-finally block (personally I prefer using a using block).

This error only occured when the 2 request were initiated from different machines.

It turns out that the error occured because the same username was being used  for all requests (we were not interested in the username  in the app - and simply validated the request through a token and set the username to the same static string for all requests).  As soon as we made the username unique (by appending a guid to it) the error ceased to occur.

This indicates that ADO.NET uses the username as part of it's connection pooling management. 

*** UPDATE 17 Nov 08 ***
After more testing we proved this theory incorrect.  We found that the root cause of the original exception (connection state is open) was because we used a static member in one of our ORM classes - this held on to the connection, and it never got closed (hence the exception) - once we converted this to a non-static variable the problem disappeared.  However, adding the GUID to the ASP.NET username did have the above mentioned affect in several scenarios - which still leads me to believe the username has some relation/use in the connection pooling management (not sure why - as it's the process's identity that things run as - not the user's).

HTH

Tim

Posted on Tuesday, September 9, 2008 12:48 PM C# .NET , SQL Server , ASP.NET | Back to top


Comments on this post: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open

# re: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open
Requesting Gravatar...
Hi Tim,
I have exactly the same problem where if 2 or more people are requesting pages from my ASP.NET server at the same time, at least one of them creates a "The connection was not closed..." error.

Can you clarify exactly what you did to fix it? I'm not sure where you append the GUID to make the username unique, and why this happens in the first place. There doesn't seem to be anywhere else on the net that documents this.

Thanks,
Stephen
Left by Stephen on Nov 11, 2008 3:28 AM

# re: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open
Requesting Gravatar...
Hi Stephen

I've just updated the post (above) with what the problem turned out to be.

Cheers
Tim
Left by Tim on Nov 17, 2008 9:51 PM

# re: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open
Requesting Gravatar...
Thanks Tim,

That's helped immensely (and I hope many others too). To fix this, I was verging on removing all the bits of code that use SqlDataReader objects (that were using static connections all over the place) and instead use DataSet files instead (*.xsd) to create DataSets to do all the dirty-work. This did work, and the problem went away, although using DataSets for everything would be a little less efficient.

Obviously, by using the DataSets from xsd files, all my static SqlConnections would have then been unused, but that would be side-stepping the issue altogether.

Stephen
Left by Stephen on Nov 18, 2008 6:13 AM

# re: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open
Requesting Gravatar...
Thanks for the tip, it was really helpfull !
Left by Cougnafou on Nov 25, 2010 10:24 AM

# re: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open
Requesting Gravatar...
i found best code for connection in
http://codeprojectdownload.com/389/#.T0Xba4fXDZE
so please refers this link for best answer
Left by bhavika on Feb 23, 2012 12:27 AM

# re: ASP.NET SQL Server error: The connection was not closed. The connection's current state is open
Requesting Gravatar...
Thanks for the post.
Yes, I had a similar issue.
The set up was using multithreading. I was trying to call a database stored proc using entity data model.
I was using a static entity object and due which it was holding up the connection and throwing the below exception.
'The connection was not closed. The connection's current state is connecting.'

Please don't use static while creating the entity object
Left by MK on Apr 26, 2013 8:06 AM

Your comment:
 (will show your gravatar)


Copyright © Tim Huffam | Powered by: GeeksWithBlogs.net