SQL AUTO_INCREMENT problem....

[skipsy]

Hi all,

Following this forum exemples, I did this test :

SQL1.Initialize(File.DirRootExternal, "bar.db", True)

SQL1.ExecNonQuery("CREATE TABLE conso (id INTEGER UNSIGNED AUTO_INCREMENT, date DATE, observation TEXT)")

SQL1.ExecNonQuery("INSERT INTO conso VALUES(NULL, '01/01/13', 'Obs 1')")
SQL1.ExecNonQuery("INSERT INTO conso VALUES(NULL, '02/01/13', 'Obs 2')")


Cursor1 = SQL1.ExecQuery("SELECT id, date,observation FROM conso")
For i = 0 To Cursor1.RowCount - 1
  Cursor1.Position = i
  Log(Cursor1.Position)
  Log(Cursor1.GetInt("id"))
  Log(Cursor1.GetString("date"))
  Log(Cursor1.GetString("observation"))
 Next
Cursor1.Close

Output result for 'id' is always 0 !!!
I have also tried this syntax :

SQL1.ExecNonQuery2("INSERT INTO conso VALUES(?, ?, ?)", Array As Object(Null, "01/01/13", "Obs 1"))

Same problem !!

does anybody knows whats wrong :BangHead:

Thks,
William.

 

[SCIS]

Because you're always inputting NULL instead of an int into "id". I might be wrong, but I'm quite sure.

 

[klaus]

Replace this code:

SQL1.ExecNonQuery("CREATE TABLE conso (id INTEGER UNSIGNED AUTO_INCREMENT, date DATE, observation TEXT)")

by this one:

SQL1.ExecNonQuery("CREATE TABLE conso (id INTEGER PRIMARY KEY, date TEXT, observation TEXT)")

DATE is not a standard data type in SQLite 3.
Look here Data types in SQLite.
And here Date and Time functions.

Where did you get the example from ?
Did you have a look at the SQLite chapter in the User's Guide ?

Best regards.

 

[sorex]

yes, some database don't even allow you to insert a value to an auto increment field (MSSQL for sure, MySQL allows it)

just use

SQL1.ExecNonQuery("INSERT INTO conso (date,observation) VALUES ('01/01/13', 'Obs 1')")

 

[skipsy]

Great !
Works fine. I did not understood that I must set the field as PRIMARY KEY.

Thank you too for the tips concerning the date format

William.