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, May 27 '20 at 06:27
Also, `SqlCommand`, by way of `DbCommand`, and `SqlDataAdapter`, by way of `Component`, are `IDisposable` so you should be using them with `Using` blocks. — AlwaysLearning, May 27 '20 at 06:30
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, May 27 '20 at 06:33
Does this answer your question? [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — Fabio, May 27 '20 at 06:39
*"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, May 27 '20 at 06:40

Fabio · Accepted Answer · 2020-05-27T06:46:47.737

3

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

edited May 27 '20 at 06:46

answered May 27 '20 at 06:39

Fabio

31,528
4
33
72

In actual fact, you don't even need to create the connection object. I always forget as I rarely do it this way myself but the data adapter constructor will accept a query and a connection string and create both the connection and the command internally. – jmcilhinney May 27 '20 at 07:02
Thanks, I always forget how to work with adapter, because prefer to work with DataReader instead. – Fabio May 27 '20 at 07:12

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

1 Answers1