No table is open in the current work area

问题:

While trying to create CDX file on existing DBF file using below code, I'm getting an exception saying "No table is open in the current work area".

Language - C#

Exception Details -

Message - No table is open in the current work area.
Source - Microsoft OLE DB Provider for Visual FoxPro

Below is code snippet:

     public void CreateIndex()
    {
        var cdxExpressions = new List<CDXExpression> {
            new CDXExpression { expression = "ENTRY_DATE", name = "cdx_p1"},
            new CDXExpression { expression = "ENTRY_CODE", name = "cdx_p2"},
            new CDXExpression { expression = "Month", name = "cdx_p3"},
            new CDXExpression { expression = "Year", name = "cdx_p4"},
            new CDXExpression { expression = "Company", name = "cdx_p5"}
        };

        string dbfFile = ConfigurationManager.AppSettings["DBFFileLocation"];
        string connectionString = @"Provider=VFPOLEDB.1;Data Source=" + dbfFile + ";Extended Properties=dBASE IV;";
        OleDbConnection connection = new OleDbConnection(connectionString);

        var queries = DBFQuery.CreateCDXQueries(cdxExpressions, dbfFile);

        connection.Open();
        try
        {
            foreach (var qry in queries)
            {
                OleDbParameter script = new OleDbParameter("script", qry);
                OleDbCommand dbfCommand = connection.CreateCommand();
                dbfCommand.CommandType = CommandType.StoredProcedure;
                dbfCommand.CommandText = "ExecScript";
                //dbfCommand.Connection.Open();
                dbfCommand.Parameters.Add(script);
                dbfCommand.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            // I should Open the DBF/Table in the current work area to avoid the exception
        }
        connection.Close();
    }

How to open DBF file in c# to avoid exception "No table is open in the current work area", also using Microsoft OLE DB Provider for Visual FoxPro

回答1:

This one is with your CDXExpression list:

public void CreateIndex()
{
    var cdxExpressions = new List<CDXExpression> {
            new CDXExpression { expression = "ENTRY_DATE", name = "cdx_p1"},
            new CDXExpression { expression = "ENTRY_CODE", name = "cdx_p2"},
            new CDXExpression { expression = "Month", name = "cdx_p3"},
            new CDXExpression { expression = "Year", name = "cdx_p4"},
            new CDXExpression { expression = "Company", name = "cdx_p5"}
        };

    var myCode = "use sample exclusive\n" +
          string.Join("\n", cdxExpressions.Select(e => $"index on {e.expression} tag {e.name}")) +
          "\nuse";

    string dbfFile = ConfigurationManager.AppSettings["DBFFileLocation"];
    string connectionString = @"Provider=VFPOLEDB;Data Source=" + dbfFile;

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        OleDbCommand cmd = connection.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ExecScript";
        OleDbParameter parm = cmd.CreateParameter();
        parm.OleDbType = OleDbType.Char;
        cmd.Parameters.Add(parm);
        parm.Value = myCode;
        try
        {
            connection.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            // ...
        }
    }
}

回答2:

You are hiding the most important part, the code of CreateCDXQueries. Somewhere before attempting to create the index you should have opened the table in the current work area as exclusive. Also, remove that Extended Properties part from connection string. Here is a very basic example. Suppose you have already have a table created with this command (or create within the same Execscript call):

Create Table ('d:\temp\IndexTest') free (id int, firstName c(10), lastName c(10))

This is C# code to create index tags:

static void Main()
{
    string myCode =
    @"use indexTest exclusive
index on firstName tag firstName
index on lastName tag LastName
index on id tag id
use
";

    string strCon = @"Provider=VFPOLEDB;Data Source=d:\temp";
    OleDbConnection con = new OleDbConnection(strCon);
    OleDbCommand cmd = con.CreateCommand();

    con.Open();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "ExecScript";
    OleDbParameter parm = cmd.CreateParameter();
    parm.OleDbType = OleDbType.Char;
    cmd.Parameters.Add(parm);

    parm.Value = myCode;

    cmd.ExecuteNonQuery();
    con.Close();
}

There is no try...catch in code as it was a quick sample.