Best way to get a unique key field value using PowerBuilder and SQL Server?
Posted on 2015-02-12
The program that I have created using PowerBuilder has a database table with a unique key field. It is not an identity field, but just an integer field. I have always used a system to get a new unique value of having a separate table that holds the next number to be used, and then updates that table when the next value is needed from it. This has worked well up until now. There are several users of the program and they are entering data quickly. More than one user is trying to grab a unique value at the same time and in some cases they get the same value, causing a database error when trying to save the data (problem with the primary key).
At a job a long time ago I seem to recall using a SQL Server stored procedure maybe to grab that next value, because maybe it locked the table, or somehow provided a more reliable way of insuring the value is unique. Anyhow, I can't find that code and am not sure how to approach it. Does that process sound familiar to anyone?
Or, what method do you recommend to solve this issue?