Refresh after adding new record

[Arg81]

Hi all,

In an attempt to help stop concurrency conflicts, in my dataSet I have set the keyfield (RequestNumber) to have AutoIncrement Seed and Step to both be -1.

As my backend (SQL 2000) has this field set to AutoIncrement as well, then the true RequestNumber is set when the data is send to the database.

However, within my program, when I call UpdateDataSet, the data is sent back, the true RequestNumber appears in the database, but the label displaying the value on the form still keeps -1.

As this is my key, I can't call LoadDataSet on @RequestNumber, so I'm asking if there is any other way I can refresh that record so that the correct RequestNumber appears in the label?

cheers,
Luke

 

[jmcilhinney]

Set an output parameter to return the new RequestNumber.

 

[Arg81]

Cool, I assume you are talking about @@Identity?

But I've read somewhere that if another user adds a record before say this user presses the Submit button, then this user will retrieve the other user's inserted record ID.

I don't think that my app will be affected like that as records are added one at a time over a period of days. However I would like to know the best way forward for future reference.

You're talking about creating a stored procedure? I haven't got a great deal of experience with them, my search forms are using dataAdapters with parameter queries instead. However on the second version of the DB I will probably move these across to SP's.

Cheers,
Luke

EDIT - I've just looked at the CommandText of the InsertCommand and it's set to return @@IDENTITY. Even though the label is bound to the ID field of the database, it still has the minus number displayed....

 

[Arg81]

Well nothing I've read has helped me understand @@IDENTITY or SCOPE_IDENTITY() that much, in the sense of how to refresh a label with that value.

I've worked around it, instead once the dataSet has been updated, I reload it, and use BindingContext to go to the last record of the table (last record = recently added). This then reloads the page with the correct value in the ID field for the user to take a record of.