Devart Forums

Posted: **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.

Posted: **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));

Posted: **Wed 28 Nov 2012 22:30**

Thanks, that seem clearer. I will try that.

Posted: **Thu 29 Nov 2012 08:23**

If any other questions come up, please contact us.

Devart Forums

IDENITY Result

IDENITY Result

Re: IDENITY Result

Re: IDENITY Result

Re: IDENITY Result