Not getting Identity key value

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 38
Joined: Thu 05 Oct 2006 04:22

Not getting Identity key value

Post by Japhar » Thu 21 Dec 2006 16:38


1. I have an query with simple select statement in MSQUERY component

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()

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
Active:= true;
FieldByName('empname').AsSting := 'TestEmp';
emp_id_key := FieldByName('empid').AsInteger;
raise Exception.create('Unable to return generated serial key during insert.');

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....

Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Fri 22 Dec 2006 15:43

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.

Post Reply