how to get last auto-incremented by mssql?
Posted: Wed 18 May 2011 07:59
pls get me a demo.
If you are using UniDAC SQL Generator, you should set the TCustomUniDataSet.SpecificOptions.QueryIdentity option to True (check that you have the INDENTITY column in the result set). In this case the identity field value will be set automatically. Here is an example:
Code: Select all
procedure TMainForm.BitBtnWorkClick(Sender: TObject);
begin
UniQuery.SpecificOptions.Values['QueryIdentify'] := 'True';
UniQuery.SQL.Text := 'SELECT * FROM DEPT';
// table dept has three fields:
// DEPTNO INT IDENTITY(1,1) NOT NULL, DNAME VARCHAR(14) NULL, LOC VARCHAR(13) NULL
UniQuery.Open;
UniQuery.Append;
UniQuery.FieldByName('DNAME').AsString := 'test';
UniQuery.FieldByName('LOC').AsString := 'test';
UniQuery.Post; // here the value of the IDENTIFY column DEPTNO will be set automatically
end;In this case you will get the field value as an output parameter. Also you should set ParamType of the Identity_field parameter to ptInputOutput in the BeforeUpdateExecute event handler. Here is an example:
Code: Select all
procedure TMainForm.BitBtnWorkClick(Sender: TObject);
begin
UniQuery.SQL.Text := 'SELECT * FROM DEPT';
UniQuery.SQLInsert.Clear;
UniQuery.SQLInsert.Add('INSERT INTO DEPT(DNAME, LOC) VALUES(:DNAME, :LOC)');
UniQuery.SQLInsert.Add('SET :DEPTNO = SCOPE_IDENTITY()');
UniQuery.Open;
UniQuery.Append;
UniQuery.FieldByName('DNAME').AsString := 'test';
UniQuery.FieldByName('LOC').AsString := 'test';
UniQuery.Post;
end;
procedure TMainForm.UniQueryBeforeUpdateExecute(Sender: TDataSet;
StatementTypes: TStatementTypes; Params: TDAParams);
begin
if stInsert in StatementTypes then
Params.ParamByName('DEPTNO').ParamType := ptInputOutput;
end;