Solved

anticipating next autoinc number

Posted on 2002-05-09
11
326 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now