Trying to access to a DBF file using Advantage OLE DB provider throws an exception when opening a connection

Question

I have an ASP.NET MVC application which is trying to open below OLE DB connection:

string conString = @"Provider=Advantage OLE DB Provider;Data Source=" + dbfFilePath + ";Extended Properties=dBASE IV;";

using (dBaseConnection = new OleDbConnection(conString))
{
   dBaseConnection.Open();
   // Some stuff
}

I have installed below package from here.

I am using this provider in order to access a dbf file (specified on the dbfFilePath variable) and then later add some information into it. When I perform the Open command on the above code snippet I get below exception message:

Error 6420: The 'Discovery' process for the Advantage Database Server failed. Unable to connect to the Advantage Database Server. axServerConnect AdsConnect.

Previously I was using VFPOLEDB.4 provider and it was working ok when reading and modifying the dbf file. The problem is that it is only available in 32-bit (there is no version in 64-bit) and now I need it to be in 64-bit so I decided to use Advantage OLE DB provider that is available in 64-bit and as far as I know it does the same as VFPOLEDB.

What am I doing wrong?

UPDATE 2020/11/16: If I add some parameters to connection string:

string conString = @"Provider=Advantage OLE DB Provider;Data Source=" + dbfFilePath + ";ServerType=ADS_LOCAL_SERVER;TableType=ADS_VFP;Extended Properties=dBASE IV;";

Then when opening connection I get below exception:

Error 7077: The Advantage Data Dictionary cannot be opened. axServerConnect AdsConnect

UPDATE 2020/11/20:

var dbfFilePath =@"C:\MyApp\Temp"; // using c:\MyApp\Temp\myTable.dbf does not work (below open command fails)

string conString = @"Provider=Advantage OLE DB Provider;Data Source=" + dbfFilePath + ";ServerType=ADS_LOCAL_SERVER; TableType=ADS_VFP;";

using (dBaseConnection = new OleDbConnection(conString))
{
   dBaseConnection.Open();
   OleDbCommand insertCommand = dBaseConnection.CreateCommand();
   insertCommand.CommandText = "INSERT INTO [myTable] VALUES (2,100)";
   insertCommand.ExecuteNonQuery();
}

Note: [myTable] has the same name that the dbf file within C:\MyApp\Temp.

Now open command works but when performing insertCommand.ExecuteNonQuery() it gets stuck (it does nothing).

UPDATE 2020/11/27:

Ok,I think I have detected what is happening. It works ok when using Advantage OLE DB provider in 32-bit, however, using Advantage OLE DB provider in 64-bit is not working. In both cases I use it on Windows Server 2012 R2 Standard 64-Bit and Advantage OLE DB provider is version 11.10.

I have checked this using LINQPad 5, and it works but when performing

insertCommand.ExecuteNonQuery()

... and before doing the insert to the dbf file below warning modal window appears waiting for you to click on 'Accept' button. Once you click on the button, insert is done in dbf file correctly.

So, I guess that when running my web application (ASP.NET MVC app) in production environment this warning modal windows does not appear but in fact, it is waiting for you to click on the button to proceed inserting data in the dbf file but as this warning window is not visible (it is not shown) I can click on that button and consequently ExecuteNonQuery never ends (it stalls) and it stays waiting for you to click that button indefinitely.

How can I solve this error? can I modify ads.ini in some way in order to avoid this waring message to appear so application can work?

Answer 1

I see you removed the VFP tag which I think most relevant to this question :)

I again tested that with these codes as a sample and it worked without a glitch:

