IDENTITY INSERT

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
swright
Posts: 32
Joined: Wed 23 Dec 2009 12:19

IDENTITY INSERT

Post by swright » Mon 28 Sep 2020 14:55

Hello is it possible to use SET IDENTITY INSERT [TABLE] ON with TMSTable

I can execute SET IDENTITY INSERT ON within a transaction, but TMSTable gives exception 'Field 'idField' cannot be modified.

I tried options.InsertAllSetFields and also get the same exception.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: IDENTITY INSERT

Post by Stellar » Tue 06 Oct 2020 13:00

To set a value for a key field when a new record is inserted, you need to add a custom SQL statement for TMSTable, for example:

Code: Select all

 CREATE TABLE [dbo].[DEPT5](
   	  [DEPTNO] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	  [DNAME] [varchar](20) NULL,
	  [LOC] [varchar](20) NULL)

Code: Select all

MSConnection1.Connect;
MSConnection1.ExecSQL('SET IDENTITY_INSERT Dept ON');

MSTable1.TableName := 'Dept';
MSTable1.SQLInsert.Text := ' INSERT INTO Dept ' +
                           '   (DeptNo, DName, Loc) ' +
                           ' VALUES ' +
                           '   (:DeptNo, :DName, :Loc) ';


// post record
MSTable1.FieldByName('DeptNo').ReadOnly := False;
try
  MSTable1.Insert;
  MSTable1.FieldByName('DeptNo').AsInteger := 1;
  MSTable1.FieldByName('DName').AsString := 'ACCOUNTING';
  MSTable1.FieldByName('Loc').AsString := 'NEW YORK';
  MSTable1.Post;
finally
  MSTable1.FieldByName('DeptNo').ReadOnly := True;
end;

Post Reply