Problem reading a SqlDataReader in Vb .Net

0

Hi, I have a problem reading a SqlDataReader in Vb .Net, it gives me the following message

  

Invalid attempt to call Read when the reader is closed

I have the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim ClaseAyuda As New BDCommand
    Dim Sql As String
    Dim Resul As Boolean
    Dim MyReader As SqlDataReader

    'Sql = "Update TBLSALDOSDIARIOS Set PeSecuencia = 1 Where PeFactura = 1024173"
    Sql = "select top 5 PeFactura from TBLSALDOSDIARIOS"


    MyReader = ClaseAyuda.ObtenerData(Sql, My.Settings.SIGMA)

    Try
        While (MyReader.Read)
            MessageBox.Show(MyReader("PeFactura"))
        End While

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    MyReader.Close()
End Sub

And in my class Help

    Public Function ObtenerData(ByVal Sql As String, ByVal Cnn As String) As SqlDataReader
    Dim MyReader As SqlDataReader

    Try
        Dim ActiveCon As New SqlConnection(Cnn)
        Using (ActiveCon)
            ActiveCon.Open()
            Dim cmd As New SqlCommand(Sql, ActiveCon)
            Try
                MyReader = cmd.ExecuteReader()
            Catch ex As SqlException
                MessageBox.Show("Error al Obtener Datos. " + ex.Message)
                MyReader = Nothing
            End Try
        End Using
    Catch ex As Exception
        MessageBox.Show("Ha ocurrido un error al ejecutar comando." + ex.Message)
        MyReader = Nothing
    End Try
    Return MyReader
End Function
    
asked by N. Zaldivar 22.07.2016 в 00:40
source

2 answers

0

You can not return a datareader since this requires that the connection is open, if you need to return data you should do it with disconnected objects such as a datatable or a list or collection of the class you define

Your code should be like this

Public Function ObtenerData(ByVal Sql As String, ByVal Cnn As String) As DataTable

    Dim dt As New DataTable

    Try
        Using (ActiveCon As New SqlConnection(Cnn))
            ActiveCon.Open()
            Dim da As New SqlDataAdapter(Sql, ActiveCon)
            da.Fill(dt)
        End Using
    Catch ex As Exception
        MessageBox.Show("Ha ocurrido un error al ejecutar comando." + ex.Message)
    End Try

    Return dt

End Function

It is not good practice to return a datareader

If you use classes and return a typed object, you define a class (this way of defining the property requires .net 4 or higher)

Public Class Factura
    Public Property PeFactura As String
    Public Property PeFechaVencimiento As DateTime
    Public Property PeMontoAs Decimal
End Class

Then load it by iterating the datareader

Public Function ObtenerData(ByVal Sql As String, ByVal Cnn As String) As List(Of Factura)

    Dim list As New List(Of Factura)

    Using (ActiveCon As New SqlConnection(Cnn))
        ActiveCon.Open()
        Dim cmd As New SqlCommand(Sql, ActiveCon)
        Dim MyReader As SqlDataReader = cmd.ExecuteReader()

         While (MyReader.Read)
            Dim item As Factura
            item.PeFactura = MyReader("PeFactura").ToString()
            item.PeFechaVencimiento= Convert.ToDateTime(MyReader("PeFechaVencimiento"))
            item.PeMonto= Convert.ToDecimal(MyReader("PeMonto"))
            lust.Add(item)
        End While

    End Using

    Return list

End Function

You get the list of the class you define as reps, it is similar to the datatable only with objects

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim Sql As String = "select top 5 PeFactura from TBLSALDOSDIARIOS"

    Dim ClaseAyuda As New BDCommand
    Dim result As List(Of Factura) = ClaseAyuda.ObtenerData(Sql, My.Settings.SIGMA)

    Try

        ForEach item In result
            MessageBox.Show(item.PeFactura)
        End For

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

End Sub
    
answered by 22.07.2016 / 21:04
source
0

You must run the Reader that you created using the function that is why you throw the error.

MyReader = Helpdesk.ObtainData (Sql, My.Settings.SIGMA)

Try
    MyReader.ExecuteReader
    While (MyReader.Read)
        MessageBox.Show(MyReader("PeFactura"))
    End While

Try that.

    
answered by 22.07.2016 в 20:17