VB2010 with SQL Server 2008: SELECT w/UPDLOCK + UPDATE to prevent duplicate key error

[BruceG]

Hello,
We have a mulit-user application using SQL Server 2008 and VB 2010. There is code in place to get the next record ID of our Order table, but it needs to be modified to ensure that two users are not getting the same value at the same time, which could obviously cause a primary key violation if the users are using that ID when they create new orders. I believe I know what to do to fix this, but I would appreciate confirmation from others who have had experience with this.

We keep the "LastOrderID" as a field in a one-record table, let's call it AppParms, and we essentially read that record to get the ID, add 1 to it, and update the table with that new ID. Here is pseudocode for what is currently in place:

----------------------------------------------------------------------
Sql = "SELECT LastOrderID FROM AppParms"
' use reader object to execute the Sql and get the value of LastOrderID
TheNewID = LastOrderID + 1
' "TheNewID" is then used in the app to later ultimately insert the new order into the Order table
Sql = "UPDATE AppParms SET LastOrderID = " & TheNewID
' execute the SQL
----------------------------------------------------------------------

Here is what I believe I need to do to modify the above:
----------------------------------------------------------------------
' execute a "BeginTransaction" on the DB connection
Sql = "SELECT LastOrderID FROM AppParms WITH (UPDLOCK, HOLDLOCK)"
' use reader object to execute the Sql and get the value of LastOrderID
TheNewID = LastOrderID + 1
' "TheNewID" is then used in the app to later ultimately insert the new order into the Order table
Sql = "UPDATE AppParms SET LastOrderID = " & TheNewID
' execute the SQL
' execute a "Commit" on the transaction object
----------------------------------------------------------------------

Do you concur? If not, what would I need to do instead?

NOTE: One may reasonably suggest that an IDENTITY column be used for the OrderID, however, that is not an option in this case and the design is beyond my control. The example I posted is a simplified version, but there is more custom stuff going on in building the key. However, we still need the "Last ID + 1" piece; hence my post.)

[jmcilhinney]

Or you could just do what millions of other applications do world-wide and let the database generate the ID when you save the record.

[BruceG]

Please see the last part of the post:

NOTE: One may reasonably suggest that an IDENTITY column be used for the OrderID, however, that is not an option in this case and the design is beyond my control. The example I posted is a simplified version, but there is more custom stuff going on in building the key. However, we still need the "Last ID + 1" piece; hence my post.)

[jmcilhinney]

My apologies. My concentration waned before i got to that bit.

[BruceG]

No problem. So would you concur that a proper solution in this case would be to use the UPDLOCK and HOLDLOCK hints on the SELECT statement, and place the SELECT and UPDATE statements withina transaction?