auto increment C# and access

[zguidon]

Hi everybody,

I have a DataBase defined in Access. One column is auto incremented. I am trying to insert a row through a code in C#. I don't include data in the auto incremented column, because it is auto incremented..., but the computer returns that the number of columns does not match the inserted columns. If I do the same in SQL DataBase it works perfectly. Are there any esential limitation in Access in this respect that doesn't exist in SQL or anything is wrong in my code?

Thanks,
Guidon

[Jx_Man]

>> anything is wrong in my code?
post the code

[Dabuskol]

Recheck the number of columns in your insert statement.

[zguidon]

Thanks. My Access Database has 4 columns, the first is auto incremented.

The code is:

[makes the connection,...]
query.CommandText = string.Format("Insert into Orders values ('Tu', 3,4)");
query.Connection = conn;
conn.Open();
query.ExecuteNonQuery();
conn.Close();

If I write 4 columns the code works:

query.CommandText = string.Format("Insert into Orders values (6,'Tu', 3,4)");

Thanks for any answer
Guidon

[$dunk$]

I believe you are missing the fields clause. The syntax of the SQL Insert into statement is:

Insert Into TableName (FieldName1, FieldNameN) Values (Value1, ValueN);

If you just list the fields that you are setting then I think all should work as desired.

[Ken Sharpe]

That's the right answer -- sql server takes a guess at it, but access assumes that if you don't specify the columns, then you want to insert all the available columns. In your case that's not true -- you just want to insert all the columns except the primary key.

[zguidon]

Thanks to everybody, specially to $dunk$ and Ken Sharpe! Now it worked inserting the columns. That was the problem. In access you need the complete syntax, which is not necessary in Sql

Thanks again,
Guidon

[Ken Sharpe]

Just for the record, it's a better idea to have the "complete syntax" as you say, rather than leave it up to interpretation.

For example, let's say your code had worked without specifying the columns. One month from now, you'll want to add a column called "color." You'll add the column, and all of a sudden your code will break because it's trying to insert into the color column but your old code doesn't specify a value.

It's a good idea to be as explicit as you can be.