Cannot reuse open DataReader

Question

I am writing a c# windows forms application and i am coming accross the error mentioned above. I think this is happening because i am opening an sql connection and reader object in my main form load object and then doing the same thing again in another click event handler. I am not sure what i need to do in order to change my code / stop this from happening (or if this is even the problem). I have tried turning MARS on in my connection string but this did not fix the problem. Below is my code.

   namespace Excel_Importer
    {
public partial class Export : Form
{
    public Export()
    {
        InitializeComponent();
    }

    private void cmbItemLookup_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    private void Export_Load(object sender, EventArgs e)
    {
                                                                                           string connectionString = ConfigurationManager.ConnectionStrings ["dbconnection"].ConnectionString;

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("Select * From ExportItem", conn);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();

            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("ExportItemName", typeof(string));
            dt.Load(rdr);

            cmbItemLookup.DisplayMember = "ExportItemName";
            cmbItemLookup.DataSource = dt;
            conn.Close();
        }
    }

    private void btnLoad_Click(object sender, EventArgs e)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlCommand cmd2 = new SqlCommand("Select * from " + cmbItemLookup.Text, conn);
        SqlDataReader rdr2;
        rdr2 = cmd2.ExecuteReader();

        try
        {

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd2;
            DataTable dt = new DataTable();
            ada.Fill(dt);
            BindingSource bs = new BindingSource();
            bs.DataSource = dt;
            dgvExport.DataSource = bs;
            ada.Update(dt);
            conn.Close();
        }

        catch (Exception Ex)
        {
            MessageBox.Show(Ex.Message);
        }

    }


}

}

Answer 1

As the other answer points out, you must tidy up your clicked event code:

private void btnLoad_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connectionString) )
    {
      conn.Open();
      using(SqlCommand cmd2 = new SqlCommand("Select * from " + cmbItemLookup.Text, conn) )
      {
        using(SqlDataReader rdr2= cmd2.ExecuteReader())
        {

          try
          {

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd2;
            DataTable dt = new DataTable();
            ada.Fill(dt);
            BindingSource bs = new BindingSource();
            bs.DataSource = dt;
            dgvExport.DataSource = bs;
            ada.Update(dt);
            conn.Close();
          }
          catch (Exception Ex)
          {
            MessageBox.Show(Ex.Message);
          }
        }
      }
    }

}

Answer 2

You need to close your DataReaders. They implement the IDisposable interface, so the simplest thing is to put them inside a using block:

using (rdr = cmd.ExecuteReader())
{
  ..
} // .NET always calls Dispose() for you here

Actually, you pretty much have to dispose of everything that implements IDisposable, or problems gonna happen.