I got an error..There is already an open DataReader associated with this Command which must be closed first. [Answered]RSS

• • Reply
  DDNaidu

  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";
  }
  }
  }
  }

• • Reply
  oned_gk

  All-Star

  52232 Points

  15529 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

  int i = cmd2.ExecuteNonQuery();

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  Mikesdotnett...

  All-Star

  189479 Points

  27479 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.

  DDNaidu

  SqlCommand cmd2 = new SqlCommand("Insert into Tbl_AttendnceInfo([Date],LogIntime,AttendenceID) Values(@Date,@LogIntime,@AttendenceID)", con);

  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();

  ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages | Learn Dapper
• • Reply
  DDNaidu

  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..!!!

• • Reply
  oned_gk

  All-Star

  52232 Points

  15529 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";
                      }
                  }
              }
          }
  

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  DDNaidu

  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();

• • Reply
  Nan Yu

  All-Star

  18735 Points

  3785 Posts

  Microsoft

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• • Reply
  DDNaidu

  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

• • Reply
  Mukesh_Kumar

  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,

  DDNaidu

  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();
      }
  }

  Hope this will help you.

  thanks

  My Blog | Csharp Corner | Stack Overflow | Twitter | Linked In
  
• • Reply
  oned_gk

  All-Star

  52232 Points

  15529 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

  rdr.Close(); //close reader
  int i = cmd2.ExecuteNonQuery(); //before execute this

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer