SQL Server - Get value after INSERT

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
nejamube
Posts: 4
Joined: Sat 26 Nov 2011 00:45

SQL Server - Get value after INSERT

Post by nejamube » Sun 23 Jun 2013 15:48

Hello.

How I can get the value of PK after Insert with SQL SERVER.

I'm trying with these two forms without results.

    ID: = IBCQuery.ParamByName ('RET_ID'). AsLargeInt;
    ID: = IBCQuery.ParamByName ('ID'). AsLargeInt;

Thanks.

Code: Select all

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

nejamube
Posts: 4
Joined: Sat 26 Nov 2011 00:45

Re: SQL Server - Get value after INSERT

Post by nejamube » Sun 23 Jun 2013 17:53

Hello.

When running I get the error.

Field ID not found.

Code: Select all


          Close;
          SQL.Clear;
          SQL.Add(
            'INSERT INTO ventas ( ENTIDAD, ALMACEN, USUARIO, CLIENTE, VENDEDOR, VENTAREF, TOTAL, IVA, CAMBIO, REDONDEO, FECHA, HORA)');


          SQL.Add('values (:ENTIDAD, :ALMACEN, :USUARIO, :CLIENTE,' +
              ':VENDEDOR ' + ',' + sVentaRef +
              ', :TOTAL, :IVA, :CAMBIO, :REDONDEO, :FECHA, :HORA);');

 [b]   Options.ReturnParams := True;
[/b]

[b]SQL.Add(' SET :ID = SCOPE_IDENTITY(); ');[/b]

 ExecSQL;

 ID:= FieldByName('ID').AsInteger;


When testing step check that this code is never executed.

Code: Select all

procedure TfrmVentas.UniQueri1BeforeUpdateExecute(Sender: TDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
begin


  If stInsert IN StatementTypes then // otherwise there is no param with this name
    Params.ParamByName('CLAVE').ParamType := ptInputOutput;


end;

Thanks.

AndreyZ

Re: SQL Server - Get value after INSERT

Post by AndreyZ » Tue 25 Jun 2013 07:54

Hello,

TMSQuery returns the IDENTITY field value automatically by default. It is controlled by the QueryIdentity option (the default value is True). QueryIdentity is used to specify whether to request the IDENTITY field value on execution of the Insert or Append method. Here is a code example:

Code: Select all

MSQuery1.SQL.Text := 'select * from testtbl';
MSQuery1.Open;
MSQuery1.Append;
MSQuery1.Post; // here the ID IDENTITY field will be filled with the value obtained from the server
ShowMessage(IntToStr(MSQuery1.FieldByName('id').AsInteger));
If you want to execute the INSERT statement, you should get the value of SCOPE_IDENTITY() in your query. Here is a code example:

Code: Select all

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('insert into testtbl(fullname, age) values(:fullname, :age)');
MSQuery1.SQL.Add('select SCOPE_IDENTITY()');
MSQuery1.Execute;
ShowMessage(IntToStr(MSQuery1.Fields[0].AsInteger));
, where testtbl is the following table:

Code: Select all

CREATE TABLE testtbl(
  id INT IDENTITY,
  fullname VARCHAR(50) NULL,
  age INT NULL,
  CONSTRAINT PK_testtbl PRIMARY KEY (id)
)

Post Reply