Android Question How to handle AutoInc?

[Harris]

I am trying to insert a new blank record with the first field set as PK (autoinc).

efCM.SQL1.ExecNonQuery2("INSERT INTO Emp VALUES (?,?,?,?,?,?,?,?)", _
  Array As String(0,"","","","",Null,0,""))

There are eight fields, and I expect the PK (first field) to get updated automatically.
I get error Primary Key must be unique (code 19) on the second insert.
What am I doing wrong?

Thanks kindly.

 

[DonManfred]

Try to use it without the field which has autoinc...

INSERT INTO tablename SET
field1="...",
field2="...",
fieldx="xxx";

With your code you give and id 0 to a field which has autoinc... Set all other fields with SET name=value except the one with autoinc and it should work

It should also work with

efCM.SQL1.ExecNonQuery2("INSERT INTO Emp (col1, col2, col3, col4, col5, col6, col7) VALUES (?,?,?,?,?,?,?)", _
  Array As String("","","","",Null,0,""))

Please note that the autoinc-field is NOT part of the fieldnames and not part of the values. So MySQL should use the autoinc for this field...

I prefer the first variant cause you have more control over the values... Like you see in my example it is more userfriendly readable. As result you have better control over errors which may occur

 

[Erel]

Two options:
1. Change Array As String to Array As Object.
2. Change the query to:

"INSERT INTO Emp VALUES (?,?,?,?,?,NULL,?,?)"

 

[Harris]

Thanks guys - makes more sense now...
Didn't know you could use SET with insert - thought it was for UPDATE only.