[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-06
4
Medium Priority
?
369 Views
Last Modified: 2013-12-17
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
Comment
Question by:kwh3856
  • 2
4 Comments
 

Author Comment

by:kwh3856
ID: 22902132
Sorry, forgot to add where the error occurs.

UpdateNewPHICntrConn.Open();---------------------------------------Error occurs on this line
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 22902151
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
 
LVL 13

Expert Comment

by:SameerJagdale
ID: 22902165
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
 

Author Closing Comment

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

Thanks
Kenny
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month18 days, 4 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question