Hi...
The following code worked with 2.0 Framework, now with NPGSQL 2.0.11 and framework 4.0, this is erroring out despite Pooling=False for the connection. Do you get any clue?
The underlying logic is that add nodes to treeview; for each node added, immediately get subnodes and populate...
vb Code:
Private Sub LoadRequirements(ByVal parnode As TreeNode)
Try
Dim CMd1 As NpgsqlCommand
CMd1 = New NpgsqlCommand
Dim Q As String = "SELECT REQID,SUMMARY,ISCHANGE,DEPENDENTBRS FROM FACTUALDATA.REQUIREMENTS WHERE PROJECT='{0}' AND BRID='{1}' AND PARENT='{2}' ORDER BY REQID"
Obviously there is only one line where Datareader gets executed... however for the sake of elaboration.. the line
vb Code:
Dim R1 As NpgsqlDataReader = CMd1.ExecuteReader
errors out second time when the function is self-referenced.
I'm not sure whether you're pointing out the issue or still asking the question. As has been stated, the error message tells you what the issue is: you can't open a second data reader on a connection. If you must use multiple data readers then you must use multiple connections. I don't think that you need multiple data readers though. Why not just get all the data in one go into a DataTable using a single data reader or a data adapter? You can then use the Select method of that table to get specific records as required.
Also, don;t use String.Format to insert values into SQL code. Follow the Blog link in my signature and check out my post on ADO.NET Parameters to learn the proper way to do it.
But it doesn't talk about the connection, does it? It only says the command already has a datareader active, whereas the command object is also a new one. The other suggestions, though I would consider to follow in my code... I actually did not want to touch the code existing which was a part of 2.0... so I wanted to know why is it that way for 4.0... ?
Jm is correct, but then you can always close the reader (reader.close), before you open a new one
Ah.. in this case it doesnt work... the next reader is created before the current reader is traversed entirely.
JM, however I need to read the NPGSQL documentation if the features are changed in the latest 2.0.11 release (Or is it true for any other connectors too, for example MSSQL, MySQL, Oracle, DB2 etc)?
It is true for basically all ADO.NET providers that only one data reader can be open over any particular connection at a time, unless that provider has a specific feature to do otherwise, e.g. MARS for SQL Server.