I'm getting this error:
There is already an open DataReader associated with this Command which must be closed first.
I don't know where is the problem. It's closed but still says it's open. Please help!
The first command is to get all employees that have vacation between two dates.
The second command I am using it to retrieve dates by ID.
Here is my code:
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
SqlCommand cmd = new SqlCommand(" SELECT distinct E.EmployeeId, E.FirstName FROM Employee E INNER JOIN Vacation V ON E.EmployeeId = V.EmployeeId " +
" WHERE ((V.Dates >= @Start AND V.Dates <= @End) ) ", con);
cmd.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());
cmd.Parameters.AddWithValue("@End", (Calendar1.SelectedDates[Calendar1.SelectedDates.Count - 1]).Date.ToShortDateString());
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Response.Write((dr[1]).ToString() + " "); // Check if retrieves employee name
// Now by Id I want to get all dates belong to specific employee
SqlCommand cmd2 = new SqlCommand("SELECT V.Dates FROM Vacation V " +
" WHERE ((V.Dates >= @Start AND V.Dates <= @End) ) ", con);
cmd2.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());
cmd2.Parameters.AddWithValue("@End", (Calendar1.SelectedDates[Calendar1.SelectedDates.Count - 1]).Date.ToShortDateString());
cmd2.Parameters.AddWithValue("@EmployeeId", Convert.ToInt32(dr[0]));
using (SqlDataReader dr2 = cmd2.ExecuteReader())
{
while (dr2.Read())
{
Response.Write(Convert.ToDateTime(dr2[0]));
}
}
Response.Write("<br/>");
}
GridView7.DataSource = cmd.ExecuteReader();
GridView7.DataBind();
}
con.close();
}