CODEWITHSUNDEEP
c#oledbvisual-foxpro
bphdpt
bphdpt

Reputation: 1

VFPOleDb schema returned but no results in C#

I'm at the end of my rope on this issue. Trying to connect to a FoxPro directory hosted locally, using the Microsoft OLE DB Provider for Visual FoxPro 9.0 with the following code:

using (var con = new OleDbConnection(@"Data Source=C:\FoxDB;Provider=VFPOLEDB.1;"))
{
    con.Open();
    using (var cmd = new OleDbCommand("select * from order", con))
    {               
        var dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Debug.WriteLine(dr["ord_id"]);
        }
    }
}

Executing the code does not throw an exception, but there are zero rows returned. The schema is discovered and returned, as it has 72 fields present when I examine the data reader (I've done the same with data tables, data sets, data adapters, etc. All return the schema, but zero results).

Building an SSIS package to access the same table and pull into a MSSQL database results in 3,828 records being pulled in. order.dbf on disk is 884kb which seems to jive with the SSIS results I've pulled in.

I've tried adding Collation Sequence to the connection string, both Machine and General to no effect.

Please tell me there's something obvious I'm missing!

UPDATE: So apparently there's something that I just don't understand about FoxPro. If I change the command type to CommandType.TableDirect and switch the command text to just be order, it returns all the rows. Any insight would be appreciated.

Upvotes: 0

Views: 459

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

I think the problem is not with Foxpro, testing with the same exact code, I can get the result (created a free test table in c:\FoxDb). Ensure that you are using the latest VFPOLEDB driver. It looks like the problem lies within your table.

BTW, order is a keyword, you better write it as:

"select * from [order]"

although it would work as you did (VFP is forgiving in that regard). The problem might also lie in collation sequence you have used (I never use collation sequences other than machine, they are problematic in Turkish, I expect the same in some other languages).

Upvotes: 1

Related Questions