Link to home
Start Free TrialLog in
Avatar of kwh3856
kwh3856Flag for United States of America

asked on

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

I am trying to read a single record from a table, capture the value from a single field, add 1 to the value and then write the value back out to the table.  Can someone tell me why I keep getting this error message "The Connection was not closed.  The Connection's current state is open.  I thought I am using completely different connections to do the read and then do the write.  

Here is my code:

----------------------------------------------------------------

//Read Record ID Counter


            decimal id;
            string taxid;
            string userid;
            string username;

            SqlCommand oCmd;
            SqlConnection oConn;
            SqlDataReader reader = null;
           

            oConn = new SqlConnection();
            oConn.ConnectionString = ConfigurationManager.ConnectionStrings["12123123ConnectionString"].ConnectionString;
       
            oConn.Open();
            //use stored proc name here
            oCmd = new SqlCommand("ReadDRRIdCntr", oConn);
            oCmd.CommandType = CommandType.StoredProcedure;

            // Get the data
            reader = oCmd.ExecuteReader();
                   
            reader.Read();
   
            if (reader.HasRows)
            {
                id = Convert.ToDecimal(reader["id"].ToString());

                id += (decimal)1;
                Session.Add("id", id);
            }

            if (oConn != null)
            {
                oConn.Close();
            }

            ///  ---------------   Write Updated Counter Back to Database
     
            SqlConnection UpdateNewPHICntrConn;
            UpdateNewPHICntrConn = new SqlConnection();
            UpdateNewPHICntrConn.ConnectionString = ConfigurationManager.ConnectionStrings["123123123ConnectionString"].ConnectionString;
            SqlCommand Updcmd;

            UpdateNewPHICntrConn.Open();
            Updcmd = new SqlCommand("UpdatePHICounter", oConn);
            Updcmd.CommandType = CommandType.StoredProcedure;
            id = Convert.ToDecimal(Session.Contents["id"].ToString());
            Updcmd.Parameters.Add("@ID", SqlDbType.Decimal).Value = id;
 
            Updcmd.Parameters.Clear();

            UpdateNewPHICntrConn.Open();

            // Write Updated Counter Data
            Updcmd.ExecuteNonQuery();

            if (UpdateNewPHICntrConn != null)
            {
                UpdateNewPHICntrConn.Close();
            }
Avatar of kwh3856
kwh3856
Flag of United States of America image

ASKER

Sorry, forgot to add where the error occurs.

UpdateNewPHICntrConn.Open();---------------------------------------Error occurs on this line
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
couple of things:
i prefer you write a code in try..catch..finally block. and close all your connections in finally. this will ensure that even though you have an exception, your connection will be closed.
further,
i suggest:
instead of directly opening the connection, use the code like one mentioned below:

if (oConn.State != ConnectionState.Open)
oConn.Open();

if (oConn != null && oConn.State != ConnectionState.Closed )
oConn.Close();
one important thing:
it looks like you are opening the connection twice.

UpdateNewPHICntrConn.Open(); //first time
Updcmd = new SqlCommand("UpdatePHICounter", oConn);
Updcmd.CommandType = CommandType.StoredProcedure;
id = Convert.ToDecimal(Session.Contents["id"].ToString());
Updcmd.Parameters.Add("@ID", SqlDbType.Decimal).Value = id;
Updcmd.Parameters.Clear();
UpdateNewPHICntrConn.Open(); // this is the second time
Avatar of kwh3856

ASKER

Thank you.  It is pretty late where I am and I guess my eyes are going cross on me:)

Thanks
Kenny