XMozart XMozart - 1 year ago 105
Vb.net Question

MysqlDataReader.Read stuck on the last record and doesnt EOF

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
If dbType = 2 Then
cmd = New MySqlCommand
objConn = New MySqlConnection(connString)
cmd = New OleDbCommand
objConn = New OleDbConnection(connString)
End If
'If objConn.State = ConnectionState.Open Then objConn.Close()
cmd.Connection = objConn
cmd.CommandType = CommandType.Text
cmd.CommandText = str
If returnRecordSet Then
executeSQL = cmd.ExecuteReader()
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;")
'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)
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..

Answer Source

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
    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download