Invalid attempt to Read when reader is closed - Help needed

[Karthikeyan R]

Hi,

 

I am getting "Invalid attempt to Read when reader is closed" error in one of my pages.I have used the following method in my class file,

public

class ExecuteQuery

{

OleDbConnection conn;

OleDbCommand cmd;

DataTable dt;

OleDbDataAdapter da;

OleDbDataReader dr;

 

public ExecuteQuery()

{

conn =

new OleDbConnection();

conn.ConnectionString =

DBConnection.GetConnectionString();

}

public

OleDbDataReader ExecuteReader(string ReaderQuery)

{

cmd =

new OleDbCommand(ReaderQuery, conn);

conn.Open();

dr = cmd.ExecuteReader(

CommandBehavior.CloseConnection);

conn.Close();

return dr;

}

}

 

In the aspx.cs file I have used the following code,

 

private void ddlfill()

{

try

{

ddlquestion.Items.Add(

"");

ddlquestion.SelectedIndex = 0;

string query = "select Hint_Question from Tbl_Hintquestion order by Hint_No ASC";

bool abc = EQuery.ExecuteReader(query).Read();

while (abc == true)

{

ddlquestion.Items.Add(dr[

"Hint_Question"].ToString());

abc = EQuery.ExecuteReader(query).Read();

}

}

catch (Exception EXE)

{

Alert.Show(EXE.Message);

}

}

Can some one help me out to solve this problem?
--
Thanks & Regards,
Karthikeyan

[Benj Nunez]

I noticed in your code you have these:

*conn.Close();*

*return** dr;*

You've closed the connection then returned the "dr" (your datareader
object)
to the caller. Closing the connection killed the datareader as well.

Using a DataSet would help you close the connection object but allows
you to keep
the records that you queried to the DataSet. So instead of using
OleDbDataReader,
why not use DataSet? I suggest you re-write the ExecuteReader()
routine to use it.

Cheers!

Benj

On Jan 2, 8:31 pm, Karthikeyan R <karthi...@gmail.com> wrote:
> Hi,
>
> I am getting *"Invalid attempt to Read when reader is closed"* error in one

> of my pages.I have used the following method in my class file,
>
> public class ExecuteQuery
>
> {
>
> OleDbConnection conn;
>
> OleDbCommand cmd;
>
> DataTable dt;
>
> OleDbDataAdapter da;
>
> OleDbDataReader dr;
>
> public ExecuteQuery()
>
> {
>
> conn = new OleDbConnection();
>
> conn.ConnectionString = DBConnection.GetConnectionString();
>
> }
>

> *public** OleDbDataReader ExecuteReader(string** ReaderQuery)*
>
> *{ *
>
> *cmd = **new OleDbCommand**(ReaderQuery, conn); *
>
> *conn.Open();*
>
> *dr = cmd.ExecuteReader(**CommandBehavior**.CloseConnection); *
>
> *conn.Close();*
>
> *return** dr;*
>
> *}*

[sushma sushma]

Check the red marked code karthikeya.

OleDbDataReader ExecuteReader(string ReaderQuery)

{

cmd =

new OleDbCommand(ReaderQuery, conn);

conn.Open();

dr = cmd.ExecuteReader(

CommandBehavior.CloseConnection);

// Try this one Karthik

return dr;

then close connection;

conn.Close();

return dr;

--
Thanks,

Sushma

Email: sush...@gmail.com

[Benj Nunez]

"*// Try this one Karthik*
*
*
*return dr;*
*then close connection;
*
** "

Hi.

I think if you do this, you'll get a compiler error/
warning:"Unreachable code detected.".
As far as I can tell, any statement below the return statement
produces this
error/warning except when you program in Borland Delphi. ;)

Cheers!

Benj

On Jan 4, 8:45 am, sushma sushma <sushma...@gmail.com> wrote:
> *

> Check the red marked code karthikeya.
>

> OleDbDataReader ExecuteReader(string** ReaderQuery)*
>
> *{*
>
> *cmd =*
> *new OleDbCommand**(ReaderQuery, conn);*
>
> *conn.Open();*
>
> *dr = cmd.ExecuteReader(*
> *CommandBehavior**.CloseConnection);*
> *

> *
> *// Try this one Karthik*
> *
> *
> *return dr;*
> *then close connection;
> *
> **

>
> *conn.Close();*
>
> *return** dr;*
>
>
>

> On Sat, Jan 2, 2010 at 7:31 AM, Karthikeyan R <karthi...@gmail.com> wrote:
> > Hi,
>

> > I am getting *"Invalid attempt to Read when reader is closed"* error in

> > one of my pages.I have used the following method in my class file,
>
> > public
> > class ExecuteQuery
>
> > {
>
> > OleDbConnection conn;
>
> > OleDbCommand cmd;
>
> > DataTable dt;
>
> > OleDbDataAdapter da;
>
> > OleDbDataReader dr;
>
> > public ExecuteQuery()
>
> > {
>
> > conn =
> > new OleDbConnection();
>
> > conn.ConnectionString =
> > DBConnection.GetConnectionString();
>
> > }
>

> > *public*

> > * OleDbDataReader ExecuteReader(string** ReaderQuery)*
>
> > *{ *
>
> > *cmd = *
> > *new OleDbCommand**(ReaderQuery, conn); *
>
> > *conn.Open();*
>
> > *dr = cmd.ExecuteReader(*
> > *CommandBehavior**.CloseConnection); *
>
> > *conn.Close();*
>
> > *return** dr;*
>
> > *}*
> > }
>

> > In the aspx.cs file I have used the following code,
>
> > private void ddlfill()
>
> > {
>
> > try
>
> > {
>
> > ddlquestion.Items.Add(
> > "");
>
> > ddlquestion.SelectedIndex = 0;
>
> > string query = "select Hint_Question from Tbl_Hintquestion order by
> > Hint_No ASC";
>
> > bool abc = EQuery.ExecuteReader(query).Read();
>
> > while (abc == true)
>
> > {
>
> > ddlquestion.Items.Add(dr[
> > "Hint_Question"].ToString());
>
> > abc = EQuery.ExecuteReader(query).Read();
>
> > }
>
> > }
>
> > catch (Exception EXE)
>
> > {
>
> > Alert.Show(EXE.Message);
>
> > }
>
> > }
> > Can some one help me out to solve this problem?
> > --
> > Thanks & Regards,
> > Karthikeyan
>
> --
> Thanks,
>
> Sushma
>

> Email: sushma...@gmail.com

[Karthikeyan R]

Thanks for your replies. 

 

Sushma,

 

As Benj suggested it produced "Unreachable code detected." since return should be last statement in a method (as per my understanding). 

 

I have changed the logic and used a datatable to fill the dropdownlist as I found that would be an efficient way rather than using datareader.

 

I have changed the code in the Execute reader method like this,

public OleDbDataReader ExecuteReader1(string ReaderQuery)

{

OleDbDataReader reader = null;

try

{

conn =

new OleDbConnection();

conn.ConnectionString =

DBConnection

.GetConnectionString();

cmd =

new OleDbCommand(ReaderQuery, conn);

this.conn.Open();

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

catch (Exception ex)

{

if (handleErrors)

strLastError = ex.Message;

else

throw;

}

return reader;

}

It works good in few scenarios, whereas if I call a method to EXECUTESCALAR after the EXECUTEREADER method it gives the folllowing error, "The connection was not closed. The connection's current state is open."

[Karthikeyan R]

Just fixed the problem by closing the reader in the aspx.cs page.

 private void gethintno()

    {

        try

        {            

            string squery = "select Hint_No from Tbl_Hintquestion where Hint_Question = \'" + (Fixquotes(lblhintquestion.Text.Trim())) + "\'";

            IDataReader dr;

            dr = EQuery.ExecuteReader(squery);                 

            if (dr.Read() == true)

            {

                ihintno = int.Parse(dr["Hint_No"].ToString());

            }

            dr.Close();   

        }

        catch (Exception EXE)

        {

            Alert.Show(EXE.Message);

[vinay kumar]

add finally block and then close the reader

[Karthikeyan R]

Oh ok.  I will make that change Vinay.  Thanks for your comments.

[Greg Hile]

Hi all!

 

When programming I have the program performing some tasks that take a while.

 

How can I make my program more responsive?

 

Would learning threading help?

 

 

 

Greg

[Benj Nunez]

Yes. The easiest is to use the BackgroundWorker component. Give it a
try.

Cheers!

Benj