0 votes

Problem while reading from a SqlDataReader in Vb .Net

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

I try not to be valid to call Read when 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

0voto

Agustin M. Points 1160

You have to run the Reader that you have created through the function which is why we throw the error.

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

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

You try that.

0voto

Leandro Tuttini Points 25288

You can't 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


But uses classes and returns an object type, 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 the loads 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

get the list of the class you define as repsuesta, 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

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X