AutoNumber

[Ariez]

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)

I'd much rather have the DB do the checking and return me the error...

 

So how can I refresh the increment?

Edited July 23, 2003 by Ariez

[karimgarza]

Have you tried using transactions to rollback your transaction. You could even use it in a store procedure.

checkout this link:

 

http://doc.ddart.net/mssql/sql70/ra-rz_18.htm

[Ariez]

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 didn't keep the row...

 

So i'm still local here, I havent posted the changes to the DB yet...

(Good link though...i kept it)

[Nerseus]

I don't believe there's 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 I've never wanted to use it the way you mean to AND you'd 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 you'd not want the number of increment, but from a usability standpoint there's 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, you'll want to use a separate table to control the sequence.

 

-ner

[Ariez]

Yes, I need every line numbered carefully, it's just accounting requirements.

But you gave me a direction for searching:"turn off an Identity column temporarily"

Thanx...

[Ariez]

found solution:

http://ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconretrievingidentityorautonumbervalues.htm

[ballisticnylon]

If you're just looking to retrieve the autonumber as you add the row (and I know this looks wierd), you can do it thusly:

 

Dim thisPrimaryKey As Integer = ds_MyDataSet.MyDataTable.AddMyDataTableRow(MyRow).ID

[Ariez]

mana check that right now....

thanx ballisticnylon..

[jweissberg808]

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)

[Ariez]

Yeah, you know i'm glad you said it because this was my conclusion too...