[RESOLVED] ASP.Net Database Error Problem
I have a web application which integrates with a remote sql server database.
In general the application works fine however at least once a day if someone is using the system they will receive the following error:
Quote:
ExecuteReader requires an open and available Connection. The connection's current state is Closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed.
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:
[InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed.]
System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing) +279
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +80
System.Data.SqlClient.SqlCommand.ExecuteReader() +42
Repairs.WebDBTools.LoadControl(ListBox& p_lstBox, String p_sqlstmt)
Repairs.ReceiveProduct.Page_Load(Object sender, EventArgs e)
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
i know above it shows the method that has the problem but this happens with any function i have that tries to access the database
the way in which my application works is that it creates an instance of the database connection for each session that accesses the site
i have no idea what is causing the error, at first i presumed it was some shared methods but after changed these to instance methods users still get the same error.
In order to fix the issue i've to restart IIS on our server so as to refresh everything.
Has anyone got any ideas to what could be the problem
Re: ASP.Net Database Error Problem
So you have a database connection open across a Session?
I was going to say the connection sounds dodgy but if you have the connection open across the session then it might be performance issues causing this.
Ever thought of having local database connections?
DJ
Re: ASP.Net Database Error Problem
the remote database is not one i've created but its one that im required in the project to retrieve and update data to. so this database will never be local for me. while my web application must remain local.
Would you recommend putting a timeout on the sessions so at least the connection will be closed after X amount of time and the session will then need to re-establish the connection again.
Or do sessions have an automatic timeout or do they stay active even after the browser has been closed.
Several users can access the site fine and update/retrieve data from the remote sql server database.
however once this error appears for one person all users will receive the error, this is why i dont know if it session related
Re: ASP.Net Database Error Problem
No you misunderstand me.
I don't think opening a database connection in the Session object is very efficient. If you have a large amount of traffic on the site then you could get problems like dropped connections - not because of the Session but because of the large numbers of open database connections.
When I said local database connection I meant opening the connection just before you need it and closing it just after.
How many people use your site?
DJ
Re: ASP.Net Database Error Problem
there ll be a max of 6 people ever using the site at the one time, its for and intranet
Re: ASP.Net Database Error Problem
the code will explain the way i have it:
VB Code:
Imports System.Data.SqlClient
Public Class WebConn
' singleton that maintains a connection to the database
Private Shared theInstance As WebConn
Private m_connection As SqlConnection
Public ReadOnly Property connection() As SqlConnection
Get
Return m_connection
End Get
End Property
Public Shared Function getInstance() As WebConn
If theInstance Is Nothing Then
theInstance = New WebConn
End If
Return theInstance
End Function
Public Sub New()
'Create an Connection object,
'and then pass in the ConnectionString to the constructor.
m_connection = New SqlConnection(ApplicationSettings.SQL_SERVER_CONNECTIONSTRING)
Try
m_connection.Open()
Catch ex As Exception
Throw New Exception("Unable to establish connection to web")
End Try
End Sub
Protected Overrides Sub finalize()
Try
'm_connection.Close()
Catch ex As Exception
End Try
End Sub
End Class
so everytime i want to execute some database code i just called the webconn.getconnection() method which checks if the connection exists, if not creating one