Next Autoincrement number

Next Autoincrement number

Post by AntiSpamme » Mon, 14 May 2001 03:10:19



When you erase a table that has an autoincrement field, the field continues
on with the next number in the autoincrement sequence when  new record is
added. Is there a way to determine what that next number will be? I don't
think there's a way to reinitialize the sequence with 1.

TIA,

Jerry

 
 
 

Next Autoincrement number

Post by Delvin Insk » Wed, 16 May 2001 00:29:10


There is no way that I know of to get the next increment before it is added.
You can however, set the counter back to one on an empty table by compacting
the database after the table is empty.

HTH
Del

 
 
 

Next Autoincrement number

Post by AntiSpamme » Wed, 16 May 2001 05:06:27


Thanks for the info. I can look at the current value and add 1 to it for the
next increment. That works fine unless the table is empty, of course. I was
hoping for a way to work around this without the overhead of compacting.
Guess not.

Thanks...

Jerry


Quote:> There is no way that I know of to get the next increment before it is
added.
> You can however, set the counter back to one on an empty table by
compacting
> the database after the table is empty.

> HTH
> Del

 
 
 

Next Autoincrement number

Post by Chris Petche » Fri, 18 May 2001 04:16:20


Bear in mind that the next increment +1 is NOT neccesarily the value you
will get when you add the next row.

If you begin a transaction, insert 10 rows and then roll back the
transaction, or delete the last 10 inserts, you loose those values.
That is to say

Id currently shows 1000
do 10 inserts
Id now shows 1010
Roll back the transaction, or delete the last 10 inserts

Maximum value in the ID column now shows at 1000, so you might expect the
next insert to have id value of 1001, but in fact it will be 1011, because
the autoincrement cannot take account of rollbacks and deletes. It cant for
obvious reasons.
Id=1000
User 1 inserts 10 rows
ids=1000-1009
User 2 inserts 10 rows
ids = 1010 - 1019
User 1 rolls back his transaction.
User 2 inserts 1 more row. The database cant reuse 1000-1009 because user2
has already gone beyond that.


> Thanks for the info. I can look at the current value and add 1 to it for
the
> next increment. That works fine unless the table is empty, of course. I
was
> hoping for a way to work around this without the overhead of compacting.
> Guess not.

> Thanks...

> Jerry



> > There is no way that I know of to get the next increment before it is
> added.
> > You can however, set the counter back to one on an empty table by
> compacting
> > the database after the table is empty.

> > HTH
> > Del

 
 
 

1. Help: Getting the next autoincrement number

Is there a way to check the what the next autoincrement number
is going to be?

The of selecting .Last and then checking the number wont do it
if the last record has been deleted.

Apperently, the table keeps the next number in itself, so there
must surely be a way for me to view it?

Help is appreciated.

Martin
---

2. PowerBuilder Job

3. Next Autoincrement number?

4. "Pinned" tables in SQL Server 6.5a

5. Resolving autoincrement fields next value

6. problem with CLongBinary in database

7. Microsoft Access for Chinese Windows?

8. Retrieving AutoIncrement Number in Access 2000

9. Autoincrement a Number

10. Getting next number in series

11. next number

12. Using triggers to fetch next sequential number