EOF is making problem

[moeed]

i m trying to pick data randomly from the data base
but an error is getting generate the error is:
"either EOF or BOF is true or the current record has been deleted; the operation requested by the application requires a current record."
the record is available in th data base
de is the "data environment", "cid" is a variable having class id, "sid" is a variable having subject id from the tables.
plz c the code below 'n send me the answer.


While Not de.rscmd3.EOF
k = CInt(Rnd * 10)
de.rscmd3.Filter = "classid= " & cid & " And subjectid = " & sid & " And questionid = " & k
If de.rscmd3.EOF = True Then
de.rscmd3.MoveFirst
End If
paper.List1.AddItem "Q:" & x & ") " & de.rscmd3.Fields("question")
x = x + 1
If x = no Then
paper.Show
de.rscmd3.Close
Exit Sub
End If
Wend

[IROY55]

If de.rscmd3.EOF = True Then
--------------------------------------
This usualy indicates that recordset is empty.

[techgnome]

Originally posted by IROY55
If de.rscmd3.EOF = True Then
--------------------------------------
This usualy indicates that recordset is empty.

Not quite 100% correct .EOF only indicates the end of the recordset.

if (.EOF AND .BOF ) is true, then you have an empty recordset.

[IROY55]

No, when EOF is True means (in most of the time) that there is no records returned from the DB.

[techgnome]

Originally posted by IROY55
No, when EOF is True means (in most of the time) that there is no records returned from the DB.

Yes... BUT the problem is that he's checking for EOF, if EOF is True (which in the case of an empty recordset it is), he's doing a .MoveFirst.... which he can't do, because BOF is also true.
Here's how I would do it:

de.rscmd3.Filter = "classid= " & cid & " And subjectid = " & sid & " And questionid = " & k
If NOT (de.rscmd3.EOF AND de.rscmd3.BOF) Then
de.rscmd3.MoveFirst
End If

But that's just one gnome's opinon.

[IROY55]

Yes... BUT the problem is that he's checking for EOF, if EOF is True (which in the case of an empty recordset it is), he's doing a .MoveFirst.... which he can't do, because BOF is also true.

The very simple answer is:

If Rs.EOF = True Then
    MsgBox "No Data Found"
End If

Roy

[trojjer]

Originally posted by IROY55

If Rs.EOF = True Then
    MsgBox "No Data Found"
End If

Yo, I'm sorry, but that's a pretty reckless way of going about it. Like Tech, I disagree.

Here's the definite solution, that I would use (and it's still pretty simple):

If DB.BOF And DB.EOF Then
MsgBox "The database is empty"
Else
DB.MoveFirst
End If

All you need to do is include both BOF and EOF, using the operator as shown. Simple.

[IROY55]

Hey YOOOOOOOOOOO, I have a name first.
Second,
I can give you a lecture if you need on this topic. But the easiest way to explain this to you would be the following:
DB compiler (if you will) first "sets" itself on the BOF of the future return set and then must "populate" the space between BOF and EOF. But if there is no records to get based on the query it "moves" immediately to the EOF and pointer will stay there. So in your program all you need to do is to whether or not EOF = True.
Is that clear now, yoooo.

Best regards, Roy

[trojjer]

...Yeah, ok IROY(55?) - that would probably work. I'm just saying that, to remove any doubt, adding that little conditional statement for BOF will not do any harm.

I know you are a non-conformist on this issue (I am, usually...); so fine... I would stick to the proven - and simple - methods for this, though.

[crosbj]

Just wanted to add the following from the MSDN.

If you open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is zero. When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are False.

If you delete the last remaining record in the Recordset object, the BOF and EOF properties may remain False until you attempt to reposition the current record.

I bring it up for the second paragraph that mentions deleting records. I have gotten errors moving through ado recordset's when users deleted the last record because both .BOF and .EOF are false but there are no records? I started to use the recordcount instead of .BOF and .EOF. I know its off the post but it is an odd quirk about ADO to be aware of. (not sure about ado.net)

[mlewis]

Assuming you're running with ADO it is ALWAYS better to use .RecordCount (as long as the ODBC engine you're tied to supports it, I know MySQL doesn't but Access and SQL server do, so YMMV)

This is because RecordCount ALWAYS returns the current status of the recordset without tossing an error, like a .MoveNext and .EOF check might do.

It is also very sloppy coding to only check .EOF at the beginning of a DB query. It's better to use something like this:

If Not (recset.eof And recset.bof) Then
Do
recset.movenext
Loop until Recset.eof
End If


Of course this may vary depending on the situation.

[crosbj]

Assuming you're running with ADO it is ALWAYS better to use .RecordCount

Thanks 'mlewis' I always wondered if I should be using the .RecordCount for various ADO recordset functions but it seemed to be the only way I could avoid certain errors. Just wasn't sure if it was proper to rely on it so much.