MysqlDataReader.Read stuck on the last record and doesnt EOF

Question

i confused why mySqlDataReader.Read stuck on the last record and doesnt EOF ..

Here's my private function for executeSql :

Private Function executeSQL(ByVal str As String, ByVal connString As String, ByVal returnRecordSet As Boolean) As Object
    Dim cmd As Object
    Dim objConn As Object
    Try
        If dbType = 2 Then
            cmd = New MySqlCommand
            objConn = New MySqlConnection(connString)
        Else
            cmd = New OleDbCommand
            objConn = New OleDbConnection(connString)
        End If
        'If objConn.State = ConnectionState.Open Then objConn.Close()
        objConn.Open()
        cmd.Connection = objConn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = str
        If returnRecordSet Then
            executeSQL = cmd.ExecuteReader()
            executeSQL.Read()
        Else
            cmd.ExecuteNonQuery()
            executeSQL = Nothing
        End If
    Catch ex As Exception
        MsgBox(Err.Description & " @ExecuteSQL", MsgBoxStyle.Critical, "ExecuteSQL")
    End Try
End Function

And this is my sub to call it where the error occurs :

Using admsDB As MySqlConnection = New MySqlConnection("server=" & rs("server") & ";uid=" & rs("user") & ";password=" & rs("pwd") & ";port=" & rs("port") & ";database=adms_db;")
                    admsDB.Open()
                    connDef.Close()
                    rs.Close()
                    'get record on admsdb
                    Dim logDate As DateTime
                    Dim str As String
                    str = "select userid, checktime from adms_db.checkinout in_out where userid not in (select userid " &
                        "from adms_db.checkinout in_out join (select str_to_date(datetime,'%d/%m/%Y %H:%i:%s') tgl, fid from zsoft_bkd_padang.ta_log) ta " &
                        "on ta.fid=userid and tgl=checktime)"
                    Dim rsAdms As MySqlDataReader = executeSQL(str, admsDB.ConnectionString, True)
                    Dim i As Integer
                    'This is where the error is, datareader stuck on the last record and doesnt EOF
                    While rsAdms.HasRows
                        'i = i + 1 
                        logDate = rsAdms(1)
                        'save to ta_log
                        str = "insert into ta_log (fid, Tanggal_Log, jam_Log, Datetime) values ('" & rsAdms(0) & "','" & Format(logDate.Date, "dd/MM/yyyy") & "', '" & logDate.ToString("hh:mm:ss") & "', '" & logDate & "')"
                        executeSQL(str, oConn.ConnectionString, False)
                        rsAdms.Read()
                    End While

                    'del record on admsdb
                    str = "truncate table checkinout"
                    executeSQL(str, admsDB.ConnectionString, False)
                End Using

i'm new to vbnet and really have a little knowledge about it,, please help me,, and thank you in advance..

there's no actual error message, it's just the loop doesnt end and stuck at the last record,, — XMozart, Dec 01 '16 at 05:30

score 1 · Accepted Answer · answered Dec 01 '16 at 05:54

The issue is that you're using the HasRows property as your loop termination expression. The value of that property never changes. Either the reader has rows or it doesn't. It's not a check of whether it has rows left to read, so reading has no effect.

You are supposed to use the Read method as your flag. The data reader begins without a row loaded. Each time you call Read, it will load the next row and return True or, if there are no more rows to read, it returns False.

You normally only use HasRows if you want to do something special when the result set is empty, e.g.

If myDataReader.HasRows Then
    '...
Else
    MessageBox.Show("No matches found")
End If

If you don't want to treat an empty result set as a special case then simply call Read:

While myDataReader.Read()
    Dim firstFieldValue = myDataReader(0)
    '...
End While

Note that trying to access any data before calling Read will throw an exception.

MysqlDataReader.Read stuck on the last record and doesnt EOF

1 Answers1