CODEWITHSUNDEEP
c#
user2082630
user2082630

Reputation: 279

Invalid attempt to call Read when reader is closed on while(Reader.read()). why?

DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=NSIC;Initial Catalog=Dev;User ID=sa;Password=123456"))
{
    // SqlDataAdapter adapter = new SqlDataAdapter("Select * from Employee", connection);
    connection.Open();
    //adapter.Fill(ds);
    using (SqlCommand command = new SqlCommand("Select * from Employee", connection))
    {
        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (reader.Read()) //Error is here
            {
                var table = new DataTable();
                table.Load(reader);
                ds.Tables.Add(table);
            }
        }
    }
}
GridView1.DataSource = ds;

I am trying to retrieve data from data sql server.but error on while(Reader.read()).

Invalid attempt to call Read when reader is closed on while(Reader.read()).

why?

Upvotes: 0

Views: 82

Answers (2)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391446

The problem here is that you're using DataTable.Load inside the while-loop.

The while-loop basically says "While there are more rows", and then you call DataTable.Load that loads all the rows, and then go back to check for more.

Instead you can simply remove the while loop altogether:

using (SqlDataReader reader = command.ExecuteReader()) 
{
    var table = new DataTable();
    table.Load(reader);
    ds.Tables.Add(table);
}

Upvotes: 1

Irshad
Irshad

Reputation: 3131

I think using CommandBehavior.CloseConnection is messing with your reader.

Try using it like below;

    using (SqlConnection connection = new SqlConnection("Data Source=NSIC;Initial Catalog=Dev;User ID=sa;Password=123456"))
    using (SqlCommand command = new SqlCommand("Select * from Employee", connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader()) 
        {
            while (reader.Read())
            {
               var table = new DataTable();
               table.Load(reader);
               ds.Tables.Add(table);
            }
        }
    }

Upvotes: 0

Related Questions