SQLite: delete a record for its ID: no such column error

Getting Started
21

Ok thanks to all, you’re really precious !!!
I’m creating from scratch the db: DB Browser save my new database with .db extension, is the same of .sqlite one ?

22

The extension dosnt matter at all

1 Like
23

At this point i’m really lost …

I’ve created in DB Browser all that:

CREATE TABLE ‘Notifiche’ ( ‘ID’ INTEGER NOT NULL, ‘Cronologico’ TEXT, ‘Destinatario’ TEXT, ‘Via’ TEXT, ‘Civico’ TEXT, ‘Cap’ TEXT, ‘Citta’ TEXT, ‘Provincia’ TEXT, ‘Raccomandata’ TEXT, ‘UfficioPostale’ TEXT, ‘Giorno’ TEXT, ‘Mese’ TEXT, ‘Anno’ TEXT, ‘Parte’ TEXT, ‘Procuratore’ TEXT, ‘UfficioGiudiziario’ TEXT, ‘Pratica’ TEXT, ‘Atto’ TEXT, ‘Notificato’ TEXT, ‘Duplicato’ TEXT DEFAULT ‘NO’, ‘DGiorno’ TEXT, ‘DMese’ TEXT, ‘DAnno’ TEXT, ‘Ticket’ TEXT, PRIMARY KEY(‘ID’) )

CREATE VIRTUAL TABLE cNotifiche USING fts5(ID, Cronologico, Destinatario, Via, Civico, Cap, Citta, Provincia, Raccomandata, UfficioPostale, Giorno, Mese, Anno, Parte, Procuratore, UfficioGiudiziario, Pratica, Atto, Notificato, Duplicato, DGiorno, DMese, DAnno, Ticket)

cNotifiche_AFTER_INSERT

CREATE TRIGGER cNotifiche_AFTER_INSERT AFTER INSERT ON Notifiche BEGIN INSERT INTO cNotifiche(ID, Cronologico, Destinatario, Via, Civico, Cap, Citta, Provincia, Raccomandata, UfficioPostale, Giorno, Mese, Anno, Parte, Procuratore, UfficioGiudiziario, Pratica, Atto, Notificato, Duplicato, DGiorno, DMese, DAnno, Ticket) SELECT ID, Cronologico, Destinatario, Via, Civico, Cap, Citta, Provincia, Raccomandata, UfficioPostale, Giorno, Mese, Anno, Parte, Procuratore, UfficioGiudiziario, Pratica, Atto, Notificato, Duplicato, DGiorno, DMese, DAnno, Ticket FROM Notifiche WHERE new.ID = Notifiche.ID; END

cNotifiche_AFTER_DELETE

CREATE TRIGGER cNotifiche_AFTER_DELETE AFTER DELETE ON Notifiche BEGIN DELETE FROM cNotifiche WHERE ID = Notifiche.ID; END

And again after creating the first record in my database and try to delete it (from my Xojo App) i continue to receive the famigerate error message and i cannot delete the record neither within DB Browser.
Here’s the error message in DB Browser:

Schermata 2020-08-16 alle 00.31.54
Schermata 2020-08-16 alle 00.31.54838×324 32.1 KB

24

OLD VERSION

CREATE TRIGGER cNotifiche_AFTER_DELETE 
AFTER DELETE ON Notifiche 
BEGIN 
       DELETE FROM cNotifiche WHERE ID = Notifiche.ID; 
END

NEW VERSION

CREATE TRIGGER cNotifiche_AFTER_DELETE 
AFTER DELETE ON Notifiche 
BEGIN 
       DELETE FROM cNotifiche WHERE ID = OLD.ID; 
END

notice the second last line on both?? should be using OLD.ID instead of Notifiche.ID

1 Like
25

Thank you so much all of you @MarkusR, @TimStreater, @Ivan_Tellez, @Richard_Duke, @Gilles_Plante your effort and ‘king caparbiety’ in helping me (and all of us, novice or not in this forum) is like a rare gem! Hope in a future can return in someway … :hugs:
Now all works like a charm !

26

I delete a record in sqlite just like that
rem delete
Var nr As Integer = Textsatznr.value.Val
If kudb.Connect Then
KUDB.ExecuteSQL “DELETE FROM kunde WHERE ID = ?;”,nr
End If