Unable to delete a Record, most of the time

Databases
1

I tried with three different computer (three OSes too):

  1. Windows 7 (or 10 ?)
  2. Snow Leopard i7 laptop
  3. El Capitan i5 laptop

The application was compiled with Xojo 2015r1.

The trouble ?

I was unable to find a duplicate Record with “Joël” (from the ListBox). I searched it manually.
Back at Home, with Xojo running the project I was able to find it…

So, I add a new Record using my name and only ASCII characters (I hope).
The record was added.
The data base can be read too as I displayed it into a ListBox.

In the original code, I do not cleared the window contents *, I add code to do that (after then) before the DELETE …/… WHERE ID=value.

In the original code (a Method), I add System.DebugLog everywhere in case something goes wrong: nothing goes wrong, the code goes until db.Commit.

I add a Breakpoint to follow the code in the debugger: everything is correct.

I clicked in the Close button and the ListBox is populated again: my Record is still here.

The boot was fresh. No active Internet.

Of course, the code was not changed since 2013.

That PC is running a fresh Windows and started to have troubles some months ago. I declared the Ant-virus faultive “only if the MacBook Pro does not behave the same”. The Customer’s MacBook Pro shared the same bug.

  • I noticed that sometimes if I read the next Record, the Previous Record, the card disappears (mileage may vary; waiting some seconds after the window that allows Add, Delete, Modify appears).

Conclusion:
The FolderItem is Valid
The db Class is not Nil
Connect is OK
No error in SQLExecute
DB.Commit is executed.

The Record is still here.

Just in case you want to know, here’s the command line:

DB.SQLExecute("DELETE FROM Caritas WHERE ID=" + Str(App.gUniqueID))

Do you have an idea ?

2

Is the ID a number or a string? (if it is a string, it should be surrounded by quotes)
Are you connecting to the database you THINK you are connecting to?
(Try running select count(1) from caritas where …) and see if the record exists in the database you are accessing.

Run your delete
Then check the record count again. Did it go to 0?
Do a commit.
Has it gone now?

Only once through those steps, check the database. If your code says it has gone, and your eyes say it is there, its not the same database. (Is it in memory only?)

3

The ID is an Integer (1701 Records only).

I used the default DataBase file, and I also tried the most recent (Open… ; maybe the same file, but I take that one today).

The database is reloaded once the DELETE (Modify and Add) is done. So, no error (in fact, at launch time, I check some fields like First Name, Family Name Spouse Name if there is an EndOfLine in it… and return a list of errors so they can be removed. I get that window: the ListBox is populated from the db).

With a so few number of Records, how frequent a database compress (I do not recall the real command name; this is what I wrote on the button I do not press since… ages) have to be done on the DB file ?

The Data base is a file (not in memory).

4

Not important. A deleted file should not show up in a search after it has been deleted and commit run.

Did you do the count, then the delete, then the count?
What happens?

5

I do not checked the count, yet.

I checked, and something strange happened: I had 1701 Records, add one (my name), then I had 1702 Records. I deleted my Record, and now I have 1701 Records, and my Record is still here. What Record did I deleted ?

That is the command I was searching for; I was lucky, I found the sources of the application; unchanged since march 2018… the command is:

DB.SQLExecute("VACUUM")
6

Instead of a Count() , do a select

First, do a msgbox str(App.gUniqueID)

So you know what ID you are targetting

then, do a select
Select * from Calibras where ID = 1234 (or whatever)

Now examine the result: which row was it?
Look at the row you wanted to delete: what ID does it have really?

7

I will do; I display App.UniqueID in the window. So, somewhere I “know” what Record I deleted. I just am afraid I deleted some unknow Record (not me, but the Customer on its base).

My base can be destroyed, I (always) have a backup.

BTW: thank for your help.

8

I’ve followed your steps, used the ID for my Record.

The MsgBox returned that ID; rs holds the data from my Record (*), that Record was deleted.

I still do not understand.

(*) I’ve added a Break point just after rs= db.SQLSelect… so I can check what’s in rs.

9

rs holds the data from my Record (*), that Record was deleted.

These two statements contradict.

10

rs is a different beast.

Code extract:

  If Caritas_DB.Connect() Then
    MsgBox str(App.gUniqueID)
    
    Dim rs As RecordSet
    rs = Caritas_DB.SQLSelect("Select * from Caritas where ID = 1722")
    Beep // Breakpoint here
    
    Caritas_DB.SQLExecute("DELETE FROM Caritas WHERE ID=" + Str(App.gUniqueID))
    If Caritas_DB.Error Then
11

have your table a primary key on id?
is your database in a read only resource folder? it seems you speak about a sqlite db.
create the sql string first for debug output and then use it.
before CommitTransaction a BeginTransaction should be used, and at error a RollBack.
edit a database table and write something unique in it that you can see in your app.

12

Yes, rs will still be populated from the SQLSelect. That data is now local to your app and unaffected by anything you do to the db. If you use rs to populate your window, then the original data will still be there, even though it no longer exists in the database.

13

If you read the original entry, you can see there is one:

DB.SQLExecute("DELETE FROM Caritas WHERE ID=" + Str(App.gUniqueID))

is your database in a read only resource folder?
I already wroe I add an entry using my name and tried to delete it.

All your other entries: irrelevant.

At last, this application works since 2013-04. The problem started some weeks ago.

I fired DiskUtils and checked my hard disks. I started back my test and it seems to works now.

A night passed and the morning ended. I booted on a fresh High Sierra, fired Xojo 2019… everything is OK. I then installed 2021r1.1, loaded the project, run… beside the 32 Bits warning, the application ran.
I add a Record with my name and specs, delete it, and …
a. My Record was added,
b. When I delete it, it was removed.

I believe my hard disk where the project was had a problem (?) or some kind and create a bad application. Now, after some reboot, etc. all is clear.

I will wait until late this evening, check once more, then build a brand new application that I will install tomorrow if everything is OK.

thank you all for your help.

14

sure i saw this field with name id but it does not mean it is set as primary key.
however a database without this could behave odd.

1 Like