There is already an open DataReader associated with this Command which must be closed first. In asp.net C#

Question

I have created 3 dropdown lists, and I want to search by Student name, Father name or Serial Number. When I selected the Student name or father name and write the name, it shows the data. But when I select search "by Serial number", an error occurs:

There is already an open DataReader associated with this Command which must be closed first.

Error Code

Search screenshot

I have tried to close the dataReader but it isn't working.

protected void btnSearch_Click(object sender, EventArgs e)
{
        string QueryStudent = "Select SerialNumber as [Serial No.], Name as [Student Name], FatherName as Father,Phone,Course, AdmissionDate as[Admission date], Status from tblStudents where Name= '" + txtSearch.Text + "'";
        string QueryFather = "Select SerialNumber as [Serial No.], Name as [Student Name], FatherName as Father,Phone,Course, AdmissionDate as[Admission date], Status from tblStudents where FatherName= '" + txtSearch.Text + "'";
        string QuerySerial = "Select SerialNumber as [Serial No.], Name as [Student Name], FatherName as Father,Phone,Course, AdmissionDate as[Admission date], Status from tblStudents where SerialNumber= '" + txtSearch.Text + "'";

        con.Open();

        using (var sqlCommand = new SqlCommand(QueryStudent, con))
        using (var sqlCommand2 = new SqlCommand(QueryFather, con))
        using (var sqlCommand3 = new SqlCommand(QuerySerial, con))
        {
            SqlDataReader reader = sqlCommand.ExecuteReader();

            if (DropDownList.SelectedItem.Value == "By Student Name")
            {
                if (!reader.HasRows)
                {
                    GrindSearch.Visible = false;
                    lblmsg.Visible = true;
                    lblmsg.Text = "No Record found";
                }
                else
                {
                    reader.Close();
                    GrindSearch.Visible = true;
                    lblmsg.Visible = false;
                    SqlDataAdapter da = new SqlDataAdapter(QueryStudent, con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    GrindSearch.DataSource = dt;
                    GrindSearch.DataBind();
                }
            }
            else if (DropDownList.SelectedItem.Value == "By Father Name")
            {
                reader.Close();

                SqlDataReader reader2 = sqlCommand2.ExecuteReader();

                if (!reader2.HasRows)
                {
                    GrindSearch.Visible = false;
                    lblmsg.Visible = true;
                    lblmsg.Text = "No Record found";
                }
                else
                {
                    reader2.Close();
                    GrindSearch.Visible = true;
                    lblmsg.Visible = false;
                    SqlDataAdapter da = new SqlDataAdapter(QueryFather, con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    GrindSearch.DataSource = dt;
                    GrindSearch.DataBind();
                }
            }
            else if (DropDownList.SelectedItem.Value == "By Serial Number")
            {
                SqlDataReader reader3 = sqlCommand3.ExecuteReader();

                if (!reader3.HasRows)
                {
                    GrindSearch.Visible = false;
                    lblmsg.Visible = true;
                    lblmsg.Text = "No Record found";
                }
                else
                {
                    reader3.Close();
                    GrindSearch.Visible = true;
                    lblmsg.Visible = false;
                    SqlDataAdapter da = new SqlDataAdapter(QueryFather, con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    GrindSearch.DataSource = dt;
                    GrindSearch.DataBind();
                }
            }
        }
    }
}

Answer 1

The "By Serial Number" clause does not start with the same reader.Close(); that "By Father Name" has.

But more in general, your management of that reader is fragile.
It should be managed in a using(...) {...} block.

And, related, the algorithmic structure looks far from efficient. When DropDownList.SelectedItem.Value == "By Serial Number" you have already executed the QueryStudent, never using its results.

Answer 2

You can fix this issue by adding MultipleActiveResultSets=true to the provider part of your connection string.