We are currently using VB to create order numbers. Each user requests an
order number as and when required. The problem is that the next in the
sequence seems to be allocated on the odd occasion to more than one user.
This being due to the requests coming at almost the same time but not as yet
assigned to the user. Is there a way to lock a request to a table until a
previous process is completed before continuing to the next request. This
will help eliminate our issue. If someone could point me in the right
direction then that would be great.

Thank you ever so much

If you are updating sql will automatically lock any other transaction until
the update is finished.
If you have concurrency problems you may want to read the BoL on this to
determine what exact issue you have, for example you may want to execute an
exclusive lock while reading etc etc.

For more see
http://www.quest-pipelines.com/newsletter-v3/0202_F.htm

Wrap processes in a transaction (SQL or ADO) and you will gain the ACID
requirements of your app. Beyond this, you can change SQL isolation levels
for your transaction to further lock down the bits in question.

Show quoteHide quote

You can do it, but what you have to do is something like:

set transaction isolation serializable
begin transaction
    select @value = value from table (xlock) where <whatever> --get an
exclusive lock and hold it

    --Now you are holding locks, and you have an exclusive lock on the
table, so all other users will wait until you are finished and execute

commit transaction

before moving along.  This single-threads activity to this table, so
consider that when you use this technique.  I would consider having a table
that you use to generate keys, rather than using the same table.

Alternatively, you can do something using identity columns and inserts to
simultate this process, have a table where you insert a row to build a new
key, then use the identity value as the key or part of the key to insert.
This reduces locking, but gives you a table you have to frequently clean out
(like with scheduled job.)

Show quoteHide quote

simple insert of strings
Complex Query
adjust my code
avoid using cursors....
really large stored procedures