autoincrement primary key

[gapi]

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

 

[Erel]

You should omit this field or add NULL as its value.

 

[gapi]

:BangHead: I set it to Null ... not seem to work, here the code:

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

 

[barx]

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.

 

[gapi]

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

 

[Erel]

It should be PRIMARY KEY AUTOINCREMENT and you should omit the id from the map.

 

[gapi]

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

ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

... not work

 

[Erel]

It is AUTOINCREMENT. I don't think that ALTER TABLE supports it. You can see SQLite documentation here: Syntax Diagrams For SQLite

 

[mc73]

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.

 

[gapi]

thanks all, @mc73 which sqLite manager recommend for it? tnx


[solved] thanks to all