[RESOLVED] ASP.Net Database Error Problem

[Strider]

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:

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

[dj4uk]

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

[Strider]

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

[dj4uk]

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

[Strider]

there ll be a max of 6 people ever using the site at the one time, its for and intranet

[Strider]

the code will explain the way i have it:

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