How to Create Unique Values on SQL Server 6.0

• Microsoft Visual FoxPro for Windows, versions 3.0 and 3.0b

SUMMARY

SQL Server 6.0 has a new Identity property that can be used to create a unique value for each row in a table. The identity property allows the user to specify a seed value and an increment value. This article gives example code that shows how to use the new Identity property.

MORE INFORMATION

The Identity property must be used with an integer datatype. (int, smallint, or tinyint). Only one identity column is allowed per table. Identity columns cannot be updated and won't accept NULL values.

Sample Code

This example creates an Invoice table that has a starting invoice number of 1000 and increments by 1 for each new invoice record inserted.

* Establish connection to an existing datasource define to a SQL Server 6.0 * database.

  xhandle = sqlconnect()
  IF xhandle > 0
    WAIT WINDOW "Successfull Connection. Handle = "+str(xhandle)
  ELSE
    WAIT WINDOW "Bad connection."
    =errhand()
    RETURN
  ENDIF

  x = SQLExec(xhandle,"Create table invoice (invoice_no INT" + ;
     "identity(1000,1),customer varchar(25),;
     amount money, inv_date datetime)")

  IF x < 0
    =errhand()
    RETURN
  ELSE
    WAIT WINDOW "Table created successfully"
  ENDIF

  x = SQLExec(xhandle,"Insert invoice (customer,amount,inv_date)" + ;
   " Values('Brown &  Assoc.',$40000,'01/01/96' )")

  IF x < 0
    =errhand()
    RETURN
  ENDIF

  x = SQLExec(xhandle,"Insert invoice (customer,amount,inv_date)" + ;
   "Values('Jones Inc.',$90000,'01/01/96' ) ")

  IF x < 0
    =errhand()
    RETURN
  ENDIF

  x = SQLExec(xhandle,"Insert invoice (customer,amount,inv_date)" + ;
   "Values('Golden Hedge',$25000,'01/01/96' )")

  IF x < 0
    =errhand()
    RETURN
  ENDIF

  x = SQLExec(xhandle,"Insert invoice (customer,amount,inv_date)" + ;
   "Values('CPUs R Us',$50000,'01/01/96' )")

  IF x < 0
    =errhand()
    RETURN
  ENDIF

  x = SQLExec(xhandle,"Select * from invoice","Invoice")

  IF x < 0
    =errhand()
    RETURN
  ENDIF

  SELECT Invoice
  GO TOP
  BROWSE

  x = SQLDisconnect(xhandle)

  IF x < 0
    =errhand()
    RETURN
  ENDIF

  PROCEDURE errhand

    =AERROR(myerror)
    CLEAR
    ? 'An error has occurred.'
    FOR n = 1 TO 7
      ? myerror(n)
    ENDFOR
    =SQLDisconnect(xhandle)

  RETURN