• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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();
            }
0
kwh3856
Asked:
kwh3856
  • 2
1 Solution
 
kwh3856Author Commented:
Sorry, forgot to add where the error occurs.

UpdateNewPHICntrConn.Open();---------------------------------------Error occurs on this line
0
 
appariCommented:
you are calling UpdateNewPHICntrConn.Open(); twice in your code.
remove/commentout  one line

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

            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(); ////////****************** Second time


0
 
SameerJagdaleCommented:
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
0
 
kwh3856Author Commented:
Thank you.  It is pretty late where I am and I guess my eyes are going cross on me:)

Thanks
Kenny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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