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

  • 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?

    Wednesday, July 4, 2007 9:32 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 4, 2007 10:52 PM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 4, 2007 10:52 PM
  • User716270604 posted
    that's exactly what i needed... thank you!
    Thursday, July 5, 2007 11:41 AM
  • 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!

    Tuesday, April 15, 2008 7:12 AM
  • 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

    }

     

     

    Tuesday, April 13, 2010 1:32 AM