Kem (Thanks !):
I get Syntax Error on both lines. I use 1 because I only need 1 Record.
[code]TableName = “Heading”
db.SQLExecute(“DELETE FROM " + TableName + " LIMIT 1”
db.SQLExecute(“DELETE FROM Heading LIMIT 1”[/code]
Heading is the TABLE name.
Jean-Yves: Yes, I think I will go that way
Greg: I really do not know how this is possible.
I noticed that if I quit the application while the NAG screen is displayed, I get that. I was thinking I resolved the problem when I excluded Quit when the NAG (sorry: Information screen) is displayed. This never came back until I noticed that last week. I may also have to check and recheck my code in the App Event, specifically where I create the TABLES and fill them.
Anyway: I have to extract from my copy of the file a Table (Fisrst Name, Family Name, Photo and Unique ID): these are not in the Client computer (my addition for my own use). So I will export the whole DB contents and re-define it / create two files, import the data into their own file. Then I will check how I wrote Record(s) into the two Service Tables - I think I know: I check if TABLE Exists, then I fill it (in all cases ).
BTW: I tried to ALTER TABLE, but like the sqlite.com documentation says, this does not works in case of ADD Unique ID
The other service TABLE, with also is in excess of Records HAVE an Unique ID and therefore was really easy for me to delete the useless Records (and keep only 1).
a second reading after a coffee, a break, etc. leads me to understand where to search.
Yes, in my “ADD THIS SERVICE TABLE” method, I check in the TABLE already exists (to avoid the duplicate / error), but the Record ADD appears after and so can be executed many times (if a reason exists). I will change that.
In SQLite it WILL have a ROWID (a “hidden field”) unless you specifically created the table with a “WITHOUT ROWID” clause
If you specified a field of your own that is PRIMARY KEY INTEGER AUTOINCREMENT, than that Field will map to ROWID and can be accessed by either the name ROWID or the name you gave the Primary Key
But by DEFAULT all SQLITE tables have a RowID unless explicitly told not to
You don’t - thats why every table SHOULD have a primary key of some kind regardless if its “just a lookup table” etc
Otherwise you have to identify the row by column values and you still run the risk that if there are rows with duplicate values that you delete more than one row
[code] Dim Table_Name As String
Dim Row_ID As String
Dim LoopIdx As Integer
Table_Name = "Heading"
For LoopIdx = LB_Records.ListCount DownTo 2
Row_ID = Str(LoopIdx)
TableSQLite.SQLExecute("DELETE FROM " + Table_Name + " WHERE ROWID=" + Row_ID)
If UserCancelled Then Exit
Next[/code]
LB_Records is a Listbox reference where I display the TABLE Record Contents.
DownTo 2 have the adventage to keep one Record in the Table (exactly what I want).
Of course, the code above can be writtent a bit differently. For example, get the Heading TABLE RecordSet Count instead of the ListBoxs ListCount. That value canbe computed before the For / Next loop.
I used that (except the AUTOINCREMENT) and it does not works. SQLite.com documentation states here :
[code] The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:
The column may not have a PRIMARY KEY or UNIQUE constraint.[/code]
I now have code to remove the duplicate(s) Record(s) in the two involved TABLEs / modified the TABLE creation code for the one without Unique ID ( it was a simple mater of an End If location (I moved it to the end of my code, so it adds a new Record only if the code creates the Table and that makes sense - I do not saw that while coding).