SQL auto_increment

[Ramirez]

Hello, it's again me

I want to make an index (primary key) in a SQL table and want to use the AUTO_INCREMENT parametre

'Table creation
query="CREATE TABLE IF NOT EXISTS [table] ([Index] INTEGER AUTO_INCREMENT PRIMARY KEY, [Data1] TEXT, [Data2] TEXT)"
SQL1.ExecNonQuery(query)

To Add a data, I try this code:

' Add data
query="INSERT INTO table VALUES ('text1', 'text2')"
SQL1.ExecNonQuery(query)

I receive a message "Table has 3 column but only 2 values were supplied"
So I change my program to :

To Add a data, I try this code:

' Add data
query="INSERT INTO table VALUES ('','text1', 'text2')"
SQL1.ExecNonQuery(query)

But now, when I check the table (SQLViewer), the column 'index' is empty.

Where is my mistake ?

A+

 

[Kiffi]

try this one:

' Add data
query="INSERT INTO table ([Data1], [Data2]) VALUES ('text1', 'text2')"
SQL1.ExecNonQuery(query)

Greetings ... Kiffi

EDIT:
BTW: It's no good idea to name a Table 'table' because it is a reserved word.

 

[klaus]

I use following code:

query="INSERT INTO table VALUES (NULL, 'text1', 'text2')"
SQL1.ExecNonQuery(query)

The first column is declared as INTEGER PRIMARY KEY.

Best regards.

 

[Ramirez]

OK thx all !

It works !

I delete the AUTO_INCREMENT from the creation of the table because an error message appears, and that work too.

Kiffi: don't worry I don't use the name 'table' for my table in my program, I just use this name to be more explicite.

Thanx again

 

[ciginfo]

And is this right?

SQL1.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, ?)", Array As Object(Null, Text1, Text2))

Thank you