We have an ASP.NET application that writes records into Visual Foxpro tables. We use an "ID" table that has a record for every table for the purposes of assigning the next primary key for a given table. Previous we used code much like:

lnNextid = 0 
LOCATE FOR id.tablename = lcTable
IF !EOF()
     if RLOCK()
        lnNextid = id.keyvalue
        replace id.keyvalue with lnnextid + 1
    endif
endif
return lnNextid

The problem is in ASP.NET with ODBC connection we have no way of locking the record (that i know of) to make sure two users do not happen to similtaneously call our newid function and receive a duplicate key.

What would be the best way of grabbing and updating these primary keys from an ASP.NET application?

Thanks in advance!!!