[FWIW] Empty TABLE and RecordSet: SQLite

Databases
1

When I asked for a RecordSet (on an empty db file), I get a RecordSet.

Then I foolishly checked the RS reference against Nil and issued an error message with in mind: the TABLE is empty.

I just finished to add a simple test using the number of Records in that RecordSet and issued my error message: No Record found in the RecordSet.

Then, I started to read the code above the one I just add… and I discovered I was wrong years ago with my If RS = Nil Then code block (and that was the reason why I get a phone call once in a while when for some reason, the software do not find the data base file (wrong computer date / db file was moved / for examples and so, nothing in the db file because a brand new one was created, by design.

To be clear, I used:

RS_Count = RS.RecordCount If RS_Count < 10 Then // Issue an error report here End If

I checked < 10 by design. Who knows what my software is capable of (elsewhere) ? :wink:

One cannot think at everything all the time :frowning:

I hope this help.

2

I may misunderstand your Post, but why not use the recommended:

If rs <> Nil Then If Not rs.EOF Then // No Records in Table or bad querry? End If End If

3

Thank you Sasha for your suggestion, but… *

I wanted to trap a Nil. I have a Return in the If RS = Nil Then block.

In fact, I already have a silent error trapping in App that creates a brand new db file if the seeked one does not exists.

The user at the end of the year may move the previous year (2018) to an archival location instead of making a copy. To avoid troubles, I create a brand new one automatically. The data base file name holds the year number that gave an error if the computer clock is reinitialized to (2011 ? 1970 ? 1904 ? Whatever): the data base file is not found too.

I add that test (wrong test) mainy to be sure the db file have Records. My new test use the number of Records in the data base file to flag that error.

  • I strongly dislike that construction:

If [condition] <> Nil Then. I prefer = Nil because I can exit the code in the if block (and forget about that error condition) and continue outside of the error checking code.

4

The only time a recordset should be nIL is if the SQL in the select statement is not valid for some reason.

  • karen
5

Check rs.Recordcount=0 not Nil
as stated before, RS is NIL only if the query is invalid. otherwise a RS will always be returned, it just might be empty as opposed to NIL

and if you think the query might be invalid for some reason… check DB.ERROR first instead

6

If prefer the hint of @Sascha S, see above.
Not every database connection type supports .Recordcount , so better go for the general way.

7

If memory serves, if the recordset is empty (i.e. no records) then .EOF and .BOF are both true. The will work with every database.

8

A slight correction on Sascha’s post.

If rs <> Nil Then
  If Not rs.EOF Then
    // Records were found; no errors and not empty.
  End If
End If
9

Or when the db server connection fails if you are using a network-based engine.

[quote=421220:@Emile Schwarz]* I strongly dislike that construction:
If [condition] <> Nil Then. I prefer = Nil because I can exit the code in the if block (and forget about that error condition) and continue outside of the error checking code.[/quote]
What keeps you from using an else like

If rs <> Nil Then If Not rs.EOF Then // Records were found; no errors and not empty. Else // Msg user "no data found" End If Else // Fix bad select statement End If

(Can be inverted to use “If rs = nil” of course.)

10

I love this construction because it is auto-ending. Not error prone.

The indent is back to normal.

And, after all, if this is a matter of taste: I love it ! :wink:

And, you are right, if you love the opposite ! :wink: