DOCUMENT:Q316910  11-MAR-2002  [foxpro]
TITLE   :HOWTO: Create an Auto-ID Field in a FoxPro DBF via ADO and ASP
PRODUCT :Microsoft FoxPro
PROD/VER::7.0
OPER/SYS:
KEYWORDS:kbInternet kbGrpDSFox kbDSupport kbCodeSnippet kbvfp700

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 7.0 
-------------------------------------------------------------------------------

SUMMARY
=======

This article describes how to create an auto-incrementing field in a Visual
FoxPro (VFP) table by using Active Server Pages (ASP) and ActiveX Data Objects
(ADO). This article assumes that you have a good working knowledge of Visual
FoxPro and are comfortable creating and modifying databases. It also assumes you
can use Internet Information Server (IIS) Administrator to create virtual
directories and that you can assign user rights on files and folders under
Microsoft Windows NT, Microsoft Windows 2000, and Microsoft Windows XP.

In order to use this sample, you need Visual FoxPro 7.0 and a computer that is
running both IIS and ADO. The IIS computer must also have the VFP 7.0 OLEDB
provider installed and properly registered.

MORE INFORMATION
================

This demonstration has three parts:

 - Create the VFP data files

 - Create the ASP page

 - Test the sample

Create the VFP Data Files:

1. Using Windows Explorer, create a new directory on the local hard disk of a
   computer that is running Visual FoxPro 7.0.

2. Open VFP 7.0 and change directory (CD command) to the new directory that you
   just created.

3. Run the following code in VFP 7.0 to create a .dbc file and two tables:

   CLOSE DATABASES ALL
   CREATE DATABASE 'SAMPLEDBC.DBC'

   CREATE TABLE 'TABLE1.DBF' NAME 'TABLE1' (UID I NOT NULL DEFAULT newid("TABLE1"), ;
                        FNAME C(10) NOT NULL, ;
                        LNAME C(10) NOT NULL)

   CREATE TABLE 'IDS_TABLE.DBF' NAME 'IDS_TABLE' (TABLENAME C(25) NOT NULL, ;
                           CURRENTKEY I NOT NULL)

   SET COLLATE TO 'MACHINE'
   INDEX ON UPPER(TABLENAME) TAG TNAME
   INSERT INTO IDS_TABLE VALUES("TABLE1",0)

   CLOSE DATABASES ALL

4. Modify the database and open the Stored Procedures window. Paste the
   following code in the Stored Procedures window:

   FUNCTION NewID(tcAlias)
     
     LOCAL lcAlias, ;
           lnID

     lcAlias = UPPER(ALLTRIM(tcAlias))
     lnID = 0
     SET REPROCESS TO AUTOMATIC

     IF !USED("IDS_TABLE")
       USE SAMPLEDBC!IDS_TABLE IN 0
     ENDIF
     SELECT IDS_TABLE
         
     IF SEEK(lcAlias, "IDS_TABLE", "Tname")
       IF RLOCK()
         lnID = IDS_TABLE.CurrentKey
         REPLACE IDS_TABLE.CurrentKey WITH IDS_TABLE.CurrentKey + 1
         UNLOCK
       ENDIF
     ENDIF
       
     RETURN lnID
   ENDFUNC

You now have a database named SAMPLEDBC that contains two tables named TABLE1 and
IDS_TABLE. TABLE1 is the table that you will insert records into by using ASP
and ADO. The IDS_TABLE table is used to keep track of the last unique ID in the
TABLE1 table.

TABLE1 has three fields: UID, FNAME and LNAME. UID is the unique ID field and
will be automatically incremented through the stored procedure and a default
value. When a new record is inserted into this table by way of ASP and ADO, the
UID field is not included in the fields list. This value is generated by a
default value for the field. The default value is actually a call to the stored
procedure entered earlier. This stored procedure, newid("TABLE1"), returns a
unique number that is subsequently added to the UID field when the new record is
saved.

Create the ASP Page:

Now we'll create an ASP page to add records to the TABLE1 table.

1. Open the text editor of your choice (NotePad is fine) and paste the following
   code into a new file:

   <%

   DataPath = "YOUR DIRECTORY PATH\SAMPLEDBC.DBC"

   SET oConn = CREATEOBJECT("ADODB.Connection")
   SET oRS = CREATEOBJECT("ADODB.RECORDSET")

   oConn.OPEN("PROVIDER=VFPOLEDB.1;Data Source=" & DataPath)
   oConn.Execute("Insert into Table1 (FName, Lname)  VALUES ('Test','Guy')")

   oRS.OPEN "SELECT * FROM TABLE1", oConn
   %>

   <HTML>
   <BODY>
   <P>
      <TABLE BORDER=1>
      <TR>
      <% For i = 0 to oRS.Fields.Count - 1 %>
           <TD><B><% = UCASE(oRS(i).Name) %></B></TD>
      <% Next %>
      </TR>
      <% Do While Not oRS.EOF %>
           <TR>
           <% For i = 0 to oRS.Fields.Count - 1 %>
                <TD VALIGN=TOP><% = oRS(i) %></TD>
           <% Next %>
           </TR>
           <%
           oRS.MoveNext
      Loop
      
      oRS.Close
      oConn.Close   
      SET oRS = nothing
      SET oConn = NOTHING
      %>
      </TABLE>
   </BODY>
   </HTML>

2. Adjust the second line of code (which reads DataPath = "YOUR DIRECTORY
   PATH\SAMPLEDBC.DBC" ), changing YOUR DIRECTORY PATH part to the name of the
   directory you created earlier. For instance, if your VFP database is in
   C:\INETPUB\WWWROOT\VFPTEST, that line should read as follows:

   DataPath = "C:\INETPUB\WWWROOT\VFPTEST\SAMPLEDBC.DBC"

3. Save the file as UID_TEST.ASP, in the same directory you created earlier for
   the VFP .dbc file.

You now have an ASP page that will first insert a new record into the TABLE1
table, and will then query that table for all records and display them in an
HTML table.

Test the Sample:

Now let's put it all together. If the computer that is running VFP 7.0 is not
running IIS, you should copy the directory created in step 1 of the "Creating
the VFP Data Files" section to your IIS computer. Be sure to adjust the second
line in the ASP code accordingly. Change the YOUR DIRECTORY PATH test to reflect
the directory that the tables and .asp file are in.

1. Open the IIS Administrator and create a new virtual directory. Point it to
   the directory that contains the .asp file and FoxPro data. For more
   information, click the article number below to view the article in the
   Microsoft Knowledge Base:

   Q308135 HOW TO: Create a Virtual Directory in Windows 2000

2. Use Windows Explorer to navigate to the directory containing the .asp file
   and FoxPro data.

3. Set the appropriate NTFS permissions on this folder, giving the
   IUSER_[MachineName] account READ and MODIFY rights.

You should now be able to view the ASP page from a browser and see that unique
IDs are being entered into the FoxPro table.

Additional query words:

======================================================================
Keywords          : kbInternet kbGrpDSFox kbDSupport kbCodeSnippet kbvfp700 
Technology        : kbVFPsearch kbAudDeveloper kbVFP700
Version           : :7.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2002.