Microsoft KB Archive/316910

= How to create an auto-ID field in a FoxPro table by using ADO and ASP =

Article ID: 316910

Article Last Modified on 8/29/2006

-

APPLIES TO


 * Microsoft Visual FoxPro 7.0 Professional Edition

-



This article was previously published under Q316910



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
 Using Windows Explorer, create a new directory on the local hard disk of a computer that is running Visual FoxPro 7.0. Open VFP 7.0 and change directory (CD command) to the new directory that you just created.  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(&quot;TABLE1&quot;), ;                    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(&quot;TABLE1&quot;,0)

CLOSE DATABASES ALL   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(&quot;IDS_TABLE&quot;) USE SAMPLEDBC!IDS_TABLE IN 0 ENDIF SELECT IDS_TABLE IF SEEK(lcAlias, &quot;IDS_TABLE&quot;, &quot;Tname&quot;) 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(&quot;TABLE1&quot;), 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.   Open the text editor of your choice (NotePad is fine) and paste the following code into a new file: <%

DataPath = &quot;YOUR DIRECTORY PATH\SAMPLEDBC.DBC&quot;

SET oConn = CREATEOBJECT(&quot;ADODB.Connection&quot;) SET oRS = CREATEOBJECT(&quot;ADODB.RECORDSET&quot;)

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

oRS.OPEN &quot;SELECT * FROM TABLE1&quot;, oConn %>

 <BODY> <P> </BODY> </HTML> </li>  Adjust the second line of code (which reads DataPath = &quot;YOUR DIRECTORY PATH\SAMPLEDBC.DBC&quot; ), 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 = &quot;C:\INETPUB\WWWROOT\VFPTEST\SAMPLEDBC.DBC&quot; </li> Save the file as UID_TEST.ASP, in the same directory you created earlier for the VFP .dbc file.</li></ol>

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 &quot;Creating the VFP Data Files&quot; 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.  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:

308135 How to create a virtual directory in Windows 2000

</li> Use Windows Explorer to navigate to the directory containing the .asp file and FoxPro data.</li> Set the appropriate NTFS permissions on the specific files in the folder to give the IUSER_[MachineName] account READ and MODIFY rights to the .asp file and the FoxPro data.</li></ol>

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.

Keywords: kbhowto kbcodesnippet KB316910

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.