kwh3856
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.Conne ctionStrin gs["121231 23Connecti onString"] .Connectio nString;
oConn.Open();
//use stored proc name here
oCmd = new SqlCommand("ReadDRRIdCntr" , oConn);
oCmd.CommandType = CommandType.StoredProcedur e;
// Get the data
reader = oCmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
id = Convert.ToDecimal(reader[" id"].ToStr ing());
id += (decimal)1;
Session.Add("id", id);
}
if (oConn != null)
{
oConn.Close();
}
/// --------------- Write Updated Counter Back to Database
SqlConnection UpdateNewPHICntrConn;
UpdateNewPHICntrConn = new SqlConnection();
UpdateNewPHICntrConn.Conne ctionStrin g = ConfigurationManager.Conne ctionStrin gs["123123 123Connect ionString" ].Connecti onString;
SqlCommand Updcmd;
UpdateNewPHICntrConn.Open( );
Updcmd = new SqlCommand("UpdatePHICount er", oConn);
Updcmd.CommandType = CommandType.StoredProcedur e;
id = Convert.ToDecimal(Session. Contents[" id"].ToStr ing());
Updcmd.Parameters.Add("@ID ", SqlDbType.Decimal).Value = id;
Updcmd.Parameters.Clear();
UpdateNewPHICntrConn.Open( );
// Write Updated Counter Data
Updcmd.ExecuteNonQuery();
if (UpdateNewPHICntrConn != null)
{
UpdateNewPHICntrConn.Close ();
}
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.Conne
oConn.Open();
//use stored proc name here
oCmd = new SqlCommand("ReadDRRIdCntr"
oCmd.CommandType = CommandType.StoredProcedur
// Get the data
reader = oCmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
id = Convert.ToDecimal(reader["
id += (decimal)1;
Session.Add("id", id);
}
if (oConn != null)
{
oConn.Close();
}
/// --------------- Write Updated Counter Back to Database
SqlConnection UpdateNewPHICntrConn;
UpdateNewPHICntrConn = new SqlConnection();
UpdateNewPHICntrConn.Conne
SqlCommand Updcmd;
UpdateNewPHICntrConn.Open(
Updcmd = new SqlCommand("UpdatePHICount
Updcmd.CommandType = CommandType.StoredProcedur
id = Convert.ToDecimal(Session.
Updcmd.Parameters.Add("@ID
Updcmd.Parameters.Clear();
UpdateNewPHICntrConn.Open(
// Write Updated Counter Data
Updcmd.ExecuteNonQuery();
if (UpdateNewPHICntrConn != null)
{
UpdateNewPHICntrConn.Close
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("UpdatePHICount er", oConn);
Updcmd.CommandType = CommandType.StoredProcedur e;
id = Convert.ToDecimal(Session. Contents[" id"].ToStr ing());
Updcmd.Parameters.Add("@ID ", SqlDbType.Decimal).Value = id;
Updcmd.Parameters.Clear();
UpdateNewPHICntrConn.Open( ); // this is the second time
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(
Updcmd = new SqlCommand("UpdatePHICount
Updcmd.CommandType = CommandType.StoredProcedur
id = Convert.ToDecimal(Session.
Updcmd.Parameters.Add("@ID
Updcmd.Parameters.Clear();
UpdateNewPHICntrConn.Open(
ASKER
Thank you. It is pretty late where I am and I guess my eyes are going cross on me:)
Thanks
Kenny
Thanks
Kenny
ASKER
UpdateNewPHICntrConn.Open(