AutoNumber

A

Ariez

Well-known member
Joined
Feb 28, 2003
Messages
164
I have a field that is autoNumber, automatically incremented by the database.

Each time you add a row and cancel the edit or the addition is rejected by the database, the number gets incremented again even if no row has been added.

I used to have a different set of control for input and did all the checking before i added the row to the database.(double work since i already defined the constraints in the DB)
Id much rather have the DB do the checking and return me the error...

So how can I refresh the increment?
 
Last edited by a moderator:
Its just a dataset filled with this Access table where the primary key is AutoNum.
I linked the dataset to a datagrid.
each time i go to a new row (where the * is) I get the autonum +1 even if i didnt keep the row...

So im still local here, I havent posted the changes to the DB yet...
(Good link though...i kept it)
 
I dont believe theres any easy way to keep the number that was temporarily used if you rollback. I know in SQL Server you have the ability to turn off an Identity column temporarily, but Ive never wanted to use it the way you mean to AND youd have to keep track of what the number *should* be so that you could reset the seed.

I can understand from a "clean" perspective why youd not want the number of increment, but from a usability standpoint theres no reason why having the numbers out of sequence should cause any problems. Are you relying on the number to always be sequential? If so, and you have the ability to rollback trans and get the number out of sync, youll want to use a separate table to control the sequence.

-ner
 
Yes, I need every line numbered carefully, its just accounting requirements.
But you gave me a direction for searching:"turn off an Identity column temporarily"
Thanx...
 
If youre just looking to retrieve the autonumber as you add the row (and I know this looks wierd), you can do it thusly:

[VB]
Dim thisPrimaryKey As Integer = ds_MyDataSet.MyDataTable.AddMyDataTableRow(MyRow).ID
[/VB]
 
if i need to store a sequential number - that needs to be sequential, i keep that code independent of the auto-generated key - and handle the numbering myself. (i.e. in a database table)
 
Back
Top