How to successfully fill the datagridview - getting error invalid attempt to call read when reader is closed in C#

Question

I need a quick help. I am getting an error invalid attempt to call read when reader is closed when trying to add my databagridview from the reader.

the databases is a class that calls the database connection string. and the databaseColumn is a class that has all columns names.

error for column Time_Completed

what is the issues please help

Here is the code:

//datagridview, bindingsource, data_apapter global objects variables
        private DataGridView dataGridView = new DataGridView();
        private BindingSource bindingSource = new BindingSource();
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();


        //class objects
        Databases lemars = new Databases();
        Databases schuyler = new Databases();
        Databases detroitlakeskc = new Databases();


        public Form1()
        {
            InitializeComponent();
        }

        private void btn_Exit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void comboBox_Database_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox_Database.SelectedItem.ToString() == "LeMars21St")
            {
                GetDataToDataGridView();
            }
        }

        private void GetDataToDataGridView()
        {
            //prgBar_DataGridViewLoading
            DatabaseColumns Obj = new DatabaseColumns();
            String SqlcmdString = "Select * from dbo.AllInvoicesInReadyStatus";
            SqlDataReader reader;
            int i = 1;
            try
            {
                using (SqlConnection conn = new SqlConnection(lemars._LeMarsConnectionString))
                {

                    reader = null;
                    SqlCommand Sqlcmd = new SqlCommand(SqlcmdString, conn);
                    conn.Open();
                    reader = Sqlcmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        try
                        {
                            while (reader.Read())
                            {

                                Obj.Invoice = reader["invoice"].ToString();
                                Obj.Shipment = reader["shipment"].ToString();
                                Obj.Project = reader["Project"].ToString();
                                Obj.InvoiceDateTB = Convert.ToDateTime(reader["invoiceDateTB"]);
                                Obj.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
                                Obj.TypeName = reader["typeName"].ToString();
                                Obj.ExportedDate = Convert.ToDateTime(reader["exportedDate"]);
                                Obj.StatusName = reader["statusName"].ToString();
                                Obj.Total = Convert.ToDecimal(reader["total"]);
                                Obj.ImportStatus = reader["import_status"].ToString();
                                //DateTime dateFacturation;
                                int colIndex = reader.GetOrdinal("Time_Completed");
                                if (!reader.IsDBNull(colIndex))
                                    Obj.TimeCompleted = reader.GetDateTime(colIndex);
                                Obj.ErrorDescription = reader["ERROR_DESCRIPTION"].ToString();

                                //bindingSource.DataSource = reader;
                                DataTable dt = new DataTable();
                                dt.Load(reader);
                                dataGridView.DataSource = dt;

                                i++;
                            }
                        }
                        finally
                        {
                            reader.Close();
                        }
                          conn.Close();
                    }                  
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }



    }
}

I am getting the following error, so the datagridview never loads all the 12 columns I am trying to add. ERROR: invalid attempt to call read when reader is closed — FunCoder, Sep 18 '17 at 21:59
Either use a while read look or the dt.load method. Not both on the same reader. — Bob Vale, Sep 18 '17 at 22:49
Can you add a sample of code on that solution you are providing — FunCoder, Sep 19 '17 at 01:01

Tetsuya Yamamoto · Accepted Answer · 2017-09-19T02:22:46.293

DataTable.Load method automatically closes currently running DataReader instance, hence it will fail at the next iteration (the exception clearly said that the DataReader is already closed).

To fix this issue, call DataTable.Load immediately after ExecuteReader (i.e. after checking by HasRows) and you can iterate DataTable contents from that, as given in example below:

using (SqlConnection conn = new SqlConnection(lemars._LeMarsConnectionString))
{
     reader = null;
     SqlCommand Sqlcmd = new SqlCommand(SqlcmdString, conn);
     conn.Open();
     reader = Sqlcmd.ExecuteReader();
     if (reader.HasRows)
     {
         try
         {
             DataTable dt = new DataTable();
             dt.Load(reader);

             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 Obj.Invoice = dt.Rows[i]["invoice"].ToString();
                 Obj.Shipment = dt.Rows[i]["shipment"].ToString();
                 Obj.Project = dt.Rows[i]["Project"].ToString();

                 // other stuff
             }

             dataGridView.DataSource = dt;
         }
         finally
         {
             conn.Close();
         }
     }
}

Update 1:

Since one of your datetime column may contain DBNull.Value, you can check using either Convert.IsDBNull:

for (int i = 0; i < dt.Rows.Count; i++)
{
    if (!Convert.IsDBNull(dt.Rows[i]["Time_Completed"]))
    {
        Obj.TimeCompleted = Convert.ToDateTime(dt.Rows[i]["Time_Completed"]);
    }
}

Or with is operator with DBNull:

for (int i = 0; i < dt.Rows.Count; i++)
{
    if (!(dt.Rows[i]["Time_Completed"] is DBNull))
    {
        Obj.TimeCompleted = Convert.ToDateTime(dt.Rows[i]["Time_Completed"]);
    }
}

Alternatively you can use ternary operator and set DateTime.MinValue if the column has null value.

Related issues:

Error: Invalid attempt to call Read when reader is closed after the while loop?

C# - Invalid attempt to call Read when reader is closed

thanks man, that worked. Now I was able to add the data to my DataGridView. but there is 1 issue, one of the columns Time_Completed is a date column and most of the time is always going to be NULL, so for some reason I am getting an error. I will add the error above my first post. — FunCoder, Sep 19 '17 at 02:09
@AndresBryan29 Answer updated with example usage of `DBNull` checking to `Time_Completed` column. — Tetsuya Yamamoto, Sep 19 '17 at 02:23
Amazing bro, that worked nicely and beautifully, thanks for all your help. I learned a lot working on this. — FunCoder, Sep 19 '17 at 02:36
@AndresBryan29 You can accept an answer that solved your problem by clicking the "accept" mark beside the answer, indicating that you've found a best solution. It's not an obligation to do so. — Tetsuya Yamamoto, Sep 19 '17 at 02:43

How to successfully fill the datagridview - getting error invalid attempt to call read when reader is closed in C#

1 Answers1