使用Microsoft.Jet.OLEDB.4.0从C＃中将行插入Access数据库，自动编号列设置为零

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

我正在使用C#和Microsoft.Jet.OLEDB.4.0提供程序将行插入Access mdb。

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

是的,我知道Access很糟糕。这是一个巨大的遗留应用程序,其他一切正常。

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.

我用Google搜索了这个问题并阅读了有关此主题的所有文章。有人建议为自动编号列插入-1,但这不起作用。我找不到其他建议。

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

我正在使用OleDbParameter,而不是连接一个大的SQL文本字符串。

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

2 个解决方案

#1

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.

在Access中,可以将显式值INSERT到IDENTITY(a.k.a。自动编号)列中。如果您(或您的中间件)正在将值零写入IDENTITY列,并且IDENTITY列上没有唯一约束,则可能会解释它。

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.

列列表应省略IDENTITY列。 Jet SQL将允许您省略整个列列表,但随后隐式包含IDENTITY列。因此,您应该使用INSERT INTO()语法显式省略IDENTITY列。

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.

在Access / Jet中,您可以将明确的值写入IDENTITY列,在这种情况下,该值显然不会自动生成。因此,请确保您和您的中间件(ADO.NET等)未明确将零值写入IDENTITY列。

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

BTW仅针对下表中的IDENTITY列将每秒自动生成零值INSERT:

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

#2

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.

在执行插入时,您需要确保没有为AutoNumber列指定值。就像在SQL Server中一样,您不会为标识列插入值。

#1