I have difficulties to check wether a Recordset contains no records, thus is empty. I use the ‘if RS <> NIL’ statement but it doesn’t work in all situations.
Sometimes a RS is not NIL but has no records. I can’t seem to find out when that’s exactly the case.
So sometimes I use
if rs.recordcount > 0
if not rs.IdxField(1).Value.IsNull
What do you use to check wether a query returns valid records?
You do not need to check for BOF. If a RecordSet contains at least one record, BOF will be False, since the RecordSet is already pointing to the first record after SQLSelect. You only need to check for EOF.
If you are iterating over the RecordSet - for example for filling a Listbox - you can directly start with the loop:
if not db.error then
do until rst.eof
...
rs.MoveNext()
loop
end
dim rs as recordset = db.sqlselect ..
if db.error then
// you got some kind of error
end if
if rs <> nil and rs.eof then
// you got no error BUT also got no records
end if
[quote]dim rs as recordset = db.sqlselect …
if db.error then
// you got some kind of error
end if
if rs <> nil and rs.eof then
// you got no error BUT also got no records
end if[/quote]
I don’t think I’ve ever seen a case where db.error was false and RS was nil anyway.
Probably not but never hurts to be sure
I suppose that could be
dim rs as recordset = db.sqlselect ..
if db.error then
// you got some kind of error
// probably want to bail out here by return or whatever is appropriate for your code
// some would say use GOTO ERROR but I would not
end if
if rs = nil then
// you got some kind of issue that made the rs nil but NOT reported as an error
// probably want to bail out here by return or whatever is appropriate for your code
// some would say use GOTO ERROR but I would not
end if
while not rs.eof then
// you got records
// process them all
wend
// end of your code
ERROR:
return
rs will be nil ONLY if there is an error (whether reported in db.error or not). It does not tell you whether you got records or not. You should always test for rs is nil, but only for defensive coding. The real test for whether you got any records is rs.EOF. You cannot count on rs.RecordCount - it is valid for some database types, but not for others.
Ah, talking about SQlite by the way. The ‘if rs <> NIL’ is actually often used in the Xojo documentation. Like Tim said, there is an error when RS = NIL, so maybe it makes more sense to test wether db.error = True.
So maybe Xojo should put ’ if rs.eof (=True)’ in favor of ’ if rs <> NIL’ in the documentation?
No good?[/quote]
It should work fine, although it’s not immediately obvious what conditions could arise. It also seems to imply that rs could become nil inside the loop, which is a bit misleading. And it causes you to have to stop and think about whether db.Error could be affected by the loop, too.
[quote=108789:@Tim Hare]if rs <> nil then
while not rs.EOF
…
rs.MoveNext
wend
else
msgbox "There was an sql error: " + db.ErrorMessage
end[/quote]
I understand, but I want to know IF there are any records before starting the loop. The loop may be time-intensive so I’d like to inform the users with ‘Sorry… no records today’ or ‘Get some coffee as this can take a while’…
In the above code I have to check wether records got processed or not…
while not rs.EOF
recordsProcessed = True
...
rs.MoveNext
wend
else
msgbox "There was an sql error: " + db.ErrorMessage
end