Devart Forums

I am creating a new record using MSQuery with my INSERT statement in:

MSQuery.SQL

How do I retrieve the new record's IDENTITY value.
I have looked through various posts already in here but cannot find the method described in sufficient detail.

Can you help?

Thanks,
Steve.

Hi Steve,

try following :
1. add persistent field components to the query (right click at the query --> FieldsEditor --> right click --> All fields)
2. check the properties of the identity column (field), set 'AutoGenerateValue' to arAutoInc

this works in my code with SQL Server 2000

Another way to get the lastidentity is described in the CRDBGRID - Sample from SDAC - Package, check the TMSSQL and TMSQuery - objects in this project.


Hth
Tino

Posted: Thu Dec 29, 2005 5:38 am Post subject:

--------------------------------------------------------------------------------

Thanks Tino.

Can you explain further as I am still struggling with this:

try following :
1. add persistent field components to the query (right click at the query --> FieldsEditor --> right click --> All fields)

I did that

2. check the properties of the identity column (field), set 'AutoGenerateValue' to arAutoInc

How do I check the value - I used:

Value := MsQuery1.FieldByName('myID').AsInteger;

And this returns nothing.

this works in my code with SQL Server 2000

Another way to get the lastidentity is described in the CRDBGRID - Sample from SDAC - Package, check the TMSSQL and TMSQuery - objects in this project.

Still having difficulty understanding this idea and applying it in practice. Could not get this example to work readily so am looking at StoredProcUpdates. I see that the TMSQuery calls a stored procedure created by TMSSQL. The examples in the product only show information in grids. I want a user entry screen which has normal single fields for data entry. Should I use data-aware components? I am struggling to get it all to work together.

Thanks,
Steve.

Hi Steve,

i use Delphi, i think in other tools there is a similar way :
If you are within the list of the queries fields (right click at the query --> fields), select the identity field, look at object inspector and you will find the 'autoGenerateValue' - Property. Set it.

Well, the stored Procedure - Sample is more difficult, try the autogeneratevalue first.
It should work without data-aware components as well.

Tino

Hi Tino,

i use Delphi, i think in other tools there is a similar way :
If you are within the list of the queries fields (right click at the query --> fields), select the identity field, look at object inspector and you will find the 'autoGenerateValue' - Property. Set it.

I'm also using Delphi (v7).

I did as you suggest. What is your actual Delphi code to extract the value of the new record ID after an INSERT operation? (Presumably using the MSQuery component.)

I need to store that value for later use in my program.

Thanks,
Steve.

> I am creating a new record using MSQuery with my INSERT statement in:

You can use INSERT ...; SET :i = SCOPE_IDENTITY() statement. Don't forget to assign a value to ParamType property of :i.

But more preferable way is to use SDAC SQL Generator. To use it just leave clear SQLInsert etc properties.

Hi,

ok, i've tested it again and - maybe - there is another problem.

1. i have a table with an column defined as xID Integer Identity(1,1)
2. i place the MSQuery - object at the form with an active DB - Connection
3. i set the SQL - Property to 'SELECT * FROM MyTable'
4. i code the following

MSQuery1.Append;
MSQuery1.FieldByName('the_only_required_field').asString:='TEST';
MSQuery1.Post;

showMessage(MSQuery1.FieldByName('xID').asString);

--> i see the actual ID of the record

All that works without setting any other properties (SDAC sets the autogenerateValue automaticly ). The Options - Properties of the query and the connection are all default. Cursortype is ctDefaultResultSet.

i use delphi 2006 + SDAC 4.00.00.4 (a trial shipped with delphi)

Many thanks for your help Tino.

That works well.

Steve.

Sitting on the same problem... I can't get the identity value of a freshly inserted record... I've tried all the suggested solutions I found here in the forum, but with no luck.

Here is my code: I can't get the ID value after the insert. The autogenerate value of the ID Field is set to arAutoInc.

The only thing that helps, is to make a MSQuery1.refresh in the AfterPost Event, but this doesn't seem to very performant, especially on larger recordset.

What am I doing wrong here.

Ikar
What about TCustomMSDataSet.Options.QueryIdentity? What are function of this property?

Try to set property TCustomDADataSet.Options.ReturnParams = True.

What about TCustomMSDataSet.Options.QueryIdentity? What are function of this property?

TMSDataSetOptions.QueryIdentity works only if SDAC generates INSERT statement. For more information about QueryIdentity option please see SDAC help.

Both QueryIdentity and ReturnParams are set to true. All that helps, for the moment, is a refresh in the AfterPost event.

Well, I've noticed if I set the cursortype to ctKeyset, a manual Refresh is not needed after the post. This seems to work good for me, because the refresh normally fails if other transactions are currently open.

Could someone give a link or explanation about the different cursortypes in SDAC... In my SQL documentation I couldn't find anything about a DefaulResultset.

For more information about cursor types please see SDAC help. There you will find links to MSDN articles.
But it is desirable to solve the problem without using server cursors.
Please send us a complete small test project to reproduce the problem; it is
desirable to use Northwind or Master schema objects, otherwise include
definition of your own database objects; don't use third party components

Also supply us following information
- Exact version of Delphi
- Exact version of SDAC. You can see it in About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server and OLE DB provider that you use. You can see it in Info sheet of TMSConnection Editor