Question Auto Increment as ID Column

[PhillD]

I am developing a form that is linked to a single table. I am using an auto increment field as the record ID the users are given to identify their record. If 2 users open the form at the same time, they are given the same ID number. When the first user saves, they will keep the number, however, when the second user saves, their number will change.

Record ID’s should remain consistent after being presented to the user, seems like one of the downsides to using unlinked datasets. There has to be a sensible way around this. Does anyone have any suggestions?

Thanks

 

[PhillD]

Thanks for the advice.

I can't believe how difficult this is!

Here's what I really need. As the ID will be saved in the DataSet when the record is saved, I just need to be able to get the value from the DataSet. Again, nothing works! I have reseached this on other sites and the code that keeps coming up is DataSetName.Tables(0).Rows(0)(0).ToString but when i call this code, it errors with no record at row 0. That is impossible! I just saved the record, there has to be a row 0.

 

[jmcilhinney]

If the error message says that there's no row at index 0 then there's no row at index 0. You must be either clearing the DataSet, clearing the DataTable or else creating a new one of either or both. What you're trying to do is very simple but you are complicating it somehow. Exactly how I don't know because we've never seen any of your code, VB or SQL.

 

[cjard]

Thanks for the advice.

I can't believe how difficult this is!

Here's what I really need. As the ID will be saved in the DataSet when the record is saved, I just need to be able to get the value from the DataSet. Again, nothing works! I have reseached this on other sites and the code that keeps coming up is DataSetName.Tables(0).Rows(0)(0).ToString but when i call this code, it errors with no record at row 0. That is impossible! I just saved the record, there has to be a row 0.

If the following words mean nothing to you, read the DW2 link in my signature, section "Creating a Simple Data App"
DataTable, TableAdapter, DataSet

Make a new project

Make a new DataSet file in that project

So, make a new tableadapter in your dataset, query:
SELECT * FROM erpdb.someTable
Ensure as you go through the wizard that you click on the Advanced button and ensure that the option to "refresh the dataset (populate changes by the db back into the dataset)" is ticked

Finish the wizard

Click on the autonumber column, and set
AutoIncrement yes
AutoIncrementSeed -1
AutoIncrementStep -1

Click on the tableadapter itself

Amend the insert query to have ;SELECT name_of_the_autonumber_column = SCOPE_IDENTITY() on the end
(This might have to be ;SELECT @name_of_the_autonumber_parameter = SCOPE_IDENTITY() - i'm not sure; i dont work with sql server - in oracle you populate the parameter you passed in if you want the db generated value back out)

Go to the form designer

Open the datasources window, click the dropdown arrow next to the table name you just added, to DETAILS mode (not grid)

Drag the table to the form

Start the app

Make a new record, using the + button on the provided-for-you bar at the top

See the ID column has -1 in the box

Write details

Save the record

See the ID box now has the ID value generated by the DB

-

To work with these things programmatically you have to appreciate that a dataset is like a db - its a collection of datatables, datatables are collections of datarows. If your local datatable has 10 datarows, and the new one you just added is at position 11 then you have to refer to it as such

Either, because it is the current row:

DirectCast(DirectCast(myBindingSource.Current, DataRowView).Row, MyXYZTableRow).WHATEVER_COLUMN

Or because you know it is at position 11:
myDataSetInstance.MyXYZTable(11).WHATEVER_COLUMN

-

Rarely do we need to do this; the ID is returned to our dataset, but often we don't care, unless we are giing it out as a reference to the record to e.g. a caller on the phone..

 

[PhillD]

Thanks for posting this. I didn’t receive my usual email update so I only noticed it today.

I would like to ask you 1 question though. In your opinion, is it better to use identity columns as record numbers, or assign them yourself at the point of insert? The reason I ask is because when I use an identity field, the identity box on the form displays “-1”. I understand I could change the way it displays, BUT I don’t want to issue an ID to the user if it could change after they save. If I create the ID myself during insert, the ID box will stay blank until the user saves the record.

 

[PhillD]

I am now reading that it is a bad idea to manually assign incremental ID values due to the MAX() statement that must be executed during the insert.

So now I am back to the same problem. I am going to start a new thread because this one has changed too much and become clouded.