VB.NET - SQL Server query fails with “there is already an open datareader associated”

Question

I have the following code:

  Public Function executequery(ByVal query As String, Optional debug As Boolean = False)
    Try
        If debug Then
              Console.WriteLine(query)
        End If
        Dim da As New SqlDataAdapter
        Dim dtset As New DataSet
        Cmd.CommandText = query
        da.SelectCommand = Cmd
        da.Fill(dtset)
        dtset.Tables.Add()
        Return dtset.Tables(0)
    Catch ex As Exception
        logstring = "HH exq err: " & query & " - " & ex.ToString
    End Try

End Function

This function fails from time to time with this error:

HH exq err: select * from Settings where setting = 'actorid' - System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at DataCollector.Form1.executequery(String query, Boolean debug)

already an open datareader must be closed first.

I don't get what reader is causing this.. where is a data reader in this code? Also, why does this appear only sometimes?

Presumably you have a Dim Cmd as SqlCommand somewhere outside your function and that Cmd is being used by other functions. — AlwaysLearning
Also, SqlCommand, by way of DbCommand, and SqlDataAdapter, by way of Component, are IDisposable so you should be using them with Using blocks. — AlwaysLearning
cmd is declared as global variable, and initialized like this when the connection is opened: Cmd = myConn.CreateCommand and then it is only used inside the function. would it be better to declare it as a local variable inside the function? — sharkyenergy
Does this answer your question? There is already an open DataReader associated with this Command which must be closed first — Fabio
"cmd is declared as global variable". There's no good reason to do that. Create and destroy your ADO.NET objects as and where you need them. The only reason to do otherwise is if you want to use the same data adapter to retrieve and save data, which you clearly don't in this case. — jmcilhinney

Fabio Fabio · Accepted Answer · 2020-05-27T06:39:13

Always create new instances for SqlConnection and SqlCommand

Private Function GetData(query As String) As DataTable
    Using connection As New SqlConnection(connectionString), 
          adapter As New SqlDataAdapter(query, connection)

        Dim table As New DataTable()
        adapter.Fill(table)

        Return table
    End Using
End Function

VB.NET - SQL Server query fails with “there is already an open datareader associated”

1 Answers