How to get IDENTITY value after an INSERT

Sawlmgsj · Post by **Sawlmgsj** » Wed 28 Dec 2005 20:32

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.

tinof · Post by **tinof** » Thu 29 Dec 2005 05:38

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

Sawlmgsj · Post by **Sawlmgsj** » Thu 29 Dec 2005 09:36

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.

tinof · Post by **tinof** » Thu 29 Dec 2005 10:33

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

Sawlmgsj · Post by **Sawlmgsj** » Thu 29 Dec 2005 11:26

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.

Ikar · Post by **Ikar** » Thu 29 Dec 2005 12:26

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

tinof · Post by **tinof** » Thu 29 Dec 2005 12:31

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)

Sawlmgsj · Post by **Sawlmgsj** » Thu 29 Dec 2005 13:43

Many thanks for your help Tino.

That works well.

Steve.

Jelly · Post by **Jelly** » Tue 30 May 2006 21:57

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:

Code: Select all

procedure TForm10.MSQuery1AfterPost(DataSet: TDataSet);
begin
     caption := MSQuery1ID.AsString ;
end;

procedure TForm10.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
     if stInsert in StatementTypes then 
       Params.ParamByName('ID').ParamType := ptInputOutput; 

end;

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.

alec · Post by **alec** » Fri 02 Jun 2006 08:56

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

Jackson · Post by **Jackson** » Fri 02 Jun 2006 09:58

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

Jackson · Post by **Jackson** » Fri 02 Jun 2006 11:44

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.

Jelly · Post by **Jelly** » Fri 02 Jun 2006 21:01

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

Jelly · Post by **Jelly** » Mon 05 Jun 2006 17:05

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.

Jackson · Post by **Jackson** » Tue 06 Jun 2006 07:41

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