INVALID ATTEMPT TO CALL READ WHEN READER IS CLOSED. EXECUTEREADER TO GET MULTIPLE TABLE RESULTS

Ashish Pandey

Total Post:128

Points:898
Posted by  Ashish Pandey
C#
 1357  View(s)
Ratings:
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

            cmd.Parameters.Add(p)

        Next

        con.Open()

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

            If dr.HasRows Then

                Do While dr.Read()

                    Dim dt As New System.Data.DataTable

                    dt.Load(dr)

                    ds.Add(dt)

                Loop

            End If

        End Using

        con.Close()

    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.

  1. Pravesh Singh

    Post:412

    Points:2888
    Re: Invalid attempt to call Read when reader is closed. ExecuteReader to get multiple table results

    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 = ...)

    {

      do

      {

        if (reader.HasRows())

        {

           while (reader.Read())

           {

               ....

           }

        }

      } while (reader.NextResult())

    }

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!