DataReader built into Data Adapter?

[Kenbuddy]

I have a button that clears and requeries a DataSet (dsInvoices) based on parameters the user enters into two combo boxes. When I click the button, I get the following infamous error:

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

The odd thing is that since I have this error trapped, after continuing, the program works perfectly. I am aware that a DataReader hogs up a connection and must be explicitly closed. However, the problem is that I have not explicitly created nor opened a DataReader anywhere in the code. I do have some other multi-table DataSets that are open and were populated with a separate Data Adapter for each table in the DataSet.

I have read that when using the .fill method of a Data Adapter, it automatically creates a DataReader with which to populate the DataSet. But since this all happens "behind the scenes," I would expect that the Data Adapter would know to close the DataReader automatically, right? Is there anything else I have to do? Is there any way to manually close a DataReader that was created by a Data Adapter?

Thanks.

[Pirate]

DataAdapter doesn't require any DataReader object . If you are not executing SQL Commands , don't use it then . While usage , it needs exclusive open connection . Explicitly call Close() method of the adapter and the connection after done .

[Pirate]

One more thing about closing the connection , if you ever used to execute commands that return values , do this in your command object :

Dim cmd As New OleDb.OleDbCommand
cmd.ExecuteReader( [B]CommandBehavior.CloseConnection[/B] )

This will close the connection , then you don't have to do it explicitly using Close method associated with the command .

[Kenbuddy]

Well, I have not set up any command objects and created DataReaders using ExecuteReader from them. I tried adding a line that closes the connection immediately after each line that uses the DataAdapter.fill method, but I continue to get the same error. The error I am getting has to refer to an open DataReader that was temporarily created by a DataAdapter in the .fill method of the DataAdapter.

My theory is that when you execute the .fill method of a DataAdapter to fill a table in a DataSet, then the DataAdapter creates a temporary DataReader to fill the DataSet. After the DataSet is filled, the DataReader is closed automatically. But I think this is happening on a separate thread or something, and the code continues to execute past the line that called the .fill method, so it's possible that some other code immediately after the line with the .fill method would fail if it requires the same connection and is executed before the DataAdapter completes the fill operation.

Does that sound right?

[Pirate]

Originally posted by Kenbuddy
Well, I have not set up any command objects and created DataReaders using ExecuteReader from them. I tried adding a line that closes the connection immediately after each line that uses the DataAdapter.fill method, but I continue to get the same error. The error I am getting has to refer to an open DataReader that was temporarily created by a DataAdapter in the .fill method of the DataAdapter.

My theory is that when you execute the .fill method of a DataAdapter to fill a table in a DataSet, then the DataAdapter creates a temporary DataReader to fill the DataSet. After the DataSet is filled, the DataReader is closed automatically. But I think this is happening on a separate thread or something, and the code continues to execute past the line that called the .fill method, so it's possible that some other code immediately after the line with the .fill method would fail if it requires the same connection and is executed before the DataAdapter completes the fill operation.

Does that sound right?

Where did you read this ????
It's wrong .
Use DataReader with Command objects to execute SQL Strings . It's more efficient to use it when you need to return one or many records than using DataAdapter .Don't tell me you're using Data Components and binding stuff because they suck and buggy . Can you show some code that you're having trouble with !

[Kenbuddy]

I read this quote:

Note The DataAdapter uses the DataReader when filling a DataSet. Therefore, the performance gained by using the DataAdapter instead of the DataSet is that you save on the memory that the DataSet would consume and the cycles it takes to populate the DataSet. This performance gain is, for the most part, nominal so you should base your design decisions on the functionality required.

In this MSDN article:

http://msdn.microsoft.com/library/de...adonetbest.asp

I can't really paste the code because I can't identify the part of the code that is causing the problem. Here is the Try/Catch block that is catching the error, but the code here is not the source of the problem. There is a DataReader open somewhere else, and I can't identify where or what is opening that DataReader, because it ain't me opening it!

Try
    Me.daCustomerVehicles.SelectCommand.Parameters("@CustomerID").Value = intCurrentCustomer
    Me.daCustomerVehicles.Fill(Me.dsCustomers1.tblCustomerVehicles)
    Me.SqlConnection1.Close()
Catch ex As Exception
    MsgBox("PopulateCustVehicles" & vbCrLf & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error")
End Try

[Edneeis]

Are you multithreading some part that uses a dataadapter? If not how is it that two fills are running at the sametime?

[Lunatic3]

It seems that Fill method only closes connection if it was closed in the begining if not it will let it remain open. By the way, is it a windows application or web application? Ofcourse using Msgbox like that shows its a Windwos application but just to make sure. And are you using Dotnet Fx 1.1?

[Kenbuddy]

I am not doing any multithreading that I know of -- at least not deliberately! I think the problem may be that I have two procedures running back to back that each call the .fill method of a different DataAdapter filling a different DataSet, but using the same connection. They must be running too close together. I'm gonna take another look at it (the procedure calls are rather convoluted) and see if I can add some code to create a delay in there and see if that solves the problem. At Pirate's suggestion, I added the me.SqlConnection1.Close() method after every .fill method I could find in the program, but I continue to get the error. I'll keep you posted. And yes -- this is a Windows app running Dotnet Fx 1.1 (I'm using Visual Studio 2003 Professional Edition).

Thanks.

[Edneeis]

It looks like you are using dataadapters created at designtime if so then you might want to delete one or both and recreate them. Sometimes the designtime business gets a little buggy.

[Kenbuddy]

Turns out that I am using one of those "crappy" data controls that Pirate referred to -- the ComponentOne DBNavigator. Anyway, whenever I requeried the dataset, it triggered the PositionChanged event of the C1DBNavigator control, and I have a bunch of stuff in that event that shouldn't have been happening. I created a modular boolean "Initializing" variable that I used to exit out of the PositionChanged event if I had just done a requery, and that solved the problem.

However, I still don't understand what DataReader was open or where it came from.

Thanks,
Ken

[Pirate]

Oh I hate those binding stuff . I prefer to do most of work with code not with designers . I always say : Data Binding is hell . . I don't know about that quote but I've been using DataAdapter in many projects without using a single DataReader , nothing at all . If I need , I use DataReader only when executing commands like "Delete , Update , Select " . I don't recommend using any ADO.NET Component . They are hard to debug and maintain . This won't give you code resuablitity in your next projects . and btw , are you connecting to SQL Server ?(though this has no direct relation to the problem) .

[Kenbuddy]

I guess since I first learned database stuff using MS Access years ago, databinding is in my blood! A lot of the "bugginess" that I have encountered recently involves events that don't seem to fire consistently. For example, I still haven't figured out exactly how and why, with the standard .NET combo box, the SelectionChanged event gets fired. I know it fires when you go in there and click the control and change the selection. But there are other times it seems to fire, and other times it doesn't. Inconsistencies like that are what really tick me off.

As for the DataReader created by the DataAdapter, my guess is that it is buried deep within the .NET framework code -- the actual code that executes when you call the .fill method of the DataAdapter. The .fill method must create the DataReader, use it to fill the DataSet, and then (I would hope) close and dispose the DataReader. I would think the DataReader is local and private to the .fill method and can't be accessed externally. But apparently, in my situation, it was leaving the reader open for some reason. Maybe it's a bug. Maybe when I have time (i.e. never), I will create a test program and experiment with it and see if I can figure it out exactly.