SqlDataAdapter, SqlDataReader remains open

[scotitpulsa]

I'm using an SqlDataAdapter to get data from a db. I open a connection, query out data using the adapter, then dispose of the adapter, then try to query out more data using another adapter and i get the following error...

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

any thoughts to remedy this issue?

 

[dvannoy]

you also need to close your da

.close

 

[scotitpulsa]

.Close() is not a method of the SqlDataAdapter...

i.e

SqlDataAdapter myAdapter = new SqlDataAdapter(
"SELECT * FROM TABLE "+
" ", myConnection);
DataSet ds = new DataSet();
myAdapter.Fill(ds);
myAdapter.Close(); <--- DOES NOT EXIST *****
myAdapter.Dispose();

 

[jbenson001]

Is your datareader closed?

 

[vbkris]

i dont see any datareader in the code, however a DataAdapter opens a DataReader in the background, but once u dispose the adapter the reader should close...

Known is handfull, Unknown is worldfull

 

[scotitpulsa]

I'm not instantiating an SqlDataReader anywhere...
I'm guessing I'm getting this error because in the SqlDataAdapter class, a DataReader is used....

 

[dvannoy]

I open a connection, query out data using the adapter...

the message is telling you to close your connection.

all connections and readers MUST be closed after there done.

Youopen a connection, grab your records then close connection and readers you used to grab those records.

 

[dvannoy]

add

myConnection.close

after
myadapter.dispose

 

[scotitpulsa]

Doesn't opening a connection every time you want to get data from a DB use a lot of resources? Basically that is saying that in one case of a switch statement, if I query out of the DB 5 times, I have to open and close 5 connections...

 

[jbenson001]

When using a dataadapter, you do not have to explicitly open and close the connection, the adapter does it automatically.

 

[dvannoy]

well,, somewhere in your code you have either a connection open or a reader open. I would bet it's a connection.

why would you leave a connection open?? if your done close it. even you if open make five requests to the DB, you get it and get out. I would never leave connections open to any of my DB's..

 

[jbenson001]

This is from VS Help"

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

 

[scotitpulsa]

Per the last post, couldn't it be assumed that by disposing of the SqlDataAdapter, the SqlDataReader would be closed and disposed of as well...thus making the connection available to another SqlDataAdapter?

 

[vbkris]

exactly, unless u r opening one on ur own...

Known is handfull, Unknown is worldfull

 

[dvannoy]

you mentioned making five requests to the db.. are you doing multiple inserts or something like that, or was that just a comment?

you search that error on the web and most of the hits you'll get refer to accessing multi tables or doing multiple inserts.

 

[scotitpulsa]

it was just a comment...but theoretically that is what you would have to do given the statements above, regardless of whether you are inserting or merely selecting...