SQL Server - Get value after INSERT

[nejamube]

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]

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]

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)
)