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.
IDENTITY INSERT
Re: IDENTITY INSERT
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;