Insert rows into Access db from C# using Microsoft.Jet.OLEDB.4.0, autonumber column is set to zero

Question

I'm using C# and Microsoft.Jet.OLEDB.4.0 provider to insert rows into an Access mdb.

Yes, I know Access sucks. It's a huge legacy app, and everything else works OK.

The table has an autonumber column. I insert the rows, but the autonumber column is set to zero.

I Googled the question and read all the articles I could find on this subject. One suggested inserting -1 for the autonumber column, but this didn't work. None of the other suggestions I could find worked.

I am using OleDbParameter's, not concatenating a big SQL text string.

I've tried the insert with and without a transaction. No difference.

How do I get this insert to work (i.e. set the autonumber column contents correctly)?

Thanks very much in advance,

Adam Leffert

Answer 1

When doing the insert, you need to be sure that you are NOT specifying a value for the AutoNumber column. Just like in SQL Server you don't insert a value for an identity column.

Answer 2

I'm not specifying a value.

I only tried that once, after running into the problem, because it was suggested in an article I found on the web.

Answer 3

In Access it is possible to INSERT an explicit value into an IDENTITY (a.k.a. Automnumber) column. If you (or your middleware) is writing the value zero to the IDENTITY column and there is no unique constraint on the IDENTITY column then that might explain it.

Just to be clear you should be using the syntax

INSERT INTO (<column list>) ...

and the column list should omit the IDENTITY column. Jet SQL will allow you to omit the entire column list but then implicitly include the IDENTITY column. Therefore you should use the INSERT INTO () syntax to explicitly omit the IDENTITY column.

In Access/Jet, you can write explicit values to the IDENTITY column, in which case the value will obviously not be auto-generated. Therefore, ensure both you and your middleware (ADO.NET etc) are not explicitly writing a zero value to the IDENTITY column.

BTW just for the IDENTITY column in the below table will auto-generate the value zero every second INSERT:

CREATE Table Test1 
(
   ID INTEGER IDENTITY(0, -2147483648) NOT NULL, 
   data_col INTEGER
);