Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Returning ID value after call to ExecSQL. |
Sat, Apr 7 2012 8:34 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Sat, Apr 7 2012 8:56 AM | Permanent Link |
Uli Becker | 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 |
Sat, Apr 7 2012 9:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sat, Apr 7 2012 11:57 AM | Permanent Link |
Adam Brett Orixa Systems | 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!
|
Sat, Apr 7 2012 12:21 PM | Permanent Link |
Adam Brett Orixa Systems | 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! |
Sun, Apr 8 2012 8:07 AM | Permanent Link |
Uli Becker | 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 |
This web page was last updated on Monday, May 6, 2024 at 12:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |