GridView RowCreated: Invalid attempt to call FieldCount when reader is closed

Question

I am getting this error when the gridview is created with an empty dataset. What I am trying to do is populate a dropdown list in the EmptyDataTemplate. From reading other posts the error is caused by use SqlDataReader bind gridview after SqlConnection obejct closed. However, doesn't row created fire after the gridview row is populated?

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.EmptyDataRow)
    {
        string connectionString3 = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        DropDownList ddl = (DropDownList)e.Row.FindControl("EOCEmpty");

        using (SqlConnection conn3 = new SqlConnection(connectionString3))
        {
            SqlCommand cmd3 = new SqlCommand("SELECT DISTINCT GLAccountEOC, EOCDescription FROM Acct_GLAccount WHERE CostCenter = @CostCenter Order By EOCDescription", conn3);
            cmd3.Parameters.Add("@CostCenter", System.Data.SqlDbType.Int);
            cmd3.Parameters["@CostCenter"].Value = "3215";

            try
            {
                conn3.Open();
                SqlDataReader cmdreader3 = cmd3.ExecuteReader();
                ddl.DataSource = cmdreader3;
                ddl.DataValueField = "GLAccountEOC";
                ddl.DataTextField = "EOCDescription";
                ddl.DataBind();
                cmdreader3.Close();
            }

            finally
            {
                conn3.Close();
            }
        }
    }
}

well you are binding the reader then closing it. also you could check `HasRows` first to determine if you want to bind to the reader — Nkosi, Jan 02 '19 at 17:28
@Nkosi In my gridview?, yes I am using HasRows to determine it. — Dan Nick, Jan 02 '19 at 17:37

score 1 · Accepted Answer · answered Jan 03 '19 at 03:21

The problem seem occur in these lines:

SqlDataReader cmdreader3 = cmd3.ExecuteReader();
ddl.DataSource = cmdreader3;

As you should know, ExecuteReader() method builds SqlDataReader instance (which is forward-only stream), and the connection seem to be closed when trying to access the reader. Consider using SqlDataReader.Read() method to open the reader and advances to first record, or load the contents into in-memory DataSet/DataTable and use it as GridView's data source instead.

Here is an example to load SqlDataReader contents inside DataTable:

var dt = new DataTable();

using (SqlConnection conn3 = new SqlConnection(connectionString3))
{
    SqlCommand cmd3 = new SqlCommand("SELECT DISTINCT GLAccountEOC, EOCDescription FROM Acct_GLAccount WHERE CostCenter = @CostCenter Order By EOCDescription", conn3);
    cmd3.Parameters.Add("@CostCenter", System.Data.SqlDbType.Int);
    cmd3.Parameters["@CostCenter"].Value = "3215";

    try
    {
        conn3.Open();
        SqlDataReader cmdreader3 = cmd3.ExecuteReader();
        if (cmdreader3.HasRows)
        {
            dt.Load(cmdreader3);
            ddl.DataSource = dt; // use DataTable instead of SqlDataReader
            ddl.DataValueField = "GLAccountEOC";
            ddl.DataTextField = "EOCDescription";
            ddl.DataBind();
        }
        cmdreader3.Close();
    }

    finally
    {
        conn3.Close();
    }
}

If the problem still persists, use RowDataBound event handler instead of RowCreated to populate dropdownlist value (see difference between RowCreated & RowDataBound event).

Similar issue:

asp.net Invalid attempt to FieldCount when reader is closed error

Thank you I used RowDataBound instead of RowCreated and it worked. — Dan Nick, Jan 03 '19 at 13:20

GridView RowCreated: Invalid attempt to call FieldCount when reader is closed

1 Answers1