The connection was not closed. The connection's current state is open.

Question

User716270604 posted

in my code, i have two identical calls to the same function, both supposed to return string values from one field each. when i try to preview the application in ie i get a server error on the last row in this call - even though the identical call two rows earlier was ok. i have several similar calls following, and when i tried commenting out the one giving me problems the next one did the same thing.

this is what the call looks like. the complaint from the server is on row 8 (unless i comment it out, and it complains about the next call on row 10...)

1    objDynVars.Table = "tblBoard"
2    objDynVars.Crit = "ID"
3    objDynVars.CritValue = Request.QueryString("member")
4   
5    objDynVars.Fields = "FirstName"
6    tbFirstName.Text = objDynamic.Get_Dynamic(objDynVars)
7    objDynVars.Fields = "LastName"
8    tbLastName.Text = objDynamic.Get_Dynamic(objDynVars)   

and this is the function called by objDynamic.Get_Dynamic(objDynVars)

  Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ConfigurationSettings.AppSettings("connectionstring") & "")

1    Function Get_Dynamic(ByVal objVars As clsVars) As String
2       Dim strSQL As String
3       strSQL = "SELECT " & objVars.Fields & " FROM " & objVars.Table & " WHERE " & objVars.Crit & "=" & objVars.CritValue
4       Dim objCmd As New OleDbCommand(strSQL,objConn)
5   
6       Try
7         objConn.Open()
8         Return objCmd.ExecuteScalar()
9         objConn.Close()
10      Catch e As OleDbException
11        Throw e
12      End Try
13   End Function
 

Why does the connection not close when i call the function the second time?

Answer 1

User-989915499 posted

Hi There,

6       Try
7         objConn.Open()
8         Return objCmd.ExecuteScalar()
9         objConn.Close()
10      Catch e As OleDbException
11        Throw e
12      End Try
13   End Function
 

If you notice the your code not reaching line objConn.Close() , as Return objCmd.ExecuteScalar() is finalize the function.

Try this:

       Try
         objConn.Open()
         Return objCmd.ExecuteScalar()         
      Catch e As OleDbException
        Throw e

Finally

objConn.Close()
      End Try
   End Function

"Finally" section will get executed no matter what.

Answer 2

User716270604 posted
that's exactly what i needed... thank you!

Answer 3

User-1463153959 posted

This may be a little late...but I wanted to let you know that this post solved a problem that I have been struggling with for three days. I was reading data from an Excel file. Sometimes it would work correctly, and then I would add a little more data to it and it would throw an "Unknown error." I finally figured that it had to do with not closing the connection properly, but couldn't figure out where the close needed to go until I saw your post about putting it into the "Finally" portion of the Try block. Thanks!

Answer 4

User993897575 posted

Kindly help out with below issue ..

on executing the below code its the throwing the error.. "The connection was not closed. The connection's current state is open."

 

using

System;

using

System.Collections;

using

System.Configuration;

using

System.Data;

using

System.Linq;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.HtmlControls;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Xml.Linq;

using

 

System.Data.SqlClient;

public

partial class Default2 : System.Web.UI.Page

{

 

 

 

 

{

 

 

DropDownList1.DataSource = GetDr( sqlText );

DropDownList1.DataBind();

}

 

{

 

sqlConn =

sqlCmd =

sqlConn.Open();

sqlCmd.Connection.Open();

dr = sqlCmd.ExecuteReader(System.Data.

 

}

 

{

 

 

}

 

{

 

}

SqlDataReader dr;SqlConnection sqlConn;SqlCommand sqlCmd;private void Page_Load(object sender, System.EventArgs e)string sqlText = "select * from tblresource";private SqlDataReader GetDr( string sqlText)new SqlConnection(ConnectionString());new SqlCommand(sqlText,sqlConn);CommandBehavior.CloseConnection);return dr;private string ConnectionString()return ConfigurationManager.ConnectionStrings["ESSConnectionString"].ConnectionString;private void Page_Init( object Sender, System.EventArgs e)//end Page_Init

}