User complaint: update does not works

In a project running since the Xojo early days (2013) a user complains about Update does not works.

I was looking something else yesterday and found… he was right.

It was just like if he do not click in the Update button.

No more hint excepted it does once only. In an EditField, I moved (Cut / Paste) a line to set kids in the birth order (yes strange idea).

The next time (same Record, same Cut / Copy, it worked.

Ideas ?

I forgot:

This happens with Windows 10 (and Windows 7 before), old macOS (El Capitan here, more older for the user) and in the IDE (here), Xojo 2015r1 elsewhere.

I even compiled the application with the user’s computer - in case mine have troubles - after making a copy of the project (with images…) into an external SSD.

Can you show us the code that’s doing the Update? Off the top of my head is that I’d suspect that the sql isn’t doing what you think it’s doing but that’s just a guess without seeing code. DB API 1 code has been working well for us for many years.

Same here, it worked fine from 2013 to 2021… I do not have the code handly.

Nota: once the “Add, Modify, Delete” window is closed, the db is reloaded / redisplayed in the ListBox. Less than 2,000 Records.
I urged the guy to not use other applications concurently to that one (to avoid memory constaints). He used AVAST on the Windows machine, so I told him to make tests on his MacBook Pro.

Here’s the used code (I skipped most of the Record Fields, you get the construct style here…). There are 12 Fields in a Record. < 2,000 Records.

API1 (2015r1 for historic reasons)

Dim CaritasDB As SQLiteDatabase
Dim OneRecord As String

CaritasDB = New SQLiteDatabase
CaritasDB.DatabaseFile = App.gCaritasFI
If CaritasDB.Connect() Then
// a. Build the SQLExecute command string
OneRecord = "UPDATE Caritas "

// ---------- ---------- ---------- ---------- Prénom(s)
// Is Prénom(s) empty or not ?
If TF_PNom.Text = "" or TF_PNom.Text = " " Then
  OneRecord = OneRecord + "SET PNom='', "
  OneRecord = OneRecord + "SET PNom='" + ReplaceAll(TF_PNom.Text,"'","’") + "', "
End If

// all other fields skipped… here

// Add the unique ID part
OneRecord = OneRecord + "WHERE ID='" + Str(App.gUniqueID) + "'"


CaritasDB.SQLExecute (OneRecord)
If CaritasDB.Error Then
  MsgBox "Modification d’une carte" + EndOfLine + EndOfLine +_
  "Une erreur " + Str(CaritasDB.ErrorCode) + " s’est produite: " + CaritasDB.ErrorMessage
  // Validate the write change
  // Set the Changed Flag to True
  Nav_DB_Changed = True // Allow, elsewhere, to reload the whole db in the ListBox…
End If

My first thought is that App.gUniqueID is not correct. Like maybe it’s 0. It wouldn’t generate an error and obviously wouldn’t actually update anything. I’d log the SQL statement to make sure.

It might be that you have multiple events that are causing the update statement to run but the whatever sets the ID isn’t set first.

\I find it odd that your record ID is set at the App level and not locally. Kind of weird and not what I would do - ever. Keep those variables as local as possible.

does xojo used a sqlite.dll in system folder ?

where does the gUniqueID comes from?
can you see it in a output window somehow?
it the App.gUniqueID=0 i would throw an error at start.
does the user start the exe direct and maybe not a link with it arguments?

Thank you Bob.

I have a List window (the main window) and the other one where I can Add, Modify, Delete…

You are right: an invalid ID do nothing (I can go to Record ID i: nothing is done if there is no Record with that ID).

Actually, the machine do not have Internet access, etc.

I’d log the SQL statement to make sure.
If the bug as easy to encounter, I’d do that right now ! But for the whole ellapsed year (I made some attempts to reach the bug), I never saw the bug (only once, yesterday).

But, I think - maybe - something in a Field may actually bug the UPDATE (since I do not check what’s in each field - excepted for the 3 dates: no possible error there - ).

I will read if I add some tests (against multi lines, whatever) in some fields (Control values). I recall having seen more than one line where only one have to be there. Users are capable of anything.

On the other hand, I have the last db file and wait a phone call if / when a new Record refuses to be modified or deleted.

I have a modification pending: the user can display the View by List OR the View by Record * (no more relationship between both). That will allow me to use a db_ID in each window.

  • With a search feature.