DataRow AutoNumber

[q1w2e3r4t7]

I'm adding rows to a datatable, however the automatically generated autonumber differs from the from the automatic number when updated to the database.

 

How can i add rows to a datatable initial retrieved from a database and have the autonumber the same as when it's finally updated to the database ?

 

Thanks for any assistance

[Nerseus]

Most of the time, you want code and the user to not know about the AutoGenerated ID until after a record has been saved. A typical solution is to display "New" wherever the ID would normally be shown (such as a textbox with an "ID" label). After save, the "ID" field would update to show the real ID generated by the database. In our company, we set the seed in our DataTable to -1 with an increment of -1. That helps to differentiate a real ID from a "new ID."

 

If you really want them in sync, there's the "guess" solution and the accurate method(s). I've listed these as I've seen them used before - there may well be other methods.

1. The "guess" method: When you enter "add" mode, run a query on the DB to get the "MAX(ID)" of the table/column you want and add 1. It's a guess because another user could insert a record after you've retrieved the MAX(ID) and then their record would be that ID and you'd get the next highest number. If you have a single user app, then this would probably be just fine.

 

2. The accurate method 1: Don't use autonumber columns - use a separate table that holds the "next ID" value. This changes your process to run some query that updates the table and returns the next ID for your app to use. The one drawback is that a user that cancels your transaction will generally leave that ID as "used" even though it wouldn't really be used. For example, you have a "NextCustomerNumber" table with the current value 124. The user chooses to add a customer so you query the table to get 125 and update the table to 125 - this increments the ID so the next person gets 126. Now the user cancels - maybe they didn't want to add the customer after all. Now the table will still have 125 and the next "Add Customer" will use 126, but there will be no Customer 125.

 

3. Allow saving an "empty" record. Here you'd allow saving a Customer with all NULL data when they click "Add Customer." The idea is that the save of a customer is always an update. If they choose to cancel the "Add Customer," you can still delete the dummy row you created. This would have lots of drawbacks.

 

-ner

[q1w2e3r4t7]

Thanks Nerseus, these are good and logical ideas. They arent what i'm really looking for, however most likely i will have to revert to these.

 

P.s. a hole in your max id theory, if a user has deleted a number of records at the end of the table, max + 1 would not work.

 

Anyways, as said, thanks, i'll prob have to use these ideas.

 

 

Ta.