Invalid attempt to call Read when reader is closed

Question

I'm kind of struggling trying to get this datagrid to bind. Everytime I run my code, I get an error message stating, "Invalid attempt to call Read when reader is closed". I don't see where I am closing my reader. Can you please help me? My code for loading the datagrid is below:

protected void LoadGrid()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["VTC"].ConnectionString;
                conn.Open();


                string sql = "select * from roi_tracking";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader sqlReader = cmd.ExecuteReader())
                    {

                        gridROI.DataSource = sqlReader;
                        gridROI.DataBind();

                        sqlReader.Dispose();
                        cmd.Dispose();
                    }
                }

            }
        }

Your using statement will close connection for further info check this thread, it might answer your question http://stackoverflow.com/questions/5516914/invalid-attempt-to-read-when-reader-is-closed — rs., Dec 20 '11 at 16:35
Same error in a different scenario http://stackoverflow.com/questions/6775136/invalid-attempt-to-call-read-when-reader-is-closed-error-for-lengthy-operatio — LCJ, Nov 20 '12 at 04:40

pseudocoder · Accepted Answer · 2011-12-20T17:26:38.760

You can't use a SqlDataReader as a DataSource for a DataGrid.

From MSDN:

A data source must be a collection that implements either the System.Collections.IEnumerable interface (such as System.Data.DataView, System.Collections.ArrayList, or System.Collections.Generic.List(Of T)) or the IListSource interface to bind to a control derived from the BaseDataList class.

Datasource property: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.basedatalist.datasource.aspx

SqlDataReader: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

A common methodology to bind the query results to your datagrid would be to use a SqlDataAdapter to fill a DataTable or DataSet and then bind that to your DataGrid.DataSource:

protected void LoadGrid()
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["VTC"].ConnectionString;
            conn.Open();

            string sql = "select * from roi_tracking";
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;

                adapter.Fill((DataTable)results);
                gridROI.DataSource = results;
            }

        }
    }

Thank You Pseudocoder and Mike! Your advice helped a lot and my code is now working. :) — JTRookie86, Dec 21 '11 at 16:20
@user1108273 My pleasure :) Don't forget to accept an answer if you feel your issue is resolved. — pseudocoder, Dec 21 '11 at 16:24

score 1 · Answer 2 · answered Dec 20 '11 at 18:48

In addition to what pseudocoder said, you wouldn't want to bind to a SqlDataReader anyway: the connection to the database will remain open so long as the reader instance exists.

You definitely want to deserialize the data into some other disconnected data structure so that you release the connection back into the pool as quickly as possible.

Invalid attempt to call Read when reader is closed

2 Answers2

Related