?
Solved

The connection was not closed. The current state is open

Posted on 2013-06-18
12
Medium Priority
?
2,199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 43

Expert Comment

by:Rob
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 800 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
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 

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 1200 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
 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 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