CODEWITHSUNDEEP
mysqlvb.net
user2694605
user2694605

Reputation:

Mysql & Vb.net 'Invalid Attempt to Read when reader is closed'

I have run into this error while collecting data from a mysql database and placing it into a DataView control... This is my code:

    Private Function PopulateActivity()
    Dim loginStatement As String = "SELECT * FROM activity WHERE id = @userid"

    Dim cmd As MySqlCommand = New MySqlCommand(loginStatement, mainconn)
    cmd.Parameters.AddWithValue("@userid", LoggedInUser.ID)

    Dim drMyAcount As MySqlDataReader = cmd.ExecuteReader()

    Dim rowCount As Integer = 0
    Dim rowAmount As Integer = 0
    'gets count of rows returned from mysql query'
    Using dt As New DataTable
        dt.Load(drMyAcount)
        rowAmount = dt.Rows.Count
    End Using
    'adds an entry for each item returned from the mysql query'
    Do While rowCount < rowAmount
        drMyAcount.Read() 'HERE IS WHERE ERROR OCCURS'
        Dim tempDateTime As String = drMyAcount.Item("dateTime")
        Dim tempInfo As String = drMyAcount.Item("info")
        Dim tempBalChanges As String = drMyAcount.Item("balChange")
        Dim tempToFrom As String = drMyAcount.Item("toFrom")
        ActivityView.Rows.Add(tempDateTime, tempInfo, tempBalChanges, tempToFrom)
        rowCount = rowCount + 1
    Loop
    drMyAcount.Close()
    Return 0
    End Function

I am unaware of why this is but it gives me an 'Invalid Attempt to Read when reader is closed' error one the:

drMyAccount.Read()

line...

I would appreciate any help on this topic! Thanks Much...

Upvotes: 0

Views: 4034

Answers (2)

Scott Selby
Scott Selby

Reputation: 9580

take out the dt.Load() , and counting of the rows prior to using datareader. DataReader has a built in property of .HasRows

if (drMyAcount.HasRows)            
    while (drMyAcount.Read())            
         Dim tempDateTime As String = drMyAcount.Item("dateTime")
         Dim tempInfo As String = drMyAcount.Item("info")
         Dim tempBalChanges As String = drMyAcount.Item("balChange")
         Dim tempToFrom As String = drMyAcount.Item("toFrom")
         ActivityView.Rows.Add(tempDateTime, tempInfo, tempBalChanges, tempToFrom)
         rowCount = rowCount + 1 //you can still count rows in the loop
    Loop

Upvotes: 1

Steven Doggart
Steven Doggart

Reputation: 43743

The MSDN documentation does not seem to specify, but apparently the DataTable.Load method closes the given IDataReader when it is done loading the data into the table. So, once you call dt.Load(drMyAcount), the drMyAcount will be closed.

Upvotes: 0

Related Questions