DataReader error when i us it in the second time

Question

var filterConditions = new[] {
       CreateSqlFilter("CIVILIDD", ID_No, selectCommand, false),


};

        string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;

        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["my"].ConnectionString))
        {
            selectCommand.Connection = connection;
            selectCommand.CommandText = filterCondition == null ? "SELECT * FROM _4" : "SELECT * FROM _4 WHERE " + filterCondition;
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            DataTable dataSource = new DataTable();
            adapter.Fill(dataSource);
            dataGridView2.DataSource = dataSource;

            using (SqlCommand command2 = new SqlCommand("SELECT * FROM [_4] WHERE CIVILIDD = @id", mycon))
            {

                command2.Parameters.AddWithValue("@id", ID_No.Text);

                SqlDataReader dr = command2.ExecuteReader();





                while (dr.Read())
                {

                    txtname1.Text = (dr["name1"].ToString());
                    txtname2.Text = (dr["name2"].ToString());
                    Governorate.Text = (dr["Governorate"].ToString());
                    City.Text = (dr["City"].ToString());
                    Block.Text = (dr["Block"].ToString());
                    Street.Text = (dr["Street"].ToString());
                    Avenue.Text = (dr["Avenue"].ToString());
                    House.Text = (dr["House"].ToString());
                    Floor.Text = (dr["Floor"].ToString());
                    flat.Text = (dr["flat"].ToString());
                }
            }

        }

i use this code to filter my database and i get that error when i run it in the first time it work good in the second time i get this error

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

in this line SqlDataReader dr = command2.ExecuteReader();

Answer 1

You will need to close the datareadre after you use it.

This is how your code should look like when you are not using Using()

Se the comment to understand

var filterConditions = new[] {
       CreateSqlFilter("CIVILIDD", ID_No, selectCommand, false),


};

        string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;

        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["my"].ConnectionString))
        {
            selectCommand.Connection = connection;
            selectCommand.CommandText = filterCondition == null ? "SELECT * FROM _4" : "SELECT * FROM _4 WHERE " + filterCondition;
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            DataTable dataSource = new DataTable();
            adapter.Fill(dataSource);
            dataGridView2.DataSource = dataSource;

            using (SqlCommand command2 = new SqlCommand("SELECT * FROM [_4] WHERE CIVILIDD = @id", mycon))
            {

                command2.Parameters.AddWithValue("@id", ID_No.Text);

                SqlDataReader dr = command2.ExecuteReader();





                while (dr.Read())
                {

                    txtname1.Text = (dr["name1"].ToString());
                    txtname2.Text = (dr["name2"].ToString());
                    Governorate.Text = (dr["Governorate"].ToString());
                    City.Text = (dr["City"].ToString());
                    Block.Text = (dr["Block"].ToString());
                    Street.Text = (dr["Street"].ToString());
                    Avenue.Text = (dr["Avenue"].ToString());
                    House.Text = (dr["House"].ToString());
                    Floor.Text = (dr["Floor"].ToString());
                    flat.Text = (dr["flat"].ToString());
                }
                 // Close and Dispose the datareader
                 dr.Close();
                 dr.Dispose();
            }

        }