Re-open SqlDataReader

Question

My code throws the error

Invalid attempt to call Read when reader is closed.

I'm using SqlDataReader to read values from database, and that is my code:

while (rd.Read())
{
    string stateincharge = rd["stateincharge"].ToString();
    string email = rd["email"].ToString();
    cmd.Dispose();
    rd.Close();
    cmd = con.CreateCommand();
    cmd.CommandText = "str_procedure";
    cmd.Parameters.AddWithValue("@stateincharge", stateincharge);
    cmd.CommandType = CommandType.StoredProcedure;
    ad.SelectCommand = cmd;
    ad.Fill(ds);
    count = ds.Tables[0].Rows.Count;
    DataTable dt = new DataTable();
}

This runs in a loop in ASP.NET code-behind.

My problem is that I think I need to close SqlDatReader because of an error shown.

How can I again open sqlDataReader at end of the while loop?

Can you explain what you're trying to do? I think you're understanding is mixed up. And if you could please explain maybe we could point out a better way of doing it. — Preet Sangha
– Preet Sangha, Commented Aug 29, 2011 at 8:47
at which line do you receive 'close DataReader first error'? — Waqas
– Waqas, Commented Aug 29, 2011 at 8:48
Can you put the code that whole code because i think you are doing something wrong with the variables — Samir Adel
– Samir Adel, Commented Aug 29, 2011 at 8:53

abatishchev · Accepted Answer · 2011-08-29 09:04:29Z

// connection for reader
using (SqlConnection connection1 = new SqlConnection(connectionString))
using (SqlCommand command1 = new connection1.CreateCommand())
{
    command1.CommandText = commandText1;

    connection1.Open();
    using (SqlDataReader reader = command1.ExecuteReader())
    {
        // fill table in loop
        while (reader.Read())
        {
            string stateincharge = reader["stateincharge"].ToString();
            string email = reader["email"].ToString();

            // connection for adapter
            using (SqlConnection connection2 = new SqlConnection(connectionString))
            using (SqlCommand command2 = new connection2.CreateCommand())
            {
                command2.CommandText = commandText2;

                command2.Parameters.AddWithValue("@stateincharge", stateincharge);
                command2.Parameters.AddWithValue("@email ", email );

                connection2.Open();

                DataTable table = new DataTable();
                using (SqlDataApapter adapter = new SqlDataAdapter(command2))
                {
                    adapter.Fill(table);
                    // yield return table;
                }
            }
        }
    }
}

Adrita Sharma · Accepted Answer · 2018-01-31 08:10:21Z

1

You have to remove the line that closes the reader rd.Close(); because you are closing the reader inside the while loop then you try to access the reader again. Also, I think if you used a new SQL command and new adapter you will not receive this error.

edited Jan 31, 2018 at 8:10

Adrita Sharma

22.3k11 gold badges75 silver badges85 bronze badges

answered Aug 29, 2011 at 8:52

Samir Adel

2,5092 gold badges15 silver badges16 bronze badges

1 Comment

Roshan Over a year ago

using new connection for adapter

Lucero · Accepted Answer · 2011-08-29 08:53:29Z

0

Each connection can have at most one active command (which includes the "response", that is, the reader). You're trying to do a nested call to the DB and this requires that you either load the data completely (for instance into a DataTable) before looping, or that you uise a second connection for the nested calls to str_procedure.

answered Aug 29, 2011 at 8:53

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Comments

Steve Woods · Accepted Answer · 2011-08-29 08:58:24Z

0

while (rd.Read())
    {
        string stateincharge = rd["stateincharge"].ToString();
        string email = rd["email"].ToString();
        cmd.Dispose();
        cmd = con.CreateCommand();
        cmd.CommandText = "str_procedure";
        cmd.Parameters.AddWithValue("@stateincharge", stateincharge);
        cmd.CommandType = CommandType.StoredProcedure;
        ad.SelectCommand = cmd;
        ad.Fill(ds);
        count = ds.Tables[0].Rows.Count;
        DataTable dt = new DataTable();
}
rd.Close();

answered Aug 29, 2011 at 8:58

community wiki

Steve Woods

1 Comment

Roshan Over a year ago

There is already an open DataReader associated with this Command which must be closed first.

Collectives™ on Stack Overflow

Re-open SqlDataReader

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related