>>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