how to get last auto-incremented by mssql?

[bugmenot1]

pls get me a demo.

[AndreyZ]

Hello,

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;

If you are using your own SQL query, you should add this line to your query: SET :Identity_field = SCOPE_IDENTITY()
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;

[TinTin]

UniQuery.SQLInsert.Add('SET :DEPTNO = SCOPE_IDENTITY()');
------------------------

sometimes "SCOPE_IDENTITY()" is not correct,if i use "IDENT_CURRENT('TableName')"

So I Write :
UniQuery.SQLInsert.Add('SET :DEPTNO = IDENT_CURRENT(''DEPT'')');

-----------
??

[AndreyZ]

This problem can be caused, for example, by using triggers. Please take a look at this article: http://msdn.microsoft.com/en-us/library/ms190315.aspx . There you will find an explanation of possible different values which are returned by SCOPE_IDENTITY and IDENT_CURRENT.