If I have a TEDBQuery object & write this code:

begin
 Q.SQL.Text := 'INSERT INTO Communications (DateSent, WhoTo, WhoFrom) VALUES (:DateSent, :WhoTo, :WhoFrom)';
 Q.Prepare;
 Q.ParamByName('DateSent').asDate := Date;
 Q.ParamByName('WhoTo').asString := 'a';
 Q.ParamByName('WhoFrom').asString := 'b';
 Q.ExecSQL;
end;

After I call ExecSQL is it possible to return the value of the ID field created by the INSERT process, so I can locate the resulting record?

... I am sure I remember seeing a post about how to do that ... but I can't find it anywhere.

Adam,

> If I have a TEDBQuery object&  write this code:
>
> begin
>    Q.SQL.Text := 'INSERT INTO Communications (DateSent, WhoTo, WhoFrom) VALUES (:DateSent, :WhoTo, :WhoFrom)';
>    Q.Prepare;
>    Q.ParamByName('DateSent').asDate := Date;
>    Q.ParamByName('WhoTo').asString := 'a';
>    Q.ParamByName('WhoFrom').asString := 'b';
>    Q.ExecSQL;
> end;
>
> After I call ExecSQL is it possible to return the value of the ID field created by the INSERT process, so I can locate the resulting record?

Use something like this:

  with MyQuery do
  begin
    SQL.Text:='INSERT INTO MyTable VALUES (:NewKey, Value1, Value2)';
    ParamByName('NewKey').ParamType:=ptInputOutput;
    ExecSQL;
    ShowMessage('New key value is '+ParamByName('NewKey').AsString);
  end;

Regards Uli

Adam


I think this is what you need: on line manual

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=LASTIDENTITY

Notice that you don't need a FROM clause anymore for such queries:

SELECT LASTIDENTITY('Customer', 'CustNo') AS LastCustNo


Roy Lambert [Team Elevate]

Thank you both ... LASTIDENTITY ... obvious once you say it, but hard to find in the manual, hidden away in "numeric functions" ... not the first place I would think to look!

Uli  

>>ptInputOutput;

... phew ... I didn't realise that Params could be output as well as Input ... that opens up a whole bunch of new ideas!

Adam,

>   ... phew ... I didn't realise that Params could be output as well as Input ... that opens up a whole bunch of new ideas!

Another use of output params: (no FETCH FIRST necessary):

SCRIPT
BEGIN
  DECLARE FValue VarChar DEFAULT '';
  Execute Immediate 'select Field into ? from MyTable where MyTableID =
100' using FValue;
END

Uli