• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Getting the value of an auto-inc-field (ACCESS, MS-SQL)

Hi,

by using sql 'insert' I add records to a table (access 2002, ms-sql and mysql). One of the fields is an auto-inc.

My question:
How can I get to know the next auto-inc which will be used ?
Alternatively:
How can I get to know the auto-inc-value of the last insert ?

For MySQL a function last_insert_id seems to exist but what to do with access and ms-sql ?

Thanks
K.-P. Becker

0
KPBecker
Asked:
KPBecker
  • 2
  • 2
2 Solutions
 
geobulCommented:
Hi,

In MSSQL get @@IDENTITY variable. Use TADOQuery, for instance, with the following SQL:

select @@IDENTITY as Ident

then in Dlephi after the insert do:

ADOQuery2.Close;
ADOQuery2.SQL.Text := 'select @@IDENTITY as Ident';
ADOQuery2.Open;
lastID := ADOQuery2.FieldByName('Ident').AsInteger;
ADOQuery2.Close;

Regards, Geo
0
 
mikelittlewoodCommented:
Next auto inc field
Select Max(TheIncField) + 1 FROM YourTable

Current inc value
Select Max(TheIncField) FROM YourTable
0
 
geobulCommented:
IMHO 'select max' is not a good solution for getting the last inserted identity value in multi-user environment.

>How can I get to know the next auto-inc which will be used ?
Using 'select max + 1' will return the next "possible" database-wide value not session-wide (speaking about multi-user environment again). I don't think that it's always guaranteed. The actual value might be greater in some circumstances.

Regards, Geo
0
 
KPBeckerAuthor Commented:
Thanks to the experts !

The answer of Geo is what I hoped would exist for all three DBs. It would be the cleanest way.

I think I have to use mikelittlewood's workaround in spite of the difficulty Geo pointed to.

I will split the points to both of you.

KPBecker
0
 
mikelittlewoodCommented:
I agree with you geobul for a multi user environment.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now