Home > DeveloperSection > Forums > Invalid attempt to call Read when reader is closed. ExecuteReader to get multiple table results
Ashish Pandey

Total Post:128

Posted on    September-24-2013 4:34 AM


 1 Reply(s)
 1328  View(s)
Rate this:

I'm trying to return a List(Of DataTable) using a SqlDataReader instead of filling a dataset. I'm getting the "Invalid attempt to call Read when reader is closed" error after it retrieves 4 tables correctly. Here is the function I'm using

Private Function ExecuteDS(ByVal SPName As String, ByVal ParamList As List(Of System.Data.SqlClient.SqlParameter)) As List(Of System.Data.DataTable)

    Dim ds As New List(Of System.Data.DataTable)

    Dim dbConStr As Database           

    dbConStr = New Database()

    Using con As New System.Data.SqlClient.SqlConnection(dbConStr.ReturnString)

        Dim cmd As New System.Data.SqlClient.SqlCommand(CStr(SPName), con)

        cmd.CommandType = System.Data.CommandType.StoredProcedure

        cmd.CommandTimeout = 30

        For Each p As System.Data.SqlClient.SqlParameter In ParamList




        Using dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader

            If dr.HasRows Then

                Do While dr.Read()

                    Dim dt As New System.Data.DataTable




            End If

        End Using


    End Using

    Return ds

End Function

The error occurs at dr.Read(). It should return 4 tables but after it gets the 4th, it still goes and tries to do the dr.Read() and throws the error instead of just exiting the loop properly. Any insight, I would appreciate it, thank you.

Pravesh Singh

Total Post:412

Posted on    September-24-2013 7:42 AM

Hi Ashish,

You need to not test for HasRows if there is no NextResut

In C#

That last While will cause it to leave immediately.

using (IDataReader reader = ...)




    if (reader.HasRows())


       while (reader.Read())





  } while (reader.NextResult())


Don't want to miss updates? Please click the below button!

Follow MindStick