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

Posted on 2005-03-08
Medium Priority
Last Modified: 2010-04-05

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

K.-P. Becker

Question by:KPBecker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 17

Assisted Solution

geobul earned 375 total points
ID: 13485198

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.SQL.Text := 'select @@IDENTITY as Ident';
lastID := ADOQuery2.FieldByName('Ident').AsInteger;

Regards, Geo
LVL 15

Accepted Solution

mikelittlewood earned 375 total points
ID: 13485205
Next auto inc field
Select Max(TheIncField) + 1 FROM YourTable

Current inc value
Select Max(TheIncField) FROM YourTable
LVL 17

Expert Comment

ID: 13485439
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

Author Comment

ID: 13487375
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.

LVL 15

Expert Comment

ID: 13487554
I agree with you geobul for a multi user environment.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question