locking SQL Server or UID Gen

<i><b>Originally posted by : eric (e)</b></i><br />Please see sample code below....<br />Is there a way to lock the MSSQL server so that when I execute the first request it will lock and update by one then when I run my SELECT I will get the updated number. I am having the problem that if one user has just executed the UPDATE statement and another user executes an UPDATE when they execute the SELECT they both receive the same number. Im looking for a way to serialize the commands or lock the DB when the first UPDATE is executed then unlock the DB after the SELECT is done. Can this be done with one staement? Or is there a Microsoft way of doing<br />"SELECT GEN_ID( myDB, inc val) FROM RDB$DATABASE"<br />Im just trying to use SQL as a UID generator that will increment by 1 when it is called.<br />Thanks<br />Eric<br />sqlCmd = "UPDATE thetest SET thetest.thecounter = thetest.thecounter + 1 "<br />openLog.Execute sqlCmd <br />sqlCmd = "SELECT thetest.thecounter FROM thetest WHERE thetest.id = 12" <br />Set msg = openLog.Execute( sqlCmd ) <br />number = msg("thecounter")<br /><br /><br />

Not a lock pb. Best is use a stored proc and a table with an AutoNumber (Identity) column. Then just after the insert add

SELECT MyID = @@IDENTITY

MyID will contain the ID of the new record