Page 1 of 1
SQL Server - Get value after INSERT
Posted: Sun 23 Jun 2013 15:48
by 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');
Re: SQL Server - Get value after INSERT
Posted: Sun 23 Jun 2013 17:53
by 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.
Re: SQL Server - Get value after INSERT
Posted: Tue 25 Jun 2013 07:54
by 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)
)