Problems With Insert Query with Auto Number field

[liorjj]

Hi All,

I'm trying to insert record to Access DB to a tbale that contains
its first field as AutoNumber field using the following command:

"INSERT INTO Threads VALUES (" + category + "')";

And it gives me the following error: "Number of query values and destination fields are not the same".

As far as I'm understand I don't need to add the first field because it's AutoNumber, So why this error is always appearing?

Please Help,
10x

[Stewart Ross]

...

"INSERT INTO Threads VALUES (" + category + "')";

And it gives me the following error: "Number of query values and destination fields are not the same".
As far as I'm understand I don't need to add the first field because it's AutoNumber, So why this error is always appearing?...

Hi Liorjj. There are two forms of the INSERT statement: one in which you define the fields to be inserted and their insertion order, and the other in which you don't define the fields and instead rely on implied ordering. It is the implied order version you are using, where the SQL will match the first field in your table to the first item in the VALUES list, the second field to the second item and so on.

Because your autonumber field is the first one you can see that implied ordering is leaving you trying to overwrite this field, as you have not told it the actual name of the field you really want to insert.

You need to explicitly define the field name before your VALUES statement:

 
"INSERT INTO Threads FIELDS([FieldName]) VALUES (" + category + "')";
 

By the way, you seem to have an extra " ' " character at the end of your statement, before the closing bracket: " ') "

Regards

Stewart