>>>Bob's solution does not address multiuser issue. You need to lock NumberTable or at least one row from this table (if it is possible).
>>>I'd create stored procedure for this job.
>>>Something along these lines:
>>>CREATE PROCEDURE GETID @tbl char(10) AS
>>>set nocount on
>>>declare @TempId int
>>>begin tran
>>>select @TempId = (select ID_Value from NumberTable (Tablockx HOLDLOCK) where TABLENAME=@tbl
>>>select @TempId = @TempId + 1
>>>update NumberTable SET ID_Value =@TempId where TABLENAME= @tbl
>>>select ID_Value from NumberTable where TABLENAME= @tbl
>>>commit tran
>>>
>>>In Fox code you just ask for next ID:
>>>=SQLExec(nConn, "GETID","curID")
>>>yourId=curID.ID_Value
>>>
>>>I locked the table, b/c I am working with 6.5, but in SQL Server 7 should be the way to lock just one row.
>>>
>>
>>
>>Oleg,
>>I think I get the point, Thanks !
>>- WHat's wrong with Bob's solution ? It seems to work allright for me, even in multiuser ...
>Pascal,
>in multiuser env without locking you can have the same ID for 2 users.
>Between SELECT and UPDATE someone else could issue SELECT an get the same ID.
>Thanks,
>Oleg

Oleg,

Look at it again... the UPDATE will NOT work unless the LAstNumber is still what it was when the selet was done. Look at the WHERE of the update.

BOb