void Main()
{
    string dbfFilesPath = @"C:\PROGRAM FILES (X86)\MICROSOFT VISUAL FOXPRO 9\SAMPLES\Data";
    string conString = $@"Provider=Advantage OLE DB Provider;Data Source={dbfFilesPath};ServerType=ADS_LOCAL_SERVER;TableType=ADS_VFP;";
    DataTable t = new DataTable();
    using (OleDbConnection cn = new OleDbConnection(conString))
    using (OleDbCommand cmd = new OleDbCommand($@"insert into Customer 
        (cust_id, company, contact)
        values
        (?,?,?)", cn))
    {
        cmd.Parameters.Add("@cId", OleDbType.VarChar);
        cmd.Parameters.Add("@company", OleDbType.VarChar);
        cmd.Parameters.Add("@contact", OleDbType.VarChar);

        cn.Open();
        for (int i = 0; i < 10; i++)
        {
            cmd.Parameters["@cId"].Value = $"XYZ#{i}";
            cmd.Parameters["@company"].Value = $"Company XYZ#{i}";
            cmd.Parameters["@contact"].Value = $"Contact XYZ#{i}";
            cmd.ExecuteNonQuery();
        }

        t.Load(new OleDbCommand($"select * from Customer order by cust_id desc", cn).ExecuteReader());
        cn.Close();
    }
    t.Dump(); // tested in LinqPad AnyCPU version
}

Here is the partial result I got:

XYZ#9  Company XYZ#9                            Contact XYZ#9                                                                                                                                                                                                                           0.0000 
XYZ#8  Company XYZ#8                            Contact XYZ#8                                                                                                                                                                                                                           0.0000 
XYZ#7  Company XYZ#7                            Contact XYZ#7                                                                                                                                                                                                                           0.0000 
XYZ#6  Company XYZ#6                            Contact XYZ#6                                                                                                                                                                                                                           0.0000 
XYZ#5  Company XYZ#5                            Contact XYZ#5                                                                                                                                                                                                                           0.0000 
XYZ#4  Company XYZ#4                            Contact XYZ#4                                                                                                                                                                                                                           0.0000 
XYZ#3  Company XYZ#3                            Contact XYZ#3                                                                                                                                                                                                                           0.0000 
XYZ#2  Company XYZ#2                            Contact XYZ#2                                                                                                                                                                                                                           0.0000 
XYZ#1  Company XYZ#1                            Contact XYZ#1                                                                                                                                                                                                                           0.0000 
XYZ#0  Company XYZ#0                            Contact XYZ#0                                                                                                                                                                                                                           0.0000 
XXXXXX Linked Server Company                                                                                                                                                                                                                                                            0.0000 
WOLZA  Wolski  Zajazd                           Zbyszek Piestrzeniewicz        Owner                          ul. Filtrowa 68                                              Warszawa                        01-012     Poland          (26) 642-7012            (26) 642-7012            3694 
WINCA  Wenna Wines                              Vladimir Yakovski              Owner                                                                                                                                                                                                    0.0000 
WILMK  Wilman Kala                              Matti Karttunen                Owner/Marketing Assistant      Keskuskatu 45                                                Helsinki                        21240      Finland         90-224 8858              90-224 8858              4400 
WHITC  White Clover Markets                     Karl Jablonski                 Owner                          305 - 14th Ave. S., Suite 3B                                 Seattle         WA              98128      USA             (206) 555-4112           (206) 555-4115           38900 
WELLI  Wellington Importadora                   Paula Parente                  Sales Manager                  Rua do Mercado, 12                                           Resende         SP              08737-363  Brazil          (14) 555-8122                                     3600 
WARTH  Wartian Herkku                           Pirkko Koskitalo               Accounting Manager             Torikatu 38                                                  Oulu                            90110      Finland         981-443655               981-443655               24200 

As it is not working for you, I think it might have to do with:

• Access rights. You say, you use with ASP.Net MVC, I wonder if the 'connecting account' has only read access? In IIS, basic settings as I remember there were a setting for connect as. You might at least set it to connect by an account that has full rights to that directory.
• Sharing. The file might be in use shared elsewhere and for some reason your insert is waiting trying to get lock?
• SET NULL ON ? Another slight possibility. You might need to execute this first, on the same connection. If there are fields that you are not supplying a value in insert but "not null" in table structure would otherwise cause it to fail.

You might start testing the same file from say within LinqPad running with administrator rights to eliminate the access rights stuff alltogether (if data directory is under program files or program files (x86), then it is a problem by itself.

I would expect an immediate error message, but who knows maybe driver is waiting for a timeout in case of write access failure?

Some ideas (the way I do it:) Instead of trying to use VFP data with 64 bits access, you might create a server that runs in 32 bits IIS Application pool (or use its own web serving) and handles the data access via REST API (or WCF). I use 32 bits ASP.Net MVC application(s) since years with success using VFOLEDB itself. If you think of REST API path, you might either use ASP.Net core (which is fast unlike the pre core) or use something else, say Go for building it. Go and Iris framework, for an example is an excellent fit to build a REST API server for your data over night (unlikely you would think Go, but if you do, remember to compile with x86 architecture).