Page 1 of 1

SCOPE_IDENTITY worked before, not anymore

Posted: Thu 07 Dec 2006 06:30
by Zoran565
I need to get IDENTITY of the record I just inserted so that I can use it for other tables. In one of my older programs I used TMSSQL component with this SQL code:


Parameters are all IN, except RowId which id IN/OUT. At the beginning of the program I do TMSSQL.Prepare.

It worked fine few months ago. I wrote a new program yesterday and it doesn't work anymore. I get this error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Did something changed in TMSSQL component? I am using the latest build. Database engine is the same (MS SQL Server 2005).

Or, what is the proper way to get IDENTITY now?


Posted: Thu 07 Dec 2006 09:04
by Jackson
Thank you for information.
We have reproduced the problem and fixed it. This fix will be included in the next SDAC build.

Posted: Thu 07 Dec 2006 15:03
by Zoran565
Hi Evgeniy

Can I get the fix? I need to run this program today.... and I can't re-install the old version of SDAC because I don't have it anymore (it was overlayed with the latest version).

Is it possible to get updated build? I don't have the source.


Posted: Thu 07 Dec 2006 15:16
by Jackson
Please send us (evgeniym*crlab*com) your license information.

Scope identity

Posted: Fri 28 Sep 2007 12:00
by pgawli
In version I still cannot retrieve identity value after insert.
Now I have version but results are the same.
Is this problem solved ?
Should I change some options in TMsQuery ?

Best regards.
Piotr Gawlicki

Posted: Fri 28 Sep 2007 12:06
by Sergio Bertolotti
Dear Piotr,

use TMSQuery.BeforeUpdateExecute event to set parameter type to ptInputOutput for your insert statement.

For example:
procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
StatementTypes: TStatementTypes; Params: TMSParams);
if stInsert in StatementTypes then
Params.ParamByName('PARAM_IDENTITY').ParamType := ptInputOutput;

Don't forget to set property TCustomDADataSet.Options.ReturnParams to True.

With this settings and SDAC I don't have any type of problems.

Best regards.

Sergio Bertolotti