Solved

The connection was not closed. The current state is open

Posted on 2013-06-18
12
1,841 Views
Last Modified: 2013-06-19
I asked a question about this a week ago about how to open and close connections and I think we are doing it correctly but We are still getting these errors on or Web sites about the current state of a connection being open.  The following are the 3 connection methods we use.  An the error follows this.  technically we should not be getting these errors but we are and its usually on post back of a page not on initial load.  We thought we had this taken care of now its happening agian.

 Private Shared Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
    'Private Shared Conn As New SqlConnection(ConfigurationManager.AppSettings("ProdServer"))


For use in returning data from sql server when we need Id back or can use in place of GetReader.

    Public Shared Function getData(ByVal sqlString As String) As DataTable
        Dim cmd As New SqlCommand(sqlString, Conn)
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter

        Try
            da.SelectCommand = cmd
            da.Fill(ds)
        Finally
            If Not [Object].Equals(da.SelectCommand, Nothing) Then
                da.SelectCommand.Dispose()
            End If
        End Try

        getData = ds.Tables(0)
    End Function

Used for making quick connections to read data and return to grids and dropwdowns

    Public Shared Function getReader(ByVal sqlstring As String) As DataTable
        Dim cmd As New SqlCommand(sqlstring, Conn)
        Dim dt As New DataTable
        Conn.Open()
        Dim dr As SqlDataReader = Nothing
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.HasRows Then
            dt.Load(dr)
        End If
        dr.Close()
        Return dt
    End Function

'For use in building our drop down lists.

    Public Shared Sub buildDD(ByVal sql As String, ByVal ddField As Object)
        Dim itemText As String
        Dim ddDataTable As New DataTable

        ddDataTable = getReader(sql)

        For Each myDataRow In ddDataTable.Rows
            If ddDataTable.Columns.Count = 1 Then
                itemText = myDataRow(0)
            Else
                itemText = myDataRow(1)
            End If

            ddField.Items.Add(New ListItem(itemText, myDataRow(0)))
        Next
    End Sub

For use in InsertUpdate and deleteing Data
    Public Shared Sub insertUpdateDelete(ByVal sqlString As String)
        Dim cmd As New SqlCommand(sqlString, Conn)

        Conn.Open()
        cmd.ExecuteNonQuery()
        Conn.Close()
    End Sub

Open in new window


This is the common error we get.
The connection was not closed. The connection's current state is open.
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: The connection was not closed. The connection's current state is open.

Source Error:





Line 39:         Dim cmd As New SqlCommand(sqlstring, Conn)
Line 40:         Dim dt As New DataTable
Line 41:         Conn.Open()
Line 42:         Dim dr As SqlDataReader = Nothing
Line 43:         dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Source File: E:\Applications\ReadinessTracker\App_Code\ReadinessCommon.vb    Line: 41

Stack Trace:





