[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

anticipating next autoinc number

Posted on 2002-05-09
11
Medium Priority
?
338 Views
Last Modified: 2010-04-04
is it possible to anticipate the autoincrement number that will be assigned to a record before it is posted? (MS Access / SQL server?) I want to use that number to generate another field value.

thanks in advance
0
Comment
Question by:Goodangel Matope
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:SteveWaite
ID: 7000202
How about having another table with perhaps only ever one record in it. One of the fields could be 'LastAutoIncNumber' which is updated with your record number when issued. Obviously your first one could not be anticipated.

Regards,
Steve
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 7001154
No, you cannot really do that. What you can do is to insert an empty record (which will then give you the corrent autoinc number for that record) and update it later-on with contents.
0
 
LVL 4

Expert Comment

by:nestorua
ID: 7001518
HI,
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.

Syntax
IDENT_CURRENT('table_name')

Arguments
table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

Return Types
sql_variant

Remarks
IDENT_CURRENT is similar to the Microsoft® SQL Server? 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Sincerely,
Nestorua.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:AvonWyss
ID: 7002476
Still none of these will 100% reliably tell what the next autoinc number is...
0
 
LVL 2

Expert Comment

by:mikepj
ID: 7002816
Give some serious re-thought to your design.  I'd think it would be better to explore another way to make this need entirely unnecessary.  

I have felt the desire to do some interesting things like this before.  When I rethought my design in light of the full feature set of the language being used, these strange needs have always disappeared with much better results than if I had achieved what I wanted initially.

MP
0
 
LVL 2

Expert Comment

by:mikepj
ID: 7003264
Another solution you can use is to have a table which only contains an auto-inc field.  When you need to know what the next value would be, get an auto-inc value by inserting a "nothing" record into that table.  Some auto-inc values will be "thrown away" so you wouldn't want to do it that often.
0
 
LVL 4

Expert Comment

by:nestorua
ID: 7003311
HI, gOODANGEL,
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.
Syntax
IDENT_CURRENT('table_name')
What do you mean when say that the value
IDENT_CURRENT('table_name')
is not 100% the last autoinc number.
I don't understand you then.
Sincerely,
Nestorua.
0
 
LVL 3

Expert Comment

by:SteveWaite
ID: 7003849
NextAutoIncNumber := Inc(LastAutoIncNumber);
eh!
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 7004153
Well, if any modification to the database occurs while your program is running, you may get another autoinc number. It may be some operation in a procedure you're calling in-between, an external application or even another thread of your proper application, which makes the autoinc counter change. Therefore, the only safe way to deal with this is to allocate an empty record for which you have a autoinc number which is guaranteed to stay correct.
0
 
LVL 14

Accepted Solution

by:
AvonWyss earned 400 total points
ID: 7004154
SteveWaite,
     NextAutoIncNumber := Inc(LastAutoIncNumber);
will not compile. I guess that what you wanted to write is
     NextAutoIncNumber := Succ(LastAutoIncNumber);
0
 
LVL 3

Expert Comment

by:SteveWaite
ID: 7004876
Sorry, kind of thinking out loud. Thanks for pointing that out.

Regards,
Steve
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

830 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