How to: Access Visual FoxPro Data in Visual Studio

Article
07/09/2007

You can access Visual FoxPro databases and tables in Visual Studio using the Visual FoxPro OLE DB Provider. The Visual FoxPro OLE DB Provider is available through the Visual Studio Server Explorer or using connection strings in code.

Accessing Visual FoxPro Data Sources Using the Visual Studio Server Explorer

You can access Visual FoxPro databases and tables in Visual Studio by adding them to the list of data connections that appear in the Visual Studio Server Explorer pane. You need to first specify a connection through the Visual FoxPro OLE DB Provider to the databases and tables you want. You can then select the databases or tables so that they appear in the Visual Studio Server Explorer pane.

Note

You must be able to specify the Visual FoxPro database or table folder you want to access by providing the path and file name or browsing to it.

To connect to a Visual FoxPro database or table through the Visual FoxPro OLE DB Provider

Open Visual Studio.
From the View menu, select Server Explorer.
In the Server Explorer pane, right-click Data Connections, and click Add Connection.
In the Data Link Properties dialog box, click the Provider tab.
Select Microsoft OLE DB Provider for Visual FoxPro.

The Connection tab in the Data Link Properties dialog box appears.

To add a Visual FoxPro database or table

On the Connection tab and in the Select or enter a database name box, type path and name of the database or table folder you want.

-or-

To browse for a Visual FoxPro database or table folder, click the ellipsis (...) button to the right of the Select or enter a database name box to open the Configure Connection dialog box.
Specify a different collating sequence if desired.
To test the connection, click Test Connection. If connection is successful, click OK.

The database or table directory specified appears in the Visual Studio Server Explorer pane below the Data Connections node.

Accessing Visual FoxPro Data Sources Using Connection Strings

To access a Visual FoxPro data source in code, use a valid connection string to specify the Visual FoxPro OLE DB Provider, data source or data source name. For example, you can access the Visual FoxPro OLE DB Provider using Visual FoxPro code.

You can also specify an existing Visual FoxPro ODBC Data Source Name (DSN) instead of a data source. The Provider uses the data source indicated in the DSN and expands it to an appropriate Provider connection string.

To connect to the Visual FoxPro OLE DB Provider in Visual FoxPro

Establish an ActiveX Data Object (ADO) Connection object and create a data object in Visual FoxPro.

Specify the Visual FoxPro OLE DB Provider and data source in a connection string as shown in the following code:

oConn = CREATEOBJECT("ADODB.Connection")
oConn.ConnectionString = "provider=vfpoledb.1;;
   data source=.\MyTestDatabase.dbc"
oConn.Open

This code creates a data object you can use to retrieve data.

Note

Be sure to replace MyTestDatbase with the appropriate database name.

Instead of a data source, you can use an existing Open Database Connectivity (ODBC) data source name (DSN) in the connection string for the Visual FoxPro OLE DB Provider. The Visual FoxPro OLE DB Provider accepts the argument, DSN = cDSNName, and uses the data source specified by the DSN as shown in the following example:

oConn=CREATEOBJECT("adodb.connection")
oConn.ConnectionString="Provider=vfpoledb;DSN=ODBCdataSourceName"
oConn.Open()

Note

Be sure to replace ODBCdataSourceName with the appropriate ODBC DSN, for example, vfpTestData.

After the connection is open, you can query the value of ConnectionString to determine the data source evaluated.

A connection string includes the following attribute keywords and values:

Provider= cVFPOLEDBProvider
Specifies the Visual FoxPro OLE DB Provider (VFPOLEDB).

Data Source= cPath
Specifies the path to the Visual FoxPro database or a folder containing free tables. For example, c:\Microsoft Visual FoxPro\Samples\Data\Testdata.dbc

DSN= cDSNName
Specifies an existing ODBC DSN.

Mode= cMode
Specifies one of the following: Read, ReadWrite, Share Deny None (default), Share Deny Read, Share Deny Write, or Share Exclusive, which includes the previous two modes.

For a complete explanation of connection string syntax, see the Microsoft OLE DB 2.5 Programmer's Reference and SDK Guide.

Updating Visual FoxPro Data

You can update Visual FoxPro Data through the Visual FoxPro OLE DB Provider using ADO. The following procedure describes three ways you can update Visual FoxPro data using data from the sample Northwind database, located in the Visual FoxPro ..\Samples\Northwind directory.

Note

Be sure to replace NorthWind.dbc with the database you want to use.

To update Visual FoxPro data through the Visual FoxPro OLE DB Provider using ADO

Call an update, insert, or delete command directly.

To call an update command directly, use the ADO Execute method. For example:

