locked
SqlDataReader - Invalid attempt to call Read when reader is closed RRS feed

  • Question

  • User-1487494846 posted

    I have a class in which I pass in the SqlCommand and it gets/updates/sets the results for me on the database, that way on my other classes I don't have to open or close or mess with any connections.  here is my first function:

      Private Function GetSQLResult(ByVal sqlcmd As SqlCommand, ByVal Type As SQLExecuteType)
           
    
    
            Dim oResult = Nothing
    
            Dim conStr As String = "data source:....."
    
            '* we will use the Using to open the SqlConnection'
            Using sqlConnect As New SqlConnection(conStr)
    
                sqlConnect.Open()
    
                 sqlcmd.Connection = sqlConnect
    
                '* we will check what type of command is trying to be executed'
                Select Case Type
    
                    Case SQLExecuteType.NonQuery
                        '* non query, this includes updates, inserts, and deletes'
                        oResult = sqlcmd.ExecuteNonQuery()
    
                    Case SQLExecuteType.Scalar
                        '* scalar, returns the first result (first row, first column)'
                        oResult = sqlcmd.ExecuteScalar()
    
                    Case SQLExecuteType.DataReader
    
                        oResult = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
    
                End Select
    
                sqlConnect.Close()
    
            End Using
    
            '* Return the results'
            Return oResult
    
        End Function

    now on my class I have the following:

     Public Sub DataReaderTest(ByRef var1 As String, ByRef var2 As String)
    
                Dim sqlcmd As New SqlCommand
    
                '* Configure the command object'
                sqlcmd.CommandType = CommandType.Text
                sqlcmd.CommandText = "select * from testIdentity"
    
                Using DataReader As SqlDataReader = SQLExecute(sqlcmd, SQLExecuteType.DataReader)
    
                    '* Populate controls with reader results
                    With DataReader'
    
                        If .Read Then
                            var1 = .Item("column1")
                            var2 = .Item("column2")
                        End If
                        '* Close data reader'
                        .Close()
    
                    End With
    
                End Using
    
    
        End Sub

    Now I am getting the error on the "If .Read Then" line because the sqlConnection is not specified within this class and it is on the other class, I tried to comment out the close connection on the first class to see if it would help but I still get the same error.   Can you help please?

    NOTE: SQLExecuteType is a custom ENUM i have, it does nothing but serve as an enum.

    Friday, December 2, 2011 10:16 AM

Answers

  • User-260044566 posted

    Hi elbasha,

    SqlDataReader always needs an opened connection to the database. In your code you are using Using ... End Using for sqlconnect object and this will be disposed once it executes End Using line.In our case we are returing the sqldatareader object.So the connection object for that will be disposed and this will return the error you have mentioned above.

    Solution:

    Change your code as below,(I have removed the using and endusing)

     Private Function GetSQLResult(ByVal sqlcmd As SqlCommand, ByVal Type As SQLExecuteType)
           
    
    
            Dim oResult = Nothing
    
            Dim conStr As String = "data source:....."
    
            '* we will use the Using to open the SqlConnection'
            Dim sqlConnect As New SqlConnection(conStr)
    
                sqlConnect.Open()
    
                 sqlcmd.Connection = sqlConnect
    
                '* we will check what type of command is trying to be executed'
                Select Case Type
    
                    Case SQLExecuteType.NonQuery
                        '* non query, this includes updates, inserts, and deletes'
                        oResult = sqlcmd.ExecuteNonQuery()
     sqlConnect.Close()
                    Case SQLExecuteType.Scalar
                        '* scalar, returns the first result (first row, first column)'
                        oResult = sqlcmd.ExecuteScalar()
     sqlConnect.Close()
                    Case SQLExecuteType.DataReader
    
                        oResult = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
    'we should not close the connection here as sqldatareader needs open connection
                End Select         
    
    
    
            '* Return the results'
            Return oResult
    
        End Function
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 3, 2011 4:58 AM

All replies

  • User-58016157 posted

    Even if you rem out the sqlConnect.Close() you still have:

    oResult = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)

    Which will close it as well anyway, try changing this first so doesn't close the connection as this is the issue.  You could also try returning a DataSet or Table instead of the DataReader.

    Friday, December 2, 2011 10:35 AM
  • User-1487494846 posted

    What should I change this to? I tried:

    oResult = sqlcmd.ExecuteReader()

    but still didn't work.  Yes I could use a Dataset which is what I use for 99% of my work right now, but there are a couple that use SqlDataReader and I would like to implement that in my function so in case I ever need it in the future.. I would have it ready.

    Friday, December 2, 2011 10:51 AM
  • User-58016157 posted

    Put a breakpoint on the line that fails and just check that the line:

    Using DataReader As SqlDataReader = SQLExecute(sqlcmd, SQLExecuteType.DataReader)

    does actually return as a SqlDataReader, probably try declaring DataReader first before setting it here too.

    Friday, December 2, 2011 11:22 AM
  • User-1487494846 posted

    I tried that, it is returning a SqlDataReader like expected but I am still getting an error on the "If .Read Then" line

    Friday, December 2, 2011 11:33 AM
  • User-58016157 posted

    What is the error?

    Friday, December 2, 2011 11:38 AM
  • User-1487494846 posted

    Error is: Invalid attempt to call Read when reader is closed

    Friday, December 2, 2011 4:43 PM
  • User-58016157 posted

    I think you might be better changing this from a function to a class whereby one of the properties is a DataReader, then you can call a function within it to open the connection, another to get the datareader which at this point you can get the DataReader property and then a last one to close the connection when you are finished as it may be the case that the connection is closed automatically once the function has finished so you need some way of keeping it alive whilst you use the DataReader.

    Let me know if your not sure what I mean.

    Saturday, December 3, 2011 4:25 AM
  • User-260044566 posted

    Hi elbasha,

    SqlDataReader always needs an opened connection to the database. In your code you are using Using ... End Using for sqlconnect object and this will be disposed once it executes End Using line.In our case we are returing the sqldatareader object.So the connection object for that will be disposed and this will return the error you have mentioned above.

    Solution:

    Change your code as below,(I have removed the using and endusing)

     Private Function GetSQLResult(ByVal sqlcmd As SqlCommand, ByVal Type As SQLExecuteType)
           
    
    
            Dim oResult = Nothing
    
            Dim conStr As String = "data source:....."
    
            '* we will use the Using to open the SqlConnection'
            Dim sqlConnect As New SqlConnection(conStr)
    
                sqlConnect.Open()
    
                 sqlcmd.Connection = sqlConnect
    
                '* we will check what type of command is trying to be executed'
                Select Case Type
    
                    Case SQLExecuteType.NonQuery
                        '* non query, this includes updates, inserts, and deletes'
                        oResult = sqlcmd.ExecuteNonQuery()
     sqlConnect.Close()
                    Case SQLExecuteType.Scalar
                        '* scalar, returns the first result (first row, first column)'
                        oResult = sqlcmd.ExecuteScalar()
     sqlConnect.Close()
                    Case SQLExecuteType.DataReader
    
                        oResult = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
    'we should not close the connection here as sqldatareader needs open connection
                End Select         
    
    
    
            '* Return the results'
            Return oResult
    
        End Function
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 3, 2011 4:58 AM