ADO error "There is already an open DataReader associated with this Connection"

[jck via DotNetMonster.com]

I have been reading somewhat about this error, but I am confused as to why
mine is happening.

I use a single, static connection from the instantiation of the form in my
project. Against this, I run DataReaders and then close them immediately.
From what I understand, the MS DataReader object may not "cleanup" after
itself well, even tho you may use the .Close method and even explicitly use
the old "<datareadername> = Nothing" from VB6.

However, I am using a combobox on a form which is *never* associated with
any data. I manually populate the combobox with the .Add method at the
time for form creation, which the form is an MDI child of the MainForm.
Here is what happens on opening the form in the New() method:
*******************
Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call
Me.cboFiscalYear.Items.Add(Format(DateAdd("yyyy", -1, Now), "yyyy"))
Me.cboFiscalYear.Items.Add(Format(Now, "yyyy"))
Me.cboFiscalYear.Update()
'InitializeFormValues()

cnData = New SqlConnection
cnData.ConnectionString = BiblioConnString
cnData.Open()

End Sub
****************
Now each time I use a DataReader, I ExecuteReader() and then .Close()
the DataReader. In the process of loading information into the form based
on item selection from a subscreen, I run into this error. However, it's
not dealing with DataReaders and DataSets. Whenever I address certain
properties of the cboFiscalYear ComboBox, I receive the error. Here is my
code that is blowing up:
****************
Private Sub LoadEntry(ByVal intID As Integer)
Dim strSql As String
strSql = "SELECT * from transactions where TransID =" & intID
Dim cmdTransaction As New SqlCommand
cmdTransaction.Connection = cnData
cmdTransaction.CommandText = strSql
Dim rs1 As SqlDataReader = cmdTransaction.ExecuteReader()
Dim strTemp As String
Dim intTemp As Integer
Dim data(4) As String
Try
Do While rs1.Read
strTemp = rs1.Item("FiscalYear").ToString
intTemp = Me.cboFiscalYear.FindString(strTemp)
Me.cboFiscalYear.SelectedIndex = intTemp <<---- Line
that blows up
Me.txtPOROVisa.Text = rs1.Item("PORO").ToString
Me.txtOrderInfo.Text = rs1.Item("ORDERINFO").ToString
Me.txtInvoiceNum.Text = rs1.Item("INVOICENUMBER").ToString
Me.dtpInvoiceDate.Text = rs1.Item("INVOICEDATE").ToString
Me.dtpProcessDate.Text = rs1.Item("TRANSDATE").ToString
Loop
Catch ex As Exception
MessageBox.Show("Error in transaction main population: " &
ex.Message)
End Try

rs1.Close()


cmdTransaction.CommandText = "select * from transdetail where TransID
= " & intID
rs1 = cmdTransaction.ExecuteReader
Me.ordersTable.Clear()
Do While rs1.Read
data(0) = CType(rs1.Item("DeptID"), Integer)
data(1) = CType(rs1.Item("AMOUNT"), Decimal)
data(2) = CType(rs1.Item("INVOICENUM"), String)
data(3) = CType(rs1.Item("ACCOUNTID"), Integer)
data(4) = CType(rs1.Item("FUNDID"), String)
Me.ordersTable.Rows.Add(data)
Me.ordersTable.AcceptChanges()
Me.dgrDeptCosts.DataSource = Me.ordersTable

Me.cboDeptID.SelectedItem = 0
Me.cboDeptID.Text = ""
Me.txtDeptAmt.Text = ""

RetotalExpense()
Loop

rs1.Close()
rs1 = Nothing


End Sub
***************
This also happens if I try to set the .Text member of the ComboBox.

Can anyone tell me why this ADO DataReader error occurs in .NET upon
assignment of a value to a ComboBox? Does the DataReader object in .NET
require only dynamic connections so that proper cleanup occurs?

Thank you for any help you can provide.

 

[Sahil Malik [MVP]]

This error is happening because you are trying to run two active commands on
one connection. You can't do that in .NET 1.1/SQL 2000. You need MARS - .NET
2.0 / SQL2K5 to do that - which is something that is nice, but should be
used carefully. (Chapter #12 in my upcoming book).

To get around this, just use two connections.

 

[jck via DotNetMonster.com]

I created 2 global subs in the main Module1.vb called StartDBConnection and
EndDBConnection that dynamically create and destroy the DB connection
automatically for each time it's used.

I'm not sure why .NET does this, but evidently if you use a static
connection object with the SqlClient namespace you have issues with
DataReader cleanup even if you use the innate .Close method and the VB6-
style
"<datareader> = Nothing" code.

 

[Marina]

Well, you will have bigger problems then this. Even if let's say the
datareader was cleaned up correctly, if 2 users hit your site at the same
time, you still get the problem.

You cannot use the same connection object globally in asp.net. Then all your
users have the same instance - and obviously they can't all be running
command on it in parallel.

However, the code you posted before does not appear to be working that way,
so I am not sure what you are talking about.

Also, setting variables to Nothing, does just that - nothing. It's just an
extra instruction to execute that does not do a thing.

 

[Sahil Malik [MVP]]

That is because setting something to nothing in VB6 caused the underlying
COM object to Release. .NET relies on Garbage collection, in which setting
anything to null doesn't mean it is cleaned up at that very instant. You
should be calling dispose instead.

DataReader.Close doesn't mean SqlConnection.Close, unless you are using
CommandBehavior.CloseConnection.

The error you are getting is most certainly because you are trying to run
two datareaders or two parallel commands on a single open connection. Why do
you have to do a static SqlConnection?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/