CLEAR
LOCAL oConn as "adodb.connection"
LOCAL oRS as "adodb.recordset"
oConn = CREATEOBJECT('adodb.connection')
oRS = CREATEOBJECT('adodb.recordset')
cConnStrng = ;
   "Provider=vfpoledb;Data Source="+HOME(2)+"Northwind\Northwind.dbc"
oConn.Open(cConnStrng)
oConn.BeginTrans()
oConn.Execute("UPDATE Customers SET contactname = 'Patricio X. Simpson';
   WHERE customerid='CACTU'")
oRS = oConn.Execute("SELECT * FROM customers WHERE customerid = 'CACTU'")
?oRS.Fields("contactname").Value
oConn.RollbackTrans()
oRS.Requery()
?oRS.Fields('contactname').Value
oRS.Close()
oConn.Close()

-or-

Use a client cursor.

For example:

CLEAR
LOCAL oConn as "adodb.connection"
LOCAL oRS as "adodb.recordset"
oConn = CREATEOBJECT('adodb.connection')
oRS = CREATEOBJECT('adodb.recordset')
cConnStrng = ;
   "Provider=vfpoledb;Data Source="+HOME(2)+"Northwind\Northwind.dbc"
oConn.CursorLocation= 3            && adUseClient
oConn.Open(cConnStrng)

*!* Open RecordSet using keyset cursor and optimistic locking.
oRS.Open(;
   "SELECT * FROM customers WHERE customerid = 'CACTU'",oConn,1,3,1)
? 'Current value:',oRS.Fields("contactname").Value
oRS.Fields("contactname").Value = "Patricio X. Simpson"
oRS.Update()
oRS.Requery()
? 'New value:',oRS.Fields("contactname").Value
oRS.Close()
oConn.Close()

-or-

Use a server cursor.

For example:

LOCAL oRS as "adodb.recordset"
oConn = CREATEOBJECT('adodb.connection')
oRS = CREATEOBJECT('adodb.recordset')
cConnStrng = ;
   "Provider=vfpoledb;Data Source="+HOME(2)+"Northwind\Northwind.dbc"
oConn.Open(cConnStrng)

*!* Updatable server cursors must use the USE <table name> command to 
*!* open the table, not a SELECT statement. Server cursor is updatable,
*!* opened keyset, and lock optimistic.
oRS.Open("USE customers",oconn,1,3,1)
? 'CursorLocation:',IIF(oRS.CursorLocation=2,"adUseServer","adUseClient")

* Find CACTU.
? oRS.Find("customerid='CACTU'")
? 'Current value:',oRS.Fields("contactname").Value
oRS.Fields("contactname").Value = "Patricio Simpson"
oRS.Update()
oRS.Requery()
oRS.Find("customerid='CACTU'")
? 'New value:',oRS.Fields("contactname").Value 
oRs.Close()
oRS =''
oConn.Close()oConn=''

Accessing Visual FoxPro Data from Different Languages

You can access the Visual FoxPro OLE DB Provider from other languages using different connection strings, depending on the language. For example, to connect to a Visual FoxPro database from a Visual C# application, you can use the following connection string, replacing myVFPDatabase with the appropriate data source name:

oleDbConnection1.ConnectionString = "Provider=VFPOLEDB.1;" + 
   "Data Source=C:\\myVFPDatabase.DBC;";

The following code examples illustrate how to use connection strings that specify the Visual FoxPro OLE DB Provider and data source when accessing Visual FoxPro data from different languages.

Note

Be sure to replace myVFPDatabase with the appropriate data source or DSN.

To access a Visual FoxPro data source in Visual C#

Use a connection string to specify the Visual FoxPro OLE DB Provider and access the data source as shown in the following code:

OleDbConnection oleDbConnection1 = new OleDbConnection("Provider=VFPOLEDB.1;" + 
      "Data Source=C:\\myVFPDatabase.DBC;");
oleDbConnection1.Open();

To access a Visual FoxPro data source in Visual Basic

Use a connection string to specify the Visual FoxPro OLE DB Provider and the data source as shown in the following code, where oConnection represents an ADO Connection object:
```
oConnection.Open("Provider=vfpoledb.1;
   Data Source=.\myVFPDatabase.dbc")
```

To access Visual FoxPro data using ActiveX Data Objects (ADO) in Visual Basic

Use a connection string to specify the Visual FoxPro OLE DB Provider and the data source as shown in the following code:

Imports System.Data.OleDb
Public Class ConnectToVFP
    Public Function ADONETOpenVFPDatabase() As Boolean
        Dim bIsConnected As Boolean = True
        Try
            Dim cnn As New OleDbConnection("Provider=VFPOLEDB.1;" + _
               "Data Source=.\MyTestDatabase.dbc;")
            cnn.Open()
        Catch e As System.Exception
            bIsConnected = False
        End Try
        Return bIsConnected
    End Function
End Class

Note

Make sure to replace MyTestDatabase with the name of the database or table directory you want to access.

To access a Visual FoxPro data source in Visual C++

Set the database properties by specifying the DBSetProp array, DBProp array, and the IDBProperties pointer, followed by a connection string that specifies the Visual FoxPro OLE DB Provider as shown in the following code:

HRESULT hr;
CLSID clsid;     

// Set the DBProp array.
DBPROP iProp[1];
DBPROPSET rgIP;
IDBProperties* pIDBProperties = NULL;
IDBInitialize *pIDBInitialize = NULL;
VariantInit(&iProp[0].vValue);
iProp[0].dwOptions = DBPROPOPTIONS_OPTIONAL;    // Required
iProp[0].colid = DB_NULLID;

// Set the location of data source. 
iProp[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
iProp[0].vValue.vt = VT_BSTR;
iProp[0].vValue.bstrVal = L"c:\myVFPDatabase.dbc"; // Data source

// Set DBpropset to point to the DBPROP array.
rgIP.guidPropertySet = DBPROPSET_DBINIT;
rgIP.cProperties = 1;
rgIP.rgProperties = iProp;
::CoInitialize(NULL);
hr = CLSIDFromProgID(L"vfpoledb.1",&clsid); // Specify OLE DB Provider.
hr = CoCreateInstance(clsid,
       NULL,
       CLSCTX_INPROC_SERVER,
       IID_IDBInitialize,
       (void**)&pIDBInitialize);

// Initialize.
hr = piDBInitialize->QueryInterface(_uuidof(IDBProperties), (void**)&pIDBProperties);
hr = pIDBProperties->SetProperties(1 , &rgIP) ;
hr = pIDBProperties->Release();
hr = pIDBInitialize->Initialize();

To access a Visual FoxPro data source in ASP using ActiveX Data Objects (ADO)

Create an ASP page with appropriate HTML tags.

To establish a connection and run commands using ADO, add Visual Basic Scripting Language (VBScript) code:

<%
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionString="Provider=VFPOLEDB.1;
       Data Source=myVFPDatabase.dbc"
    conn.Open
    sql = "select * from tableName"
    Set rsArrival = conn.Execute(sql)
%>

Note

Be sure to replace tableName with the name of the table you want.

To access a Visual FoxPro data source in JScript

Use a connection string to specify the Visual FoxPro OLE DB Provider and data source as shown in the following code:

var vbOKCancel = 0;
var vbInformation = 64;
var vbCancel = 2;
var L_Welcome_MsgBox_Message_Text = "This script demonstrates how to access VFP OLE DB Provider using the Windows Scripting Host.";
var L_Welcome_MsgBox_Title_Text = "VFP OLE DB Provider JScript Sample";
var sBuffer = "";
var sConnString = "Provider=vfpoledb.1;Data Source=myVFPDatabase.dbc";
var oConn = new ActiveXObject("ADODB.Connection");
var oRS = new ActiveXObject("ADODB.Recordset");
oConn.Open(sConnString);
oRS.Open("select * from tableName where fieldName1='fieldValue'",oConn,3,3);
// Get tableName.fieldName2
sBuffer = oRS.Fields('fieldName2').value;

var WSHShell = WScript.CreateObject("WScript.Shell");
var intDoIt;
intDoIt =  WSHShell.Popup(sBuffer,
   0,
   L_Welcome_MsgBox_Title_Text,
   vbOKCancel );
   if (intDoIt == vbOKCancel) {
      oRS.Close();
      oConn.Close();
      WScript.Quit();
}

Note

Be sure to replace myVFPDatabase, tableName, fieldName1, fieldName2, and fieldValue with the appropriate values. You must use a backslash (\) escape characters to include the backslash character in the file path, for example, "c:\\MyFolder\\MyVFP.dbc".

How to: Access Visual FoxPro Data in Visual Studio

Accessing Visual FoxPro Data Sources Using the Visual Studio Server Explorer

To connect to a Visual FoxPro database or table through the Visual FoxPro OLE DB Provider

To add a Visual FoxPro database or table

Accessing Visual FoxPro Data Sources Using Connection Strings

To connect to the Visual FoxPro OLE DB Provider in Visual FoxPro

Updating Visual FoxPro Data

To update Visual FoxPro data through the Visual FoxPro OLE DB Provider using ADO

Accessing Visual FoxPro Data from Different Languages

To access a Visual FoxPro data source in Visual C#

To access a Visual FoxPro data source in Visual Basic

To access Visual FoxPro data using ActiveX Data Objects (ADO) in Visual Basic

To access a Visual FoxPro data source in Visual C++

To access a Visual FoxPro data source in ASP using ActiveX Data Objects (ADO)

To access a Visual FoxPro data source in JScript

See Also

Tasks

Reference

Other Resources

Additional resources