after put the code,again got this error..,The Connection was not closed..the connection current state is open
in cmd2
cmd2.Parameters.AddWithValue("@Logintime", currenttime);
Line 64: cmd2.Parameters.AddWithValue("AttendenceID", HiddenValue.Text);
Line 65: con.Open();
Line 66: int i = cmd2.ExecuteNonQuery();
Line 67: con.Close();
The connection probably have not closed last time . Then you could try to use different Connection object when access the databsase like :
public void run_command(string query)
{
using(var con = new SqlConnection(connectionString))
using(var cmd = new SqlCommand(query, con))
{
con.Open();
// ...
} // close not needed since dispose also closes the connection
}
I got an error..There is already an open DataReader associated with
Best thing when you work with datareader, close the connection immediately, so always use reader.close(); and to check connection is open or not with while loop is the best practice as microsoft recommended it. Please find the following
code snippet for best way to handle connection with datareader.
private static void ReadOrderData(string connectionString)
{
string queryString =
"SELECT OrderID, CustomerID FROM dbo.Orders;";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
}
// Call Close when done reading.
reader.Close();
}
}
Member
44 Points
82 Posts
I got an error..There is already an open DataReader associated with this Command which must be cl...
Aug 01, 2016 06:51 AM|DDNaidu|LINK
But Here I used Only one Datareader..But its Showing Error
String connection = ConfigurationManager.ConnectionStrings["EAMS"].ConnectionString;
using (SqlConnection con = new SqlConnection(connection))
{
foreach (DataListItem lst in HiddenField.Items)
{
con.Open();
Label HiddenValue = (Label)lst.FindControl("HiddenValue");
SqlCommand cmd = new SqlCommand("Select Date,AttendenceID from tbl_attendnceinfo Where Date=@Date and AttendenceID=@AttendenceID", con);
DateTime date = Convert.ToDateTime(System.DateTime.UtcNow.ToString("dd/MM/yyyy"));
cmd.Parameters.AddWithValue("@Date", date);
cmd.Parameters.AddWithValue("AttendenceID", HiddenValue.Text);
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
Label3.Text = "Your Login Time Already Saved..";
}
else {
SqlCommand cmd2 = new SqlCommand("Insert into Tbl_AttendnceInfo([Date],LogIntime,AttendenceID) Values(@Date,@LogIntime,@AttendenceID)", con);
DateTime date1 = Convert.ToDateTime(System.DateTime.UtcNow.ToString("dd/MM/yyyy"));
cmd2.Parameters.AddWithValue("@Date", date1);
DateTime currenttime = Convert.ToDateTime(System.DateTime.UtcNow.AddHours(5.5).ToString("hh:mm tt"));
cmd2.Parameters.AddWithValue("@Logintime", currenttime);
cmd2.Parameters.AddWithValue("AttendenceID", HiddenValue.Text);
int i = cmd.ExecuteNonQuery();
if (i != 0)
{
Label3.Text = "Your Log In Time Saved as:" + System.DateTime.UtcNow.AddHours(5.5).ToString("hh:mm tt");
}
else
{
Label3.Text = "Time Not Saved..!!Some Conflict occured..Contact Admin Managament";
}
}
}
}
All-Star
52683 Points
15724 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 01, 2016 06:58 AM|oned_gk|LINK
Suwandi - Non Graduate Programmer
All-Star
194527 Points
28084 Posts
Moderator
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 01, 2016 07:02 AM|Mikesdotnetting|LINK
DataReaders have exclusive access to their connection, which means that you cannot reuse the connection until the reader has been closed.
The bit above needs to be changed to use a different connection object, or you need to close the connection and then open it again:if (sdr.HasRows) { Label3.Text = "Your Login Time Already Saved.."; con.Close(); } else { SqlCommand cmd2 = new SqlCommand("Insert into Tbl_AttendnceInfo([Date],LogIntime,AttendenceID) Values(@Date,@LogIntime,@AttendenceID)", con); con.Open();Member
44 Points
82 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 01, 2016 07:03 AM|DDNaidu|LINK
i Put cmd2 but Showing again same Error..!!!
All-Star
52683 Points
15724 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 01, 2016 07:14 AM|oned_gk|LINK
try this
String connection = ConfigurationManager.ConnectionStrings["EAMS"].ConnectionString; using (SqlConnection con = new SqlConnection(connection)) { foreach (DataListItem lst in HiddenField.Items) { con.Open(); Label HiddenValue = (Label)lst.FindControl("HiddenValue"); SqlCommand cmd = new SqlCommand("Select Date,AttendenceID from tbl_attendnceinfo Where Date=@Date and AttendenceID=@AttendenceID", con); DateTime date = Convert.ToDateTime(System.DateTime.UtcNow.ToString("dd/MM/yyyy")); cmd.Parameters.AddWithValue("@Date", date); cmd.Parameters.AddWithValue("AttendenceID", HiddenValue.Text); SqlDataReader sdr = cmd.ExecuteReader(); Boolean hasrow = sdr.HasRows; con.Close(); if(hasrow) { Label3.Text = "Your Login Time Already Saved.."; } else { SqlCommand cmd2 = new SqlCommand("Insert into Tbl_AttendnceInfo([Date],LogIntime,AttendenceID) Values(@Date,@LogIntime,@AttendenceID)", con); DateTime date1 = Convert.ToDateTime(System.DateTime.UtcNow.ToString("dd/MM/yyyy")); cmd2.Parameters.AddWithValue("@Date", date1); DateTime currenttime = Convert.ToDateTime(System.DateTime.UtcNow.AddHours(5.5).ToString("hh:mm tt")); cmd2.Parameters.AddWithValue("@Logintime", currenttime); cmd2.Parameters.AddWithValue("AttendenceID", HiddenValue.Text); con.Open(); int i = cmd.ExecuteNonQuery(); con.Close(); if (i != 0) { Label3.Text = "Your Log In Time Saved as:" + System.DateTime.UtcNow.AddHours(5.5).ToString("hh:mm tt"); } else { Label3.Text = "Time Not Saved..!!Some Conflict occured..Contact Admin Managament"; } } } }Suwandi - Non Graduate Programmer
Member
44 Points
82 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 01, 2016 07:43 AM|DDNaidu|LINK
after put the code,again got this error..,The Connection was not closed..the connection current state is open
in cmd2
cmd2.Parameters.AddWithValue("@Logintime", currenttime); Line 64: cmd2.Parameters.AddWithValue("AttendenceID", HiddenValue.Text); Line 65: con.Open(); Line 66: int i = cmd2.ExecuteNonQuery(); Line 67: con.Close();All-Star
18815 Points
3831 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 02, 2016 06:19 AM|Nan Yu|LINK
Hi DDNaidu ,
The connection probably have not closed last time . Then you could try to use different Connection object when access the databsase like :
public void run_command(string query) { using(var con = new SqlConnection(connectionString)) using(var cmd = new SqlCommand(query, con)) { con.Open(); // ... } // close not needed since dispose also closes the connection }Best Regards,
Nan Yu
Member
44 Points
82 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 02, 2016 07:31 AM|DDNaidu|LINK
Thank You Nan Yu
Contributor
4420 Points
874 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 02, 2016 07:53 AM|Mukesh_Kumar|LINK
Hi,
Best thing when you work with datareader, close the connection immediately, so always use reader.close(); and to check connection is open or not with while loop is the best practice as microsoft recommended it. Please find the following code snippet for best way to handle connection with datareader.
private static void ReadOrderData(string connectionString) { string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;"; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); // Call Read before accessing data. while (reader.Read()) { Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1])); } // Call Close when done reading. reader.Close(); } }Hope this will help you.
thanks
All-Star
52683 Points
15724 Posts
Re: I got an error..There is already an open DataReader associated with this Command which must b...
Aug 02, 2016 08:56 AM|oned_gk|LINK
Suwandi - Non Graduate Programmer