first
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
change to
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
second
SqlCommand cmd5 = new SqlCommand();
SqlDataReader dr5;
cmd5.Connection = connection;
cmd5.CommandText = "select facultycode from fAllotmentTab where RoomNo='" + ddlSemister.SelectedValue + "' and examdate='" + ddlDate.SelectedValue + "' and esession='" + ddlSession.SelectedValue + "'";
to
string selecttext = "select facultycode from fAllotmentTab where RoomNo=@RoomNo and examdate=@ExamDate and esession=@Session";
SqlCommand cmd5 = new SqlCommand(selecttext, connection);
cmd5.parameters.add("@RoomNo"ddlSemister.SelectedValue);
cmd5.parameters.add("@ExamDate", ddlDate.SelectedValue);
cmd5.parameters.add("@Session", ddlSession.SelectedValue);
There could be more optimizations in code but these things were absolute blunders so i pointed them.