Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
DATABASES, TABLES AND QUERIES >>  BUT I DON'T HAVE AN AUTOINC FIELD - I DON'T HAVE VFP 8 OR BETTER

  Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy



VFP 8 introduced the autoinc field, and life became far simpler for those of us who were able to upgrade. For those who are still working in earlier versions (VFP 7 or older) you can achieve the same sort of thing without the autoinc field.

The first thing you need is a table that will hold the last primary key value used for each table. Create this table using the following code:
CREATE TABLE [Path2\KeyValues] (;
   cTableName C(20),;
   nLastKey N(10,0))


The cTableName field will hold the name of the table (as the name suggests) and the nLastKey field will hold the last record key issued.

Now index that table on the cTableName field:
INDEX ON KeyValues.cTableName TAG TableName


Now that you have your table ready, you can use it with the following function:

lnNewKey = GetNewKey([MyTable])
IF lnNewKey > 0
   INSERT INTO MyTable ;
      (MyTable.RecordID)
      VALUES (lnNewKey)
ENDIF 


FUNCTION GetNewKey
LPARAMETERS lcTableName
IF VARTYPE(lcTableName) == [C]
   IF NOT USED([KeyValues])
      USE KeyValues IN 0 SHARED
   ENDIF
   lcTableName = PADR(lcTableName,20,[ ])
   IF NOT SEEK(lcTableName,[KeyValues],[TableName])
      APPEND BLANK IN SELECT([KeyValues])
      REPLACE KeyValues.cTableName WITH lcTableName IN SELECT([KeyValues])
   ENDIF
   IF RLOCK([KeyValues])
      lnNewKey = KeyValues.nLastKey+1
      REPLACE KeyValues.nLastKey WITH lnNewKey IN SELECT([KeyValues])
      UNLOCK
      RETURN lnNewKey
   ELSE
      MESSAGEBOX([Could not lock KeyValues])
      RETURN -2
   ENDIF
ELSE
   MESSAGEBOX([Invalid Parameter])
   RETURN -1
ENDIF
ENDFUNC


You begin by calling the function. If the function returns a positive value, you can then add your new record and assign the primary key value.

The function begins by checking the parameter. If it is a character value, then it proceeds to the next step. It looks to see if a KeyValues record exists for that parameter value. If it does not, the function assumes that this is a new table that you have added and sets up a new KeyValues record for that table.

(You may not wish to do this because if you type in the table name incorrectly, it will assume this is a new table. If this is the case instead of adding a new KeyValues record, just pop a messagebox and return some negative value.)

Once the function finds (or creates) a record for that table, it then locks that record, calculates the value of the next key, saves it to the KeyValues table and returns that new key as a positive integer.

If you are feeling extra brave, you can make this a stored procedure and call it from an insert trigger - but that would be the topic of another FAQ.

FEEDBACK


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: