Locking SQL row query
Posted on 2013-01-28
I know there is a great detail of information on locking but I'm struggling to do something which, to me, should be quite straightforward.
My application assigns an order id from a sequence held in a parameter table. I need to be able to select the current 'nextorderid' from the table 'parameters' and then increment the value by 1 ready for the next order.
I can select and do the update with no problem but I need to understand how to lock the row for the split second between the select and the subsequent update so that another user cannot be assigned the same id. I've tried the following:-
Dim daParameters = New SqlDataAdapter("SELECT * from Parameters WITH (ROWLOCK)", oSQLConnection)
Dim tblParameters As New DataTable
but the management studio still allows me to edit the row. I'm obviously missing something.
Any help gratefully received.