Issue with autoincrement Primary Key Identity(1,1) on MSSQL

[peter.ineichen]

I've a table with autogenerated primary key:

Code: Select all

CREATE TABLE [dbo].[device] (
[device_id] int IDENTITY(1, 1) NOT NULL,
[name] varchar(64) NOT NULL,
[location] varchar(64) NULL,
PRIMARY KEY CLUSTERED ([device_id] ASC))

When inserting a new device, the newly created Primary Key Value is not given back.

Code: Select all

  Device := TDevice.Create;
  Device.Attach(EntityContext);
  Device.Name := 'New Device';
  Device.Location := 'New Location';
  Device.Save;

  ShowMessageFmt('DeviceId: %d', [Device.DeviceId]);

  Device.Location := 'City';
  Device.Save;

The message is always: "DeviceId: 0", but the value in the database is much higher... And the second Device.Save will not update the object in the database.

Code: Select all

  <Table Name="dbo.device" Member="Devices">
    <Type Name="Device" ed:Guid="a95e3ad3-aacb-41db-8f71-8851ea943897">
      <Column Name="device_id" Member="DeviceId" Type="Integer" AutoSync="OnInsert" DbType="INT NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" ed:ValidateRequired="True" ed:Guid="e3a7b0eb-65ad-425d-b381-fde58a37b8a4">
        <Generator Name="Identity">
          <GeneratorParameter Name="GeneratorFires" Value="OnCreate" />
        </Generator>
      </Column>
      <Column Name="name" Member="Name" Type="String" DbType="VARCHAR(64) NOT NULL" CanBeNull="false" UpdateCheck="Never" MaxLength="64" ed:ValidateMaxLength="64" ed:ValidateRequired="True" ed:Guid="52e212f7-3036-45a5-a965-fe0fecb9b713" />
      <Column Name="location" Member="Location" Type="String" DbType="VARCHAR(64)" CanBeNull="true" UpdateCheck="Never" MaxLength="64" ed:ValidateMaxLength="64" ed:ValidateRequired="False" ed:Guid="2fe6be0c-2b10-4110-b738-e11aa987edb5" />
    </Type>
  </Table>

What i'm doing wrong? Thanks!

[AlexP]

Hello,

We are unable to reproduce the problem that is related to possibility of getting a new value for an "autoincremented" field.
Please send us the complete project, that includes a model. Also, indicate an exact versions of the client and the server. The email address is alexp*devart*com .

An error that is related with data update is already fixed. The fix will be available in the next build.

[peter.ineichen]

Hi Alex

Any news about this issue? I've sent you the sample project a few days ago. Do you received it?

[AlexP]

The DevicesId field in your model is specified as a key field, but the method to retrieve the auto-incremental value is not specified. In order for your sample to work, you should set the IdGenerator property for this field to Identity. In addition, enable the IDENTITY_INSERT option.

[peter.ineichen]

Hi Alex

Setting the IdGenerator to Identity and setting IDENTITY_INSERT to ON, the first save (INSERT) works fine. But the second save (UPDATE) will fail with "Cannot update identity column 'device_id'"

Where is the option for "IDENTITY_INSERT"? I only found it in the SDAC TMSDumpOptions...

[AlexP]

We have already fixed the problem with record update, this fix will be included in the next EntityDAC version. To set IDENTITY_INSERT, you should run the following command: SQL - SET IDENTITY_INSERT TableName ON.

[WimNki]

I've the same error, whatever I try. Where can I set the IdGenerator property for this field to Identity?
How can I do SET IDENTITY_INSERT TableName ON from EntityDAC?
The problem is that this is a SESSION parameter, so must be set for every sql statement.
Thank you
Wim

[AlexP]

there is no such option in EntityDAC. Presently, to set IDENTITY_INSERT, you can execute the following query:

Code: Select all

EntityConnection.ExecuteSQL('SET IDENTITY_INSERT TableName ON');