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?

Answer 1

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