How to recover the id entered in the sqlserver database using Delphi datasnap

Asked

Viewed 492 times

0

How to get the id that has just been inserted into the bank on the server side, use Delphi XE10.1 Sqlserver Bank and Firedac, I would like to take the id to use in the child table when it is master detail. as example below

procedure TsrmCadastro.dspPessoaBeforeUpdateRecord(Sender: TObject;
    SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;var Applied: Boolean);

var idPessoa : Integer;

begin
if UpdateKind = ukInsert then
begin
  if SourceDS = qryPessoa then
  Begin
    idPessoa              := ????????;
  End
  Else if SourceDS = qryPessoaJuridica then
  Begin
    DeltaDS.FieldByName('pessoa_juridica_id').NewValue := idPessoa;
  End;
end;
  • You can use SELECT SCOPE_IDENTITY() in SQL Server 2008+

  • I used as example below but returns Zero this code put in place of ??????? 

 qry := TFDQuery.Create(self);
 qry.Connection := dm.Conn;
 try
 qry.SQL.Add('SELECT SCOPE_IDENTITY() AS ID');
 qry.Open();
 idPessoa := qry.FieldByName('ID'). Asinteger; Finally Freeandnil(qry); end;

  • You have to run SCOPE_IDENTITY() immediately after INSERT. INSERT INTO dbo.tabela(nome) VALUES('Nome Sobrenome'); SELECT SCOPE_IDENTITY();

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.