swingler
asked on
Error on OleDb Connection to Foxpro Tables "Feature is not available"
I am attempting to make a connection to a foxpro table using the Visual FoxPro OLE DB Provider.
Below is the code I am using to connect to the table, but it poops out on the Connection.Open() and gives me a "Feature is not available" error. Any one ever seen that before? Any ideas?
thx,
Aaron
private void GetData( )
{
OleDbConnection foxConn = new OleDbConnection();
OleDbCommand foxCmd;
OleDbDataReader foxReader;
DataTable dtData = new DataTable();
try
{
foxConn.ConnectionString = @"Provider=VFPOLEDB;Data
Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind \customers .dbf
;Mode=Read|Share Deny None;Collating
Sequence=MACHINE";
foxConn.Open();
foxCmd = new OleDbCommand("SELECT * FROM customers",foxConn);
foxCmd.CommandType = CommandType.Text;
foxReader = foxCmd.ExecuteReader();
for( int i = 0; i < foxReader.FieldCount - 1; i++ )
{
dtData.Columns.Add( new DataColumn( foxReader.GetName( i ), foxReader.GetFieldType( i ) ) );
}
object[] Values = new object[foxReader.FieldCoun t - 1];
while( foxReader.Read() )
{
foxReader.GetValues( Values );
dtData.Rows.Add( Values );
}
dgvData.DataSource = dtData;
}
catch( System.Exception err )
{
MessageBox.Show( err.Message );
}
}
Below is the code I am using to connect to the table, but it poops out on the Connection.Open() and gives me a "Feature is not available" error. Any one ever seen that before? Any ideas?
thx,
Aaron
private void GetData( )
{
OleDbConnection foxConn = new OleDbConnection();
OleDbCommand foxCmd;
OleDbDataReader foxReader;
DataTable dtData = new DataTable();
try
{
foxConn.ConnectionString = @"Provider=VFPOLEDB;Data
Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind
;Mode=Read|Share Deny None;Collating
Sequence=MACHINE";
foxConn.Open();
foxCmd = new OleDbCommand("SELECT * FROM customers",foxConn);
foxCmd.CommandType = CommandType.Text;
foxReader = foxCmd.ExecuteReader();
for( int i = 0; i < foxReader.FieldCount - 1; i++ )
{
dtData.Columns.Add( new DataColumn( foxReader.GetName( i ), foxReader.GetFieldType( i ) ) );
}
object[] Values = new object[foxReader.FieldCoun
while( foxReader.Read() )
{
foxReader.GetValues( Values );
dtData.Rows.Add( Values );
}
dgvData.DataSource = dtData;
}
catch( System.Exception err )
{
MessageBox.Show( err.Message );
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should be able to open legacy FoxPro tables with the VFP OleDb provider. I am trying to remember the problems with FoxPro and OleDb. One thing I did notice is that the connection strings specify vfpoledb.1, and not just VFPOLEDB.
Bob
Bob
The Data Source should be the folder containing the DBF file, not the path to the file itself.
Try
Data Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind \
Try
Data Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind
ASKER
@thelearnedone
i've tried w/ & without the trialing .1 on the provider to no avail (oddly some examples i've seen wandering the net do not use the trailing .1)
@dstanley9
no dice. when pointing to the diretory i am still getting the error "feature is not available"
i've tried w/ & without the trialing .1 on the provider to no avail (oddly some examples i've seen wandering the net do not use the trailing .1)
@dstanley9
no dice. when pointing to the diretory i am still getting the error "feature is not available"
As a pure FoxPro developer, what is the significance of the reference to FoxReader; i.e., what in the heck is FoxReader?
@CarlWarner,
FoxReader is a data reader that implements IDbDataReader. The instance is created at this line:
foxReader = foxCmd.ExecuteReader();
It is a forward-only cursor view of the data table, if that makes any sense to you.
Bob
FoxReader is a data reader that implements IDbDataReader. The instance is created at this line:
foxReader = foxCmd.ExecuteReader();
It is a forward-only cursor view of the data table, if that makes any sense to you.
Bob
Yes, I realize that instance is created with that line. I've just never ever seen the OLE DB Provider for VFP use that and I wonder if it even supports it. Maybe that's what is kicking off the "Featire is not available" message. That's why I mentioned it -- it sticks out like a big sore thumb to me.
How to: Access Visual FoxPro Data in Visual Studio
http://msdn2.microsoft.com/en-us/library/3haz2895(VS.80).aspx
Language Reference for OLE DB Development
http://msdn2.microsoft.com/en-us/library/sht28225(VS.80).aspx
If that feature exists, it will have been the first time I've seen it. But then again I work natively in the VFP programming and database environment, querying SQL Server and other back-ends as the need arises.
How to: Access Visual FoxPro Data in Visual Studio
http://msdn2.microsoft.com/en-us/library/3haz2895(VS.80).aspx
Language Reference for OLE DB Development
http://msdn2.microsoft.com/en-us/library/sht28225(VS.80).aspx
If that feature exists, it will have been the first time I've seen it. But then again I work natively in the VFP programming and database environment, querying SQL Server and other back-ends as the need arises.
The exception was on the connection Open call, which is before the call to create a data reader:
foxConn.Open();
Bob
foxConn.Open();
Bob
I setup a quick and dirty OLE DB session within interactive Visual FoxPro. It really is its own session with the possibility of settings totally different from that of a default/native VFP interactive session.
I issued the following commands without error:
oConn = NEWOBJECT('ADODB.CONNECTIO N')
oConn.Open('Provider=VFPOL EDB;Data Source=C:\Program Files\Microsoft Visual FoxPro 9\Samples\Northwind\custom ers.dbf')
oRS = oConn.Execute('SELECT * FROM customers')
? oRS.Fields(0).value && returns "ALFKI"
? oRS.Fields(0).name && returns "customerid"
? oRS.Fields(1).name && returns companyname
? oRS.Fields(1).value && returns "Alfreds Futterkiste"
I don't know the proper syntax for what you are trying to do. But it seems you have the basics there but maybe the addition of a simple extra character throws it all off. Just giving you what works without error from within VFP9 that makes calls to the OLE DB Provider for VFP.
I issued the following commands without error:
oConn = NEWOBJECT('ADODB.CONNECTIO
oConn.Open('Provider=VFPOL
oRS = oConn.Execute('SELECT * FROM customers')
? oRS.Fields(0).value && returns "ALFKI"
? oRS.Fields(0).name && returns "customerid"
? oRS.Fields(1).name && returns companyname
? oRS.Fields(1).value && returns "Alfreds Futterkiste"
I don't know the proper syntax for what you are trying to do. But it seems you have the basics there but maybe the addition of a simple extra character throws it all off. Just giving you what works without error from within VFP9 that makes calls to the OLE DB Provider for VFP.
You are connecting to a databse not a free table so the connectionstring should read
foxConn.ConnectionString = @"Provider=VFPOLEDB.1;Data
Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind \northwind .dbc;Mode= Read Share Deny None;Password="";Collating
Sequence=MACHINE";
foxConn.ConnectionString = @"Provider=VFPOLEDB.1;Data
Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind
Sequence=MACHINE";
Also, I have no idea which version of the OLE DB Provider for VFP you are using. Up until now, we simple have assumed you are using the latest. There have been multiple iterations of this provider.
The latest can be found here:
Microsoft OLE DB Provider for Visual FoxPro (through) 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
oConn = NEWOBJECT('ADODB.CONNECTIO N')
oConn.Open('Provider=Micro soft OLE DB Provider for Visual FoxPro;Data Source=C:\')
? oConn.Properties('Provider Version').VALUE && returns 09.00.0000.4611, the latest version
The latest can be found here:
Microsoft OLE DB Provider for Visual FoxPro (through) 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
oConn = NEWOBJECT('ADODB.CONNECTIO
oConn.Open('Provider=Micro
? oConn.Properties('Provider
ASKER
@suhashegde
yes, i'm aware that the posted connection string isn't correct. however i have tried modifying the string as you've suggested with no better result.
@CarlWarner
RE: OleDbDataReader foxReader - i'm using an OLE connection to open the table and using the DataReader for reading the contents of the table into my DataTable. I can't imagine that my issue is centered around my Ole data reader but as pointed out I'm not even getting to that point in the code.
RE: VFP Provider - Your assumption is correct I am using the latest OLE DB Provider for VFP (v9.0).
RE: ADODB - I hadn't considered using the ADODB framework to make the connection to the Fox Pro table, but if that is the only way then I'll give it a go.
The example code on the page you linked to (How to: Access Visual FoxPro Data in Visual Studio) suggests using and OleDb connection when using C# as I am.
snippet from page:
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.Connectio nString = "Provider=VFPOLEDB.1;" +
"Data Source=C:\\myVFPDatabase.D BC;";
yes, i'm aware that the posted connection string isn't correct. however i have tried modifying the string as you've suggested with no better result.
@CarlWarner
RE: OleDbDataReader foxReader - i'm using an OLE connection to open the table and using the DataReader for reading the contents of the table into my DataTable. I can't imagine that my issue is centered around my Ole data reader but as pointed out I'm not even getting to that point in the code.
RE: VFP Provider - Your assumption is correct I am using the latest OLE DB Provider for VFP (v9.0).
RE: ADODB - I hadn't considered using the ADODB framework to make the connection to the Fox Pro table, but if that is the only way then I'll give it a go.
The example code on the page you linked to (How to: Access Visual FoxPro Data in Visual Studio) suggests using and OleDb connection when using C# as I am.
snippet from page:
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.Connectio
"Data Source=C:\\myVFPDatabase.D
ASKER
interesting development. i just tried out that snippet of code from the above post and connected to the sample fox pro data that came w/ the ole db provider like so:
oleDbConnection1.Connectio nString = "Provider=VFPOLEDB.1;" +
@"Data Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind \northwind .dbc;";
oleDbConnection1.Open();
and it worked! so the question is it due to the fact i'm opening a dbc? or maybe the trailing ';' after each parameter of the connection string.
oleDbConnection1.Connectio
@"Data Source=C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind
oleDbConnection1.Open();
and it worked! so the question is it due to the fact i'm opening a dbc? or maybe the trailing ';' after each parameter of the connection string.
That connection string is missing all these:
Mode=Read|Share Deny None;Collating Sequence=MACHINE";
Bob
Mode=Read|Share Deny None;Collating Sequence=MACHINE";
Bob
ASKER
we have a winner folks. i'm not positive what was causing the trouble but for those who are interested here's the final connection string:
foxConn.ConnectionString = "Provider=VFPOLEDB.1;" +
@"Data Source=C:\SlimWin\;" +
"Collating Sequence=MACHINE;";
not positive what the other parameters are responsible for but with them ommited i'm able to get to the data so i'll go from there.
foxConn.ConnectionString = "Provider=VFPOLEDB.1;" +
@"Data Source=C:\SlimWin\;" +
"Collating Sequence=MACHINE;";
not positive what the other parameters are responsible for but with them ommited i'm able to get to the data so i'll go from there.
ASKER
@thelearnedone
yeah i noticed that too. i got my original string from connectionstrings.com and it had those same additional parameters. no explanation on what they are responsible for.
edit: i just appended those to the string and i'm still connecting. strange.
i wonder if it has to do with where i'm placing the '@' in my connection string. (the @ btw, for non C# persons allows me to not have to escape all the '\' in my file path
yeah i noticed that too. i got my original string from connectionstrings.com and it had those same additional parameters. no explanation on what they are responsible for.
edit: i just appended those to the string and i'm still connecting. strange.
i wonder if it has to do with where i'm placing the '@' in my connection string. (the @ btw, for non C# persons allows me to not have to escape all the '\' in my file path
ASKER
so i'm not sure what the issue was as it seems like i've come full circle.
just an FYI, it is possible to specify a specific table in the connection string
ie. Data Source=C:\MyData\data.dbf;
though i don't see any advantage to that vs point the data source the directory. maybe a security measure to limit the access to a specific set of data?
so since i ultimately ended up using what was essentially the same connection string from connectionstrings.com which was first suggested thelearnedone i'll award the points to him.
just an FYI, it is possible to specify a specific table in the connection string
ie. Data Source=C:\MyData\data.dbf;
though i don't see any advantage to that vs point the data source the directory. maybe a security measure to limit the access to a specific set of data?
so since i ultimately ended up using what was essentially the same connection string from connectionstrings.com which was first suggested thelearnedone i'll award the points to him.
ASKER
here's the complete bit of code for anyone who's interested:
private void GetData( )
{
OleDbConnection foxConn = new OleDbConnection();
OleDbCommand foxCmd;
OleDbDataReader foxReader;
DataTable dtData = new DataTable();
try
{
foxConn.ConnectionString = @"Provider=VFPOLEDB.1;
Data Source=C:\FoxProData\;
Mode=Read|Share Deny None;
Collating Sequence=MACHINE";
foxConn.Open();
foxCmd = new OleDbCommand("SELECT * FROM data",foxConn);
foxCmd.CommandType = CommandType.Text;
foxReader = foxCmd.ExecuteReader();
for( int i = 0; i < foxReader.FieldCount - 1; i++ )
{
dtData.Columns.Add( new DataColumn( foxReader.GetName( i ), foxReader.GetFieldType( i ) ) );
}
object[] Values = new object[foxReader.FieldCoun t - 1];
while( foxReader.Read() )
{
foxReader.GetValues( Values );
dtData.Rows.Add( Values );
}
dgvData.DataSource = dtData;
}
catch( System.Exception err )
{
MessageBox.Show( err.Message );
}
}
thanks for the help everyone
private void GetData( )
{
OleDbConnection foxConn = new OleDbConnection();
OleDbCommand foxCmd;
OleDbDataReader foxReader;
DataTable dtData = new DataTable();
try
{
foxConn.ConnectionString = @"Provider=VFPOLEDB.1;
Data Source=C:\FoxProData\;
Mode=Read|Share Deny None;
Collating Sequence=MACHINE";
foxConn.Open();
foxCmd = new OleDbCommand("SELECT * FROM data",foxConn);
foxCmd.CommandType = CommandType.Text;
foxReader = foxCmd.ExecuteReader();
for( int i = 0; i < foxReader.FieldCount - 1; i++ )
{
dtData.Columns.Add( new DataColumn( foxReader.GetName( i ), foxReader.GetFieldType( i ) ) );
}
object[] Values = new object[foxReader.FieldCoun
while( foxReader.Read() )
{
foxReader.GetValues( Values );
dtData.Rows.Add( Values );
}
dgvData.DataSource = dtData;
}
catch( System.Exception err )
{
MessageBox.Show( err.Message );
}
}
thanks for the help everyone
You award points to any one(I am not interested in Points) , But I would like to point that if you were to use the tables(directly) in the Database container directly ,then the triggers,Relations,Stord procedures wouldnt take effect. So be sure to use the datasource as databasename.dbc
Also to build a connectionstring,
It is a total waste of time to copy from web sites . In fact you can build it yourself.
Try this
Create a text file on the desktop and rename it with .UDL extension
Double click it and follow
Modify all what you need and even test the connection
If test is successful then you can use it.
Close the designer and the right click the same and open it in notepad
What do you see ? the coneection string itself
ALso about VFPOLEDB and VFPOLEDB.1
Vfpoledb.1 would initialise the specific version if many are installed
Also to build a connectionstring,
It is a total waste of time to copy from web sites . In fact you can build it yourself.
Try this
Create a text file on the desktop and rename it with .UDL extension
Double click it and follow
Modify all what you need and even test the connection
If test is successful then you can use it.
Close the designer and the right click the same and open it in notepad
What do you see ? the coneection string itself
ALso about VFPOLEDB and VFPOLEDB.1
Vfpoledb.1 would initialise the specific version if many are installed
The DBC -- database contailer file
Cointains - filenames of tables included, relations,triggers,views,c onnections , and many more.
This is kinda sort of defination files
.DBF this is where the data is really stored but the backlink is pointed to the DBC for joins,relations,views,conn ections,st ordproc,tr iggers, so this is a seperate file,
You NEED to open the dbc in order to take the complete effect of opening the database i.e. when the dbc is opend the u can directly query any table/tables w/w/o releations and the data accross tables remain consistent. so in your code upen the connection to the dbc and query the DBF
like connstring points to :
C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind \northwind .dbc
And
the table you might use is
select * from Customer
Which opens the customer with all the associated properties in DBC
Cointains - filenames of tables included, relations,triggers,views,c
This is kinda sort of defination files
.DBF this is where the data is really stored but the backlink is pointed to the DBC for joins,relations,views,conn
You NEED to open the dbc in order to take the complete effect of opening the database i.e. when the dbc is opend the u can directly query any table/tables w/w/o releations and the data accross tables remain consistent. so in your code upen the connection to the dbc and query the DBF
like connstring points to :
C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind
And
the table you might use is
select * from Customer
Which opens the customer with all the associated properties in DBC
You can verfy the same with
foxConn.ConnectionString ="Provider=VFPOLEDB.1;
Data Source=C:\\Program Files\\Microsoft Visual FoxPro OLE DB Provider\\Samples\\Northwi nd\\northw ind.dbc
Mode=Read Share Deny None;
Collating Sequence=MACHINE";
foxConn.Open();
dtData = foxConn.GetSchema("Tables" );
foxConn.ConnectionString ="Provider=VFPOLEDB.1;
Data Source=C:\\Program Files\\Microsoft Visual FoxPro OLE DB Provider\\Samples\\Northwi
Mode=Read Share Deny None;
Collating Sequence=MACHINE";
foxConn.Open();
dtData = foxConn.GetSchema("Tables"
ASKER
is it possible that the version of foxpro i'm using is too old to create a connection with? i'm not positive but i think the tables are either v2.5 or v3. it was my understanding that the ole db provider was backwards compatible but i haven't found anything to prove it true or otherwise.