>Just beginning to delve into SQL syntax in VFP and would like to create a primary (numeric) key in a table. I would also like this field to simply be an integer value which increments by 1 each time a new record is added.
>
>Some SQL languages have a definition which causes this to happen. I haven't seen any reference to something like this so far. Is there anything like this in VFP ?
>
>If not, is there a recommended method for handling this ? Separate FREE table to store next value in ? Table within the database ?
Let's say your table is called "client". In the default value of the field, type SerialNumber("client"). Place function SerialNumber in stored procedures. Create the appropriate table (sequence C(20), NextNum I).
HTH, Hilmar.
FUNCTION SERIALNUMBER(tnSequence)
tnSequence = lower(tnSequence)
local lnSelect
lnSelect = select()
if used("serialnumber")
select serialnumber
else
select 0
use serialnumber
endif
set order to "sequence"
seek padr(tnSequence, len(sequence))
if not found()
append blank
replace sequence with tnSequence, nextnum with 1
endif
local lnReturnValue
if lock()
lnReturnValue = nextnum
replace nextnum with nextnum + 1
else
lnReturnValue = -1
endif
unlock
select (lnSelect)
return lnReturnValue
ENDFUNC
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)