SQL AUTO_INCREMENT problem....

skipsy

Member
Licensed User
Longtime User
Hi all,

Following this forum exemples, I did this test :

B4X:
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 :
B4X:
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

Active Member
Licensed User
Longtime User
Because you're always inputting NULL instead of an int into "id". I might be wrong, but I'm quite sure.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Replace this code:
B4X:
SQL1.ExecNonQuery("CREATE TABLE conso (id INTEGER UNSIGNED AUTO_INCREMENT, date DATE, observation TEXT)")
by this one:
B4X:
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.
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
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

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

skipsy

Member
Licensed User
Longtime User
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.
 
Upvote 0
Top