Q316910: HOWTO: Create an Auto-ID Field in a FoxPro DBF via ADO and ASP
Article: Q316910
Product(s): Microsoft FoxPro
Version(s): 7.0
Operating System(s):
Keyword(s): kbInternet kbGrpDSFox kbDSupport kbCodeSnippet kbvfp700
Last Modified: 11-MAR-2002
-------------------------------------------------------------------------------
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 1986-2002.