Auto Number Issue (MS Access 2003)

[alander]

I add a new record, and then reject changes as validation has failed, and so the dataset isnt updated, autonumber value is increased by 1

next time when I add new record autonumber value is increased normally but it has skipped one number

autonumber = 1
addnew (autonumber = 2)
reject change

addnew(autonumber = 3)
update

When I update the database the value in database ishould be 2 and not 3 as it have been rejected.

How can I stop this autonumber value from increasing when changes are rejected?

 

[Arg81]

You need to make a change to the properties to the "autonumber" column of your datatable within your dataset.

Change AutoIncrement and AutoIncrementSeed BOTH to -1

This means your app will get it's correct value from the DB

 

[alander]

err,this is not working.. after setting them i get 0 as my auto number when i click new

 

[Arg81]

have you set your database column to be autonumber?

I assumed you had...

 

[alander]

yes i did..

 

[alander]

i did a work around to this problem...

i removed the autonumber as it give too much trouble..

i added in an integer field instead, set a query to find out the maximum value of the ID..

Dim maxIdValue As Int32 = Me.EmployeesInfoTableAdapter.findMaxId()

in my new button i did this

Me.EmployeesInfoBindingSource.AddNew()
Me.txtEmpId.Text = maxIdValue + 1
Me.EmployeesInfoBindingSource.EndEdit()

when i click on cancel, i juz
cancelEdit()

when i click the save button if it pass validations, i just do
maxIdValue +=1

if validation fails, i do nth..

thanks Arg81 for trying, even tho it didnt work out

 

[Arg81]

if your database column is set to AutoNumber in Access, and your DataTable pulls that info across, once a new row is submitted, it returns the new value from the database.

I've just tested it and it does as it should. I think maybe you have a problem somewhere with your dataTable settings.

The workaround is OK, but I was taught NEVER to let your application define the ID for the row, always let your backend database do it....

Each to his/her own though

 

[alander]

yea i supposed there was a problem with my table settings, ill do some more testing with a new project later to check out ur solution.. however, my database column was autonumber

Change AutoIncrement and AutoIncrementSeed BOTH to -1

I supposed u made a typo here.. because my AutoIncrement is either a true or false in my dataset designer unless i set something wrong due to that..

I set

AutoIncrementSeed to -1
AutoIncrementStep to -1

anyway will try later
thanks

 

[Arg81]

Yep I did make a typo ... I meant AutoIncrement to True, and then Seed and Step to -1


Access Database

Set Column to AutoNumber

DataSet Designer

 

[cjard]

I set

AutoIncrementSeed to -1
AutoIncrementStep to -1

Heh.. and you can just bet that the crucial missing difference will be that your AutoIncrement was set to false...