A Record is added n times to a TABLE

Hi all,

I decided to add a simple TABLE (About) with one Record that give a simple detail on the .sqlite file.

In debug mode (IDE) and in the running application, I only have ONE Record there because I check how many that TABLE have and add one only if there is none.

But, in production, sometimes I saw more than one (actually 5, but I can delete it easilly using an utility *).

I do not understand how this can be.

Nota: I ask for a RecordSet (with all Records), and check the number of Record (.RecordCount) in an If block. If the returned value is 0, I add one, else I do nothing.

  • Yes, I can check that in the running application, but how another Record can be added baffles me.

Show some code, we don’t know what’s happening by explaining.

I vaguely remember having trouble with the RS.RecordCount property.

And I’m not sure. But isn’t it more efficient to get the recordcount of some table by using the COUNT(*) value in a SQL statement?

rs = db.SQLselect( "SELECT COUNT(*) FROM myTable" )

Dim count As Integer = rs.idxField(1).IntegerValue

Where db is an opened database and rs is a RecordSet object

As far as I can remember is that when you get all the records in a recordset, it would consume more memory. When you use the COUNT(*) value, it doesn’t.
But I can be wrong. I have been getting the recordcount like this as long as I can remember. Works like a charm.

But as Derk said… we don’t know what the exact problem is you’re dealing with. Not until we see some code indeed.

Thanks for the answers.

Used code ? Wait a bit, I will do that later in the morning (time to “drive my car” now).

Basically, at open time, in the If block that creates the DB file (.sqlite), I get a RecordSet and I check if the RecordCount is 0 Then I add a Record. Note that I check if the TABLE NOT EXISTS… (I add the TABLE, else I do nothing).
Now that I wrote that line (just above), I realize that an error can be done in that block, maybe.

I may add a new Record Field with the date and time of the add Record. If this happens at plant open or close time, it maybe a hint.

If using this to store and retrieve only one record which has default values that allow you to filter a more complex data set, why not delete all entries, then add a single record based on your record set. That way there will only ever be one record in your default table

This simple Record (TABLE) is in the .sqlite only for “I am owner by this application”, in case the file goes orphan one day.

I also use another TABLE for owner (©), original application creation date, .sqlite creation date / owner’s application, and Version #, etc.

Beside Version # (and that was an excellent idea), no other information is used in the application.

I watched my code and found nothing / no error (yet).
I will take some times in the sources of my application used since spring 2013 where that Record is filled, sometimes (not at every run).

Maybe, this simple Record… is too simple and hides a major design flaw.

At last I found where the problem lies.

When I saw the line If RS.FieldCount = 0 Then I changed it to < 1 with no change.

I think some seconds reading that line until I realized: why FieldCount ? It is RecordCount that I want.

Once this change was done, the program worked fine.

Thank you all.