IDENITY Result

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
GNiessen
Posts: 28
Joined: Wed 18 Jan 2012 20:15

IDENITY Result

Post by GNiessen » Tue 27 Nov 2012 19:42

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

Re: IDENITY Result

Post by AndreyZ » Wed 28 Nov 2012 12:13

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
Posts: 28
Joined: Wed 18 Jan 2012 20:15

Re: IDENITY Result

Post by GNiessen » Wed 28 Nov 2012 22:30

Thanks, that seem clearer. I will try that.

AndreyZ

Re: IDENITY Result

Post by AndreyZ » Thu 29 Nov 2012 08:23

If any other questions come up, please contact us.

Post Reply