Get autoinc identity after pure insert statement

[MarcoTheFirst]

Hi

I'm using D2007 Win32 with SDAC Pro 4.00.0.6

I have a PURE INSERT statement that I need to run. There's no SELECT that precedes the insert statement. I need to get the value of the identity-row back after the insert statement, and it should be a performant way.

1. I tried every hint I found in this forum but couldn't get it to work. It seems like everyone is working with TMSQuery and a SELECT statement which then generates an automatic INSERT statement. That's not what I want to do since I don't need to select any records.

2. I don't want to use stores procedures because I need to maintain database portability.

3. Here's what I tried so far (both with TMSSQL and with TMSQuery)

MSSQL1.SQL.Text := 'INSERT INTO Ref(Title,Kind,Pub) VALUES (''New
Referene'',''LINK'',''False'') SET :ID=SCOPE_IDENTITY()';
MSSQL1.Execute();
FRefID := MSSQL1.ParamByName('ID').AsInteger; //--> RETURNS ZERO

--> I tried the same with TMSQuery and FieldByNAme('ID').AsInteger - but since I don't have any select statement and thus no fields an exception was raised.

Any help would greatly be appreciated.

Marco

[Jackson]

You should specify the parameter data type and set the parameter type to ptInputOutput before executing the query. For example:

Code: Select all

MSSQL1.SQL.Text := 'INSERT INTO Ref(Title,Kind,Pub) VALUES (''NewReferene'',''LINK'',''False'') SET :ID=SCOPE_IDENTITY()';
MSSQL1.ParamByName('ID').DataType := ftInteger;
MSSQL1.ParamByName('ID').ParamType := ptInputOutput;
MSSQL1.Execute;
FRefID := MSSQL1.ParamByName('ID').AsInteger;

[MarcoTheFirst]

Works like a charm!

Thank you so much!

Marco