IDENTITY INSERT

[swright]

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]

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;