[InvalidOperationException: The connection was not closed. The connection's current state is open.]
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +14
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +83
   System.Data.SqlClient.SqlConnection.Open() +96
   ReadinessCommon.getReader(String sqlstring) in E:\Applications\ReadinessTracker\App_Code\ReadinessCommon.vb:41
   ReadinessCommon.buildDD(String sql, Object ddField) in E:\Applications\ReadinessTracker\App_Code\ReadinessCommon.vb:56
   SRP_SRPCheckIn.Page_Load(Object sender, EventArgs e) in E:\Applications\ReadinessTracker\SRP\SRPCheckIn.aspx.vb:45
   System.Web.UI.Control.OnLoad(EventArgs e) +92
   System.Web.UI.Control.LoadRecursive() +54
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772
0
Comment
Question by:kdeutsch
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39258573
In the "getReader" function you open the connection but do not close it.  However you do close it in your "insertUpdateDelete" function.  Why have you left it out and where is the Conn object defined and instantiated?

such that:

    Public Shared Function getReader(ByVal sqlstring As String) As DataTable
        Dim cmd As New SqlCommand(sqlstring, Conn)
        Dim dt As New DataTable
        Conn.Open()
        Dim dr As SqlDataReader = Nothing
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.HasRows Then
            dt.Load(dr)
        End If
        dr.Close()
        Conn.Close()
        Return dt
    End Function

Open in new window

0
 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 200 total points
ID: 39258635
You are using a shared object between different functions, then what if two users use the page at the same time?, in one method you would try to open the connection and during that function execution the other users would try to open the same SqlConnection object again.

Why don't use a different connection object for each function?, if your concern is the connection creation (the most expensive task) I can say that with connection pooling this problem goes far away.
0
 

Author Comment

by:kdeutsch
ID: 39259234
Jorge_toriz,

Ok I see what you are sayingm these are shared connections and I though connection pooliing should already be in effect.  I dont understand becuase all of this was working fine up till about a month ago it all started to crash, never had a problem with multiple people using the same page at once.  How can we impliment a better model.
0
 

Author Comment

by:kdeutsch
ID: 39259236
Tagit,

ok, stanage as its been working fine up till yesterday, thanks.
0
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 300 total points
ID: 39259413
Just to follow up on what Jorge_toriz said, basically each time you need a connection you should create it, open it, use it, and close it. Connection pooling will help to make sure that subsequent requests can get a connection quickly. You are just asking for trouble using a shared (static) connection object in a web application.

Also, any code that opens a connection should be in a "try/finally" or "using" construct with the close in the finally. That way if an exception occurs inside your code after you open the connection you will ensure that it gets closed. As an example, here is one of your methods modified to use the pattern. Note, I usually write in C# so the VB.NET syntax may need a bit of tweaking.

Public Shared Sub insertUpdateDelete(ByVal sqlString As String)
    Using conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
    Using cmd As New SqlCommand(sqlString, conn)
        conn.Open()
        cmd.ExecuteNonQuery()
    End Using
    End Using
End Sub

Open in new window


Finally, it's not a great idea to have two different entries in your config file (for dev vs. prod) because it means you have to make a code change before building and deploying the prod version. It's generally a much better idea to have a single connection string in the config file and modify it as you deploy (ideally in an automated manner).
0
 

Author Comment

by:kdeutsch
ID: 39259510
So If I understand what you are saying,

do not share this object for all my connections put it in all my connections individual as you did in your example.

Private Shared Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39259665
That's what I said.
0
 

Author Comment

by:kdeutsch
ID: 39259706
OK,
I want to make sure these are all g to g then.

 Public Shared Function getReader(ByVal sqlstring As String) As DataTable
        Using Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
            Dim cmd As New SqlCommand(sqlstring, Conn)
            Dim dt As New DataTable
            Conn.Open()
            Dim dr As SqlDataReader = Nothing
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                dt.Load(dr)
            End If
            Return dt
        End Using
    End Function


  Public Shared Sub insertUpdateDelete(ByVal sqlString As String)
        Using Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
            Using cmd As New SqlCommand(sqlString, Conn)
                Conn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

Public Shared Function getData(ByVal sqlString As String) As DataTable
        Using Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
            Dim cmd As New SqlCommand(sqlString, Conn)
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter

            Try
                da.SelectCommand = cmd
                da.Fill(ds)
            Finally
                If Not [Object].Equals(da.SelectCommand, Nothing) Then
                    da.SelectCommand.Dispose()
                End If
            End Try

            getData = ds.Tables(0)
        End Using
    End Function
0
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39259785
Why are you only putting the SqlCommand object inside a "using" in one of the three method?

Also, it seems kind of silly to dispose the SelectCommand of the DataAdapter when the whole DataAdapter is disposable.
0
 

Author Comment

by:kdeutsch
ID: 39259869
Ok,

Here is the revised ones.

 Public Shared Function getData(ByVal sqlString As String, ByVal arrTables() As String) As DataSet
        Using Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
            Using cmd As New SqlCommand(sqlString, Conn)
                Dim ds As New DataSet
                Dim da As New SqlDataAdapter
                Dim theCounter As Integer

                da.SelectCommand = cmd
                da.Fill(ds)
                Conn.Close()

                For theCounter = 0 To arrTables.Length - 1
                    ds.Tables(theCounter).TableName = arrTables(theCounter)
                Next

                getData = ds
            End Using
        End Using
    End Function

    Public Shared Function getReader(ByVal sqlstring As String) As DataTable
        Using Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
            Using cmd As New SqlCommand(sqlstring, Conn)
                Dim dt As New DataTable
                Conn.Open()
                Dim dr As SqlDataReader = Nothing
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                If dr.HasRows Then
                    dt.Load(dr)
                End If
                Return dt
            End Using
        End Using
    End Function

    Public Shared Sub insertUpdateDelete(ByVal sqlString As String)
        Using Conn As New SqlConnection(ConfigurationManager.AppSettings("DevServer"))
            Using cmd As New SqlCommand(sqlString, Conn)
                Conn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39259886
Exactly!
0
 

Author Closing Comment

by:kdeutsch
ID: 39259925
Thanks for the help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now