.SQLExecute("TRUNCATE TABLE " + Table_Name + ";")
Does not seems to work.
The reported error is:
Erreur n° 1; Erreur near “TRUNCATE”: syntax error.
Syntax source:
Scroll down a bit the page to TRUNCATE TABLE.
I used DROP TABLE instead, and add a PopupMenu to add back the TABLE.
In this project design, I have three “Service” TABLEs that have a single Record. But my actual code adds a new record each time the project runs.
Yes, it’s a bug I have to squash. 
If the table does not exist (first ever run), create the table and at the same time add a record with empty or default values for its columns. Later, when you come to create a real entry, use UPDATE instead of INSERT.
No, I will made a check:
if there is no Row, it is a first run, so I add one,
Else, I do nothing.
That part of code was “Optimistic coding”… but only one TABLE (About) do that at first; now a simple rewrite leads to all three TABLES get more Rows… (but not the main TABLE as Rows are added by the user(s)…)
A google search returned:
Faster to DROP the TABLE and add one Row (and squash the bug) instead of DELETE all Rows (in a loop since at run time I do not know how many there are…)
Thanks Tim for the advices.
Doesn’t matter how many. Just say DELETE FROM MYTABLE and all rows go.
Funny, I just checked (at www.w3schools.com) and I do not found DELETE TABLE there !
That was why I searched something else, found DROP TABLE, then TRUNCATE TABLE (same page).
SQLite does not do some things, which is partly why it is Lite. As a result I always check at sqlite.org.
For DELETE FROM MYTABLE see:
Thanks.
I found the way to avoid adding continuously a Row, the same row, run after run.
In the initialisation code, I check if I need to add the TABLE, then:
Dim gSQLite_RS As RecordSet
gSQLite_RS = gSQLite_DB.SQLSelect("SELECT * FROM About")
If gSQLite_RS.RecordCount = 0 Then
// Ajoute la Fiche
gSQLite_DB.SQLExecute(Add_Cmd)
DB_Error()
End If
Add_Cmd holds the Add a Row command string.
…for each TABLE where I only need to set one Row (to be used later in the project like ListBox header strings, db file version, etc.)
Nothing beats a small project to prototype a code snipped: fast to run / debug / made changes…
That’s a bad idea my friend. You are requesting all fields, n times (maybe millions) just to know if something exists?
Please use ONE column, as some id field (or a literal as ‘OK’ or 1 if it is just for checking), a WHERE selecting what you are interested in, and LIMIT 1 (if you just want to know if something exists), in your SELECT statement.
Avoid RecordCount and just check if the RowSet returns AfterLastRow (true), if so, it has not found any record.
Rethink if you can’t do it better. 
Now that he has it cleaned up, there is only one row in each table. There will never be more (unless he introduces another bug).
So SELECT ID FROM MYTABLE will suffice, followed by a .RowCount check.
Is there ANY row in MYTABLE?
Var SQL_check As String = "SELECT 1 FROM MYTABLE LIMIT 1;"
Var rs As RowSet = db.SelectSQL(SQL_check)
If rs.AfterLastRow Then
// It is empty
Else
// There is data there
End
Is there ANY row of “type” ‘comics’ in MYTABLE?
Var SQL_check As String = "SELECT 1 FROM MYTABLE WHERE type = 'comics' LIMIT 1;"
Var rs As RowSet = db.SelectSQL(SQL_check)
If rs.AfterLastRow Then
// There's no comics records
Else
// There are
End
Thank you Rick for the info.
I do not thin at that at all.
As I already say, there are 3 service TABLEs, they need 1 Row, and the number of COLUMNs is small:
About have 3 COLUMNs (my name, what do this software, and what this db file use is)
Creator have 5 COLUMNs (Creator, Release Date, software release date, user of this software - Company Name)
Headings have 12 COLUMNs (holds the ListBox Header Strings)
And the main TABLE who hold the user entered / readed data. This one is created only at software run. No Row added by default.
This is probably why I do not think about time to request/load the TABLEs contents.
These process: creating the working folder(s), adding the color, print, etc. preferences, the default .sqlite file, adding and checking for the service TABLEs takes few time (unnoticeable) on my 8 years old MBP / El Capitan. So with more recent laptop, it will takes certainly less.
I like work that is well done.
I will investigate your code as now I have some times to do that (fine tune the software).
Works fine.
Thank you Rick.
I will look at your second snipped after lunch; I’m hungry.
I do not realised your shared code is API2. Back at home (yesterday), I adapted it to Xojo 2015r1 and it works fine.
THANK YOU.
In 2 years your compiler will be 10 years old. I gradually abandoned API1 3 years ago.
Yes, I know. A long way.
I made this application ProBono, so…
But, I do the first changes using Xojo 2021r2.1 (because I can read the project with 2015r1, export as XML, strip “deprecated items”, save and load with 2015r1…).
Regards from Strasbourg, France.