Link to home
Start Free TrialLog in
Avatar of swingler
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.FieldCount - 1];

                while( foxReader.Read() )
                {
                    foxReader.GetValues( Values );
                    dtData.Rows.Add( Values );
                }

                dgvData.DataSource = dtData;

            }
            catch( System.Exception err )
            {
                MessageBox.Show( err.Message );
            }

        }
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of swingler
swingler

ASKER

i've tried it w/ & without the Mode parameter and still no go.

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.
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
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\
@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"
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
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.
The exception was on the connection Open call, which is before the call to create a data reader:

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.CONNECTION')
oConn.Open('Provider=VFPOLEDB;Data Source=C:\Program Files\Microsoft Visual FoxPro 9\Samples\Northwind\customers.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.
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";

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.CONNECTION')
oConn.Open('Provider=Microsoft OLE DB Provider for Visual FoxPro;Data Source=C:\')
? oConn.Properties('Provider Version').VALUE   && returns 09.00.0000.4611, the latest version
@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.ConnectionString = "Provider=VFPOLEDB.1;" +
   "Data Source=C:\\myVFPDatabase.DBC;";


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.ConnectionString = "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.
That connection string is missing all these:

Mode=Read|Share Deny None;Collating Sequence=MACHINE";

Bob
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.
@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
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.
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.FieldCount - 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
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
The DBC -- database contailer file
Cointains - filenames of tables included, relations,triggers,views,connections, 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,connections,stordproc,triggers,  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


You can verfy the same with
     
                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;
                                        Collating Sequence=MACHINE";
                foxConn.Open();

dtData = foxConn.GetSchema("Tables");