Hi Alan,
The problem is you are getting the the PK value and then updating the counter. There is a chance that another user will get the same PK value before you update the counter. You need to wrap the process into a transaction and then update the counter first and get the PK value and then commit the update. I prefer to do this with a stored procedure that way you only have one call to the database to get the new value. I don't use MSSql so I can't help you with the sp.
>I have a VFP8/SQL-server application, with new PK values allocated via. a 'control table' on the server which stores the last-allocated PK values for all tables.
>
>Occassionally I am getting duplicate values for a PK, and I cannot work-out why. To exaggerate the problem, I wrote a test program to create 1000 new pk values for one table, and ran two tests simultaneously. To my surprise around 7-10% of the new values were duplicated! Maybe I've been lucky so far because duplications have been rare (and most of the input is manual, not in a tight loop), but I need to get to the bottom of the problem.
>
>From the code below, if the same id is allocated twice, both processes incremented the same value, but during the update the 2nd process should not have found the record, because nLastNumber would have been since updated by process 1. Ideally I would like to use an SQL equivalent of RLOCK(), but as there isn't one, and record locking in SQL looks (IMO) unnecessarily complicated, I went for the idea of using the "key and modified fields" in the update WHERE clause. As far as I know, SQL-server always locks the record during an update, and if no record is found with the original value(s), no record will be updated. Is my thinking correct? If so, where is the flaw in my thinking and/or code?
>
>Any help would be appreciated. TIA.
>
>Alan
>
>---------------------------------------------------------------------
>
>The next-id allocation function is as follows...
>
>
>FUNCTION NewId
>
>LPARAMETER tcIdField
>
>LOCAL lcFieldName, lcFilter, lcResult, lnConnHandle, lnNewId, lnOrigArea, lnRetVal
>lcFieldName = UPPER(tcIdField)
>lcFilter = [UPPER(cfieldname)=?lcFieldName]
>lnOrigArea = SELECT(0)
>lnConnHandle = DefaultConnection()
>
>IF lnConnHandle<0
> RETURN -1
>ENDIF
>
>DO WHILE .T.
> =SQLEXEC(lnConnHandle, ;
> [SELECT * FROM UniqueId WHERE ]+lcFilter, ;
> 'UniqueIdV')
>
> SELECT UniqueIdV
> lnOrigId = nLastNumber
> lnNewId = nLastNumber+1
>
> * Use "key and modified fields" comparison for update.
> * If record not found, record has changed, program loops to try again.
> lnResult = ;
> SQLEXEC(lnConnHandle, ;
> [UPDATE UniqueId SET nLastNumber=?lnNewId ;
> WHERE ]+lcFilter+[ AND nLastNumber=?lnOrigId])
>
> IF lnResult>0
> EXIT
> ENDIF
>
> * If program got this far, try again
> WAIT WINDOW NOWAIT "Waiting for "+lcFieldName+" record lock"
>ENDDO
>
>WAIT CLEAR
>=SQLDISCONNECT(lnConnHandle)
>SELECT (lnOrigArea)
>
>RETURN lnNewId
>
>
>The test program (simultaneously running in 2 separate VFP sessions) is as follows...
>
>LPARAMETER tcTable
>
>SET SAFETY OFF
>CLOSE DATA ALL
>CREATE TABLE (tcTable) (nId I)
>
>LOCAL X, lnNewId
>
>FOR X=1 TO 1000
> lnNewId = NewId('nTestId')
> INSERT INTO (tcTable) (nId) VALUE (lnNewId)
>ENDFOR
>
>WAIT WINDOW NOWAIT tcTable+" loaded"
>