473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Strange Datareader error

I've got an error that I simply cannot locate:

I've got a form in which I use a datareader object to read information from
a db. After the read, I close the reader and I dispose of both the reader
and the command object (but don't close the connection which is public in
the solution).

The first time i open my form from a parent form, there is no problem.
Everything works fine. I then close down my form, and I dispose of the form.

Then I open a new instance of the form using the new keyword, and then I get
the error when the command object tries to execute the datareader:

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

Why the heck? I'm 100% sure that the datareader is closed after use, and
I've even disposed of both the reader object, the command object and the
form itself.

What's going on?

From MSDN I can see that I'm not the first person with that problem, but I
cannot find any solution to it. There is a workaround posted (also on MSDN)
simply saying: Close your connection and reopen it. But I cannot do that,
because that connection is used elsewhere in the solution....

Any input would be appreciated,
Johnny J.


May 2 '07 #1
3 3365
The idea of having some sort of "global" open connection is a design flaw.
Best practices coding tecnique dictates that you should open a connection
just prior to use, perform your work, and close the connection, allowing it
to go back to the ADO.NET connection pool, which is designed specifically for
this purpose.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Johnny Jörgensen" wrote:
I've got an error that I simply cannot locate:

I've got a form in which I use a datareader object to read information from
a db. After the read, I close the reader and I dispose of both the reader
and the command object (but don't close the connection which is public in
the solution).

The first time i open my form from a parent form, there is no problem.
Everything works fine. I then close down my form, and I dispose of the form.

Then I open a new instance of the form using the new keyword, and then I get
the error when the command object tries to execute the datareader:

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

Why the heck? I'm 100% sure that the datareader is closed after use, and
I've even disposed of both the reader object, the command object and the
form itself.

What's going on?

From MSDN I can see that I'm not the first person with that problem, but I
cannot find any solution to it. There is a workaround posted (also on MSDN)
simply saying: Close your connection and reopen it. But I cannot do that,
because that connection is used elsewhere in the solution....

Any input would be appreciated,
Johnny J.


May 2 '07 #2
Hi,

"Johnny Jörgensen" <jo**@altcom.sewrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
I've got an error that I simply cannot locate:

I've got a form in which I use a datareader object to read information
from a db. After the read, I close the reader and I dispose of both the
reader and the command object (but don't close the connection which is
public in the solution).
This is a BAD design, a Connection should be open & closed on each
operation, open it as later as possible, close it as soon as possible.

You could make the connection string public in the solutioh though.
Not only that you can have a class that encapsulate the access to the DB.
The only problem with this approach (that I know no solution to) is that a
DataReader needs an open connection, so it's responsability of the calling
code to dispose it.
You can "help" to close the connection by using SqlCommand.ExecuteReader(
CommandBehavior.CloseConnection);

An alternaty is to use a dataset instead of a datareader, in this way the
connection is never open outside the "data access" class.
May 2 '07 #3

As others have said.

Open LATE
Close EARLY

Get it back to the pool.

//Close your connection and reopen it. But I cannot do that,
because that connection is used elsewhere in the solution....
//
Not a good idea.

Typical "using an IDataReader code" looks like

someCollection coll = new SomeCollection();

try
{
while (dataReader.Read())
{
if (!(dataReader.IsDBNull(0)))
{

coll.Add( //create an object using the dataReader
values // );
}
}
return coll;
}
//no catch here... see
http://blogs.msdn.com/brada/archive/...03/274718.aspx
finally
{
if (null!=dataReader)
{
try
{
dataReader.Close();
}
catch
{
//hmm
}
}
}

"Johnny Jörgensen" <jo**@altcom.sewrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
I've got an error that I simply cannot locate:

I've got a form in which I use a datareader object to read information
from
a db. After the read, I close the reader and I dispose of both the reader
and the command object (but don't close the connection which is public in
the solution).

The first time i open my form from a parent form, there is no problem.
Everything works fine. I then close down my form, and I dispose of the
form.
>
Then I open a new instance of the form using the new keyword, and then I
get
the error when the command object tries to execute the datareader:

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

Why the heck? I'm 100% sure that the datareader is closed after use, and
I've even disposed of both the reader object, the command object and the
form itself.

What's going on?

From MSDN I can see that I'm not the first person with that problem, but I
cannot find any solution to it. There is a workaround posted (also on
MSDN)
simply saying: Close your connection and reopen it. But I cannot do that,
because that connection is used elsewhere in the solution....

Any input would be appreciated,
Johnny J.


May 2 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Abhishek Srivastava | last post by:
Hello All, I wrote a program in which I have a OleDbConnection which I open in the contructor and close in the Destructor. When I run the program I get the following error Unhandled...
1
by: Hash | last post by:
Hi All, I have a small asp.net app developed in C#, where I am using COM components and Activepdf toolkit to create pdf files for given documentID's. I am using DataReader to get the docID's...
4
by: Maziar Aflatoun | last post by:
Hi everyone, I am working on the 'Delete' section of my program. What I need to do is query my database and for every ID that it finds I want to remove a file+ID.jpg from my file folder and...
14
by: Bihn | last post by:
I was reading about datareader which is said to be slimmer & faster then dataset. Since the datareader have to go fetching the dat from the database every time it need it, the data it gets then...
4
by: Shapper | last post by:
Hello, I have created a datareader function in a asp.net/vb web site. The datareader returns only one record with 2 fields: and In the same aspx.vb I want to use this values as follows:...
20
by: fniles | last post by:
I am using VB.NET 2003, SQL 2000, and SqlDataReader. As I read data from tblA, I want to populate tblB. I use SQLDataReader for both tables. I do not use thread. When I ExecuteReader on tblB, I...
10
by: jimmy | last post by:
Hi again, sorry for posting two questions so close together but im working on a school project which is due in soon and running into some difficulties implementing the database parts. I have the...
0
by: Teo | last post by:
Hi!! I have been trying to fix an error I keep getting on my VB.NET code. For some reason, I get an error saying "Invalid attempt to access a field before calling Read()" everytime. As you can see...
1
by: =?UTF-8?B?QXJuZSBWYWpow7hq?= | last post by:
JB wrote: if(!reader.IsDBNull(swimcol)) { // use (string)reader } else { // use some default value maybe null
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.