Recordset NIL

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?

When you first load the record set, you can use:

If rs.EOF and rs.BOF then

[quote=108639:@Greg O’Lone]When you first load the record set, you can use:

If rs.EOF and rs.BOF then

Does that also return False when RS = NIL?

No. Please check database for error first.
Record set is nil in case of error.

So, code that I can use in any occassion:

if not db.error then if not rs.eof and rs.bof then 'proceed with processing

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

You can also use a while loop depending on your need to branch off because of an empty recordset

[code]if rs <> nil then
while not rs.eof

    rs.movenext
wend

end if[/code]

That is what I do, but I’m looking for an universal approach to check wether RS contains any records…

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.

Would http://documentation.xojo.com/index.php/RecordSet.RecordCount be of use here?

No
Some db’s don’t actually set that - all you, and the plugin, know is “there’s rows” not how many.

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?

Code defensively. Always test for nil before you access an object you just acquired. I usually write it as

if rs <> nil then
   while not rs.EOF
       ...
       rs.MoveNext
   wend
else
   msgbox "There was an sql error: " + db.ErrorMessage
end

I always use:

While rs<>Nil And Not rs.EOF

Wend

If db.Error Then

End If

No good?

Check for error first

if db.Error Then

End If
While rs<>Nil And Not rs.EOF

Wend

[quote=108821:@Sascha S]I always use:

While rs<>Nil And Not rs.EOF

Wend

If db.Error Then

End If

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