autoincrement primary key

gapi

Active Member
Licensed User
Longtime User
Hi all, I've a table with classic 'id' field for key, but when I add more one record, return me a integrity error, because it add id = 0 in all insert query !!!

How can select and add (+1) with DBUtils ?

thanks
 

gapi

Active Member
Licensed User
Longtime User
:BangHead: I set it to Null ... not seem to work, here the code:
B4X:
Sub WriteInDatabase
Dim ListOfMaps As List
Dim codCliente, codDitta, destinaz As String

Dim d As Long
d = DateTime.DateParse(txtData.Text)
DateTime.DateFormat = "yyyy-MM-dd"


ListOfMaps.Initialize
codCliente = showOneCliente.cliente_codice
codDitta = showDitte.ditta_selezionata
destinaz = spinDestMerci.GetItem(0)

    Dim m As Map
    m.Initialize
   m.Put("id", Null)
    m.Put("data", DateTime.Date(d))
    m.Put("codCliente", codCliente)
   m.Put("codDitta", codDitta)
    m.Put("destinaz", destinaz)
    ListOfMaps.Add(m)

DBUtils.InsertMaps(Main.SQL_Ordini, "ord_tes", ListOfMaps)
End Sub
 
Last edited:
Upvote 0

barx

Well-Known Member
Licensed User
Longtime User
Is the ID column set as Primary Key in the database?

If it is then all I do is create the map but don't include ID in the map. Seems to work fine for me.
 
Upvote 0

gapi

Active Member
Licensed User
Longtime User
Hi Barx, the database I created some time ago, if I open it with firefox plugin (SQLite Manager) dump creation is this: CREATE TABLE "ord_tes" ("id" INT (11) PRIMARY KEY NOT NULL DEFAULT (null) "date" DATE, "codCliente" VARCHAR (10), "dest" VARCHAR (10), "codDitta" VARCHAR (6), "payment" VARCHAR (10), "scCassa" VARCHAR (5), "transport" VARCHAR (10), "notes" VARCHAR (255), "transmitted" VARCHAR (1) NOT NULL DEFAULT ('N'))

is correct ? tnx
 
Upvote 0

gapi

Active Member
Licensed User
Longtime User
It should be PRIMARY KEY AUTOINCREMENT and you should omit the id from the map.

thanks erel, what's the sqlite dump for modify the primary key in auto_increment ? I try with
B4X:
ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
... not work :(
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
Create a new temporary table, holding the original structure except from the id column which you set to autoincrement. Then copy all data from your 'old' table to the 'new one', drop the first, rename the second. Dropping should be done after you are sure that the transactions went completed successfully.
 
Upvote 0

gapi

Active Member
Licensed User
Longtime User
thanks all, @mc73 which sqLite manager recommend for it? tnx


[solved] thanks to all ;)
 
Last edited:
Upvote 0
Top