IDENITY Result

[GNiessen]

I cannot find an example of getting an Identity_Result from a TMSQuery. I see there is a Option.QueryIdentity. But I don't see a property like qryTest.Identity that I can check after the query runs to get the ID.

I have a Table TESTTBL that has (ID, FullName, Age) with ID being the Identity field. So I am doing an "Insert TESTTBL values(:FullName, :Age)" which works, but I am not getting the ID back. I saw some other topics, but no specific example. I also searched the demo, but did not find anything there (other then a Stored Procedure with a return value). Nor in the help.

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

[GNiessen]

Thanks, that seem clearer. I will try that.

[AndreyZ]

If any other questions come up, please contact us.