auto-increment field 
Author Message
 auto-increment field

I am needing a auto-increment field in a database table.  I don't care what
value gets assigned to it, as long as it is always unique.  Is there any way
to define such a field in the database so that any time a record is added it
gets a unique value?  I'm thinking you might be able to put a stored
procedure on the insert trigger, but need some help on this.  Any ideas?

Thanks,
Bob Powell



Sat, 21 Jul 2001 03:00:00 GMT  
 auto-increment field
Yes, you can use a stored proc. for that. In table Designer you will set a
default value for a field with unique value. That default value is a name of
SP: getprimkey("'addresses'") where getprimkey is a name of SP. You supply
SP with table's name. Different for each table.
PROC getprimkey
LPARAMETERS tcAlias
SELECT MAX(EVAL(FIELD(1))) nNextId FROM (tcAlais) INTO CURSOR curMax
IF _TALLY = 0 && first record is inserted
    RETURN 1
ELSE
    RETURN nNextId
ENDIF
ENDPROC


Sat, 21 Jul 2001 03:00:00 GMT  
 auto-increment field
I've got some typos in there. Sorry.
PROC getprimkey
LPARAMETERS tcAlias
SELECT MAX(EVAL(FIELD(1))) AS nNextId FROM (tcAlais) INTO CURSOR curMax
IF _TALLY = 0 && first record is inserted
    RETURN 1
ELSE
    RETURN nNextId + 1
ENDIF
ENDPROC


Sat, 21 Jul 2001 03:00:00 GMT  
 auto-increment field
You can also add this stored Procedure to you DBC.
You need to also include a table called "SETUP"
with the following fields:

value  (Character 20)
keyname (Character 20)

and change the DBC name.  Then set the default
value on your primary index field to

newid()

FUNCTION NewID(tcAlias)
  LOCAL lcAlias,lcID,lcOldReprocess,lnOldArea

  lnOldArea = SELECT()

  IF PARAMETERS() < 1
    lcAlias = UPPER(ALIAS())
  ELSE
    lcAlias = UPPER(tcAlias)
  ENDIF

  lcID = ""
  lcOldReprocess = SET('REPROCESS')

  SET REPROCESS TO AUTOMATIC

  IF !USED("SETUP")
    USE dbcname!setup IN 0       && change dbcname to name of dbc
  ENDIF
  SELECT setup

  IF !SEEK(lcAlias, "setup", "keyname")
    APPEND BLANK
    REPLACE setup.keyname WITH lcAlias
    REPLACE setup.value WITH ALLT(STR(0))
  ENDIF

  IF SEEK(lcAlias, "setup", "keyname")
    IF RLOCK()
      lcID = setup.value
      REPLACE setup.value WITH ALLT(STR(VAL(ALLT(lcID))+1,
LEN(setup.value)))
      UNLOCK
    ENDIF
  ENDIF

  SELECT (lnOldArea)
  SET REPROCESS TO lcOldReprocess

  RETURN lcID
ENDFUNC



Sun, 22 Jul 2001 03:00:00 GMT  
 auto-increment field
That's not a good method. In a table that has heavy adds, you will get
duplicates.

--

Craig Berntson
Microsoft Visual FoxPro MVP
Salt Lake City Fox User Group
http://www.xmission.com/~craigb


Quote:
>Yes, you can use a stored proc. for that. In table Designer you will set a
>default value for a field with unique value. That default value is a name
of
>SP: getprimkey("'addresses'") where getprimkey is a name of SP. You supply
>SP with table's name. Different for each table.
>PROC getprimkey
>LPARAMETERS tcAlias
>SELECT MAX(EVAL(FIELD(1))) nNextId FROM (tcAlais) INTO CURSOR curMax
>IF _TALLY = 0 && first record is inserted
>    RETURN 1
>ELSE
>    RETURN nNextId
>ENDIF
>ENDPROC



Tue, 24 Jul 2001 03:00:00 GMT  
 auto-increment field
Look at the article on my web site that discusses primary keys.

--

Craig Berntson
Microsoft Visual FoxPro MVP
Salt Lake City Fox User Group
http://www.xmission.com/~craigb


Quote:
>I am needing a auto-increment field in a database table.  I don't care what
>value gets assigned to it, as long as it is always unique.  Is there any
way
>to define such a field in the database so that any time a record is added
it
>gets a unique value?  I'm thinking you might be able to put a stored
>procedure on the insert trigger, but need some help on this.  Any ideas?

>Thanks,
>Bob Powell



Tue, 24 Jul 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Auto Increment Field

2. Auto Increment Field

3. Auto Increment Field

4. Auto-Incremented field

5. Auto-increment Field in Foxpro

6. How to retrieving the autoincrement fields value from SQL server

7. VFP Autoincrement Field

8. autoincrement field through remote views doesn't seem to work

9. Need help with autoincrement fields

10. autoincrement field

11. autoincrement field - Visual Foxpro database and ADO connection

12. Auto-incrementing a primary key field

 

 
Powered by phpBB® Forum Software