Not getting Identity key value

[Japhar]

Hi,

1. I have an query with simple select statement in MSQUERY component
'SELECT * FROM EMP WHERE EMPID = 0'

EMP TABLE having 2 columns empid, empname where empid is identity key.

2. Added empid, empname as persistenece fields and empid is set to 'arAutoInc'.

3. For MSQUERY component Retrun params is set to TRUE.

4. MSQUERY.SQLINSERT having only one statement SET :empid=SCOPE_IDENTITY()

NOTE:
a. I don't have any insert statement in SQLINSERT.
b. I have not written OnUpdateExecute event. Because i don't have any parameters to the query.
c. CachedUpdate is Set to TRUE for MSQUERY

On button click, i'm executing below code

with MSQUERY do
begin
Close;
try
Active:= true;
Insert;
FieldByName('empname').AsSting := 'TestEmp';
post;
emp_id_key := FieldByName('empid').AsInteger;
except
raise Exception.create('Unable to return generated serial key during insert.');
end;
end

PROBLEM: I'm not getting identitykey value in this scenario. To get identity key what changes i have to do here? In normal cases identity key is working fine. This is very special case in my project...

Can we get identity key if we doesn't have insert query in SQLINSERT?

Wating for your suggestion....

[Jackson]

In your example you use CachedUpdates mode.
When using this mode, updates to a dataset (such as inserting new records) are stored in an internal cache on the client side instead of being written
directly to the dataset's underlying database tables. When changes are
complete, an application writes all cached changes to the database in the
context of a single transaction.
Indentity fields values are generating by server at the moment of inserting
new record. So you can't get the Identity field value after the insert
operation in CachedUpdates mode because this value doesn't exist until
updates are applied (call of the method TMemDataSet.ApplyUpdates).
If usage of CachedUpdates mode is not necessary for you, just turn it off.
For more information about CachedUpdates mode please see SDAC help.
One more issue in your example you should pay attention on the value of the SQLInsert property.
If you assign some SQL statement to this property, it will be used when applying an insertion to a database.
In your case no INSERT statement is provided but only SET :empid=SCOPE_IDENTITY().
Do not assign any values to this property and SDAC will automatically generate it for you in run time on applying an insertion to a database.
For more information about this property please see SDAC help.