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

Question

ps : i tried to close connexions and datareaders but it didn't work , also tried the previous solutions here but still got the error i even separed the connexions and it didn't work as well what i'm trying to do here is to read an id from a table from the sql server and use that id to look for another data in another table but it always give me the closing connexion or data problem

SqlConnection cnx = new SqlConnection("data source = . ; database = tnt ; integrated security = true ; MultipleActiveResultSets=true");
    SqlConnection cnx2 = new SqlConnection("data source = . ; database = tnt ; integrated security = true ; MultipleActiveResultSets=true");



        SqlCommand cmd = new SqlCommand("select matricule from inscription where numformation = @n",cnx);
        cmd.Parameters.AddWithValue("@n", comboBox2.Text);
        SqlDataReader dr = cmd.ExecuteReader();

        List<string> hawhaw = new List<string>();
        while (dr.Read())
        {
            hawhaw.Add(dr[0].ToString());
        }

        cmd.Dispose();

        cnx2.Close();

        cnx2.Open();
        SqlCommand cmd2 = new SqlCommand("select * from person where matricule = @m", cnx2);
        foreach (var item in hawhaw)
        {
            cmd2.Parameters.Clear();
            cmd2.Parameters.AddWithValue("@m", item);
            SqlDataReader dr2 = cmd2.ExecuteReader();

            if (dr2.Read())
            {
                MessageBox.Show(dr2[0].ToString());
            }
        }

        cmd2.Dispose();
        cnx.Close();

The issue is not `dr`. You keep creating `dr2` inside the loop but you never close it. Plus you are using the same `Command` but keep changing the parameter. — CodingYoshi, Oct 11 '18 at 23:16
Connections, Commands, DataReaders (and others) **must** be disposed of when you are done with them. — Ňɏssa Pøngjǣrdenlarp, Oct 11 '18 at 23:18

score 1 · Accepted Answer · answered Oct 11 '18 at 23:20

most importantly, you can manage dispose and close operations. For this you should use using and will do this job on your behalf.

   List<string> hawhaw = new List<string>();

            using (SqlConnection connection = new SqlConnection("data source = . ; database = tnt ; integrated security = true ; MultipleActiveResultSets=true"))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand("select matricule from inscription where numformation = @n", connection))
                {
                    cmd.Parameters.AddWithValue("@n", comboBox2.Text);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        hawhaw.Add(dr[0].ToString());
                    }

                }
            }



            using (var connection = new SqlConnection("data source = . ; database = tnt ; integrated security = true ; MultipleActiveResultSets=true"))
            {
                connection.Open();

                foreach (var item in hawhaw)
                {
                    using (var cmd2 = new SqlCommand("select * from person where matricule = @m", connection))
                    {
                        cmd2.Parameters.AddWithValue("@m", item);
                        var dr2 = cmd2.ExecuteReader();
                        if (dr2.Read())
                        {
                            MessageBox.Show(dr2[0].ToString());
                        }
                    }

                }
            }

`SqlDataReader` is also IDisposable so `dr` and `dr2` should also be in `using` blocks. Please also read [Can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Richardissimo, Oct 12 '18 at 05:46

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

1 Answers1