[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

asp.net + ExecuteReader requires an open and available Connection. The connection's current state is Closed.

I am getting the following error, but can't figure out why.
ExecuteReader requires an open and available Connection. The connection's current state is Closed.

when i step through the code and look at the connection it says it is open, here is the code i am using

            Dim objConn As New SqlConnection
            Dim oDataReader As SqlDataReader

            If Database = "One" Then
                objConn = New SqlConnection(ConfigurationSettings.AppSettings("Database.One"))
            Else
                objConn = New SqlConnection(ConfigurationSettings.AppSettings("Database.Two"))
            End If

            Try

                If objConn.State = ConnectionState.Closed Then
                    objConn.Open()
                End If

                With objCmd
                    .CommandText = strInQuery
                    .Connection = objConn
                    .CommandTimeout = 180
                    .CommandType = CommandType.Text
                    oDataReader = .ExecuteReader() 'This is where the code seems to break and produce error
                End With

                dgInDatagrid.DataSource = oDataReader
                dgInDatagrid.DataBind()

            Catch e As Exception
                Dim strErrorMessage As String

                strErrorMessage = "<strong>Class Error: </strong> " & vbCrLf & vbCrLf
                strErrorMessage &= "<strong>Running query: </strong> " & vbCrLf
                strErrorMessage &= "    " & strInQuery & vbCrLf & vbCrLf
                strErrorMessage &= "<strong>Error Message:</strong>" & vbCrLf
                strErrorMessage &= "    " & e.Message & vbCrLf & vbCrLf
                strErrorMessage &= "<strong>Stack Trace:</strong>" & vbCrLf
                strErrorMessage &= "    " & e.StackTrace & vbCrLf & vbCrLf

            Finally
                objCmd.Dispose()
                objConn.Close()
            End Try
0
dkilby
Asked:
dkilby
2 Solutions
 
nauman_ahmedCommented:
Where did you declare objCmd?

-Nauman.
0
 
the_paabCommented:
Just idea. There is more then Open and Close connection state, try make this change:
                If not objConn.State = ConnectionState.Open Then
                    objConn.Open()
                End If
0
 
Elvio Lujan.Net Senior DeveloperCommented:
               With objCmd
                    .Connection = objConn
                    .CommandText = strInQuery
                    .CommandTimeout = 180
                    .CommandType = CommandType.Text
                    objConn.Open()
                    oDataReader = .ExecuteReader() 'This is where the code seems to break and produce error
                End With
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arunrsCommented:
Am not sure if this explaination is correct. I remember it reading it somewhere.

By using the "with" clause, the scope of the object (here objCmd) gets terminated and hence the connection. So, try the same without the "with" clause. I am not sure if this is correct but just give a try and see.

arunrs
0
 
RejojohnyCommented:
just give this a try

Dim objConn As New SqlConnection

If Database = "One" Then
  objConn = New SqlConnection(ConfigurationSettings.AppSettings("Database.One"))
Else
  objConn = New SqlConnection(ConfigurationSettings.AppSettings("Database.Two"))
End If

Dim objCmd As SqlCommand = New SqlCommand(strInQuery, objConn)
objConn.Open()

Dim oDataReader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

dgInDatagrid.DataSource = oDataReader
dgInDatagrid.DataBind()
0
 
dkilbyAuthor Commented:
Ok it looks like it was to do with the objCmd  being declared outside of the function, was still open in a different function, when i pulled the declaring of the function in each function solved the problem

I am splitting the points between Rejojohny and nauman_ahmed  as they talked about the objCmd declaring.  

Thank you everyone for the help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now