C# VFP OLEDB query throwing "Command contains unrecognized phrase/keyword"

Question

I have a C# application intended to update one field in a table based on a value found in another table. Using the following:

listComm.CommandText = "update [c-disc] inner join [c-info] " +
 "on [c-info].keys = [c-disc].cd_key set [c-disc].cd_distric = ? " +
 "where [c-disc].cd_tax = ? and [c-info].ci_region = ?";

and in a foreach loop below it:

string region = line.Substring(0, 4).PadRight(14);
string taxable = "Y";
string district = line.Substring(5, 4).PadLeft(4);
listComm.Parameters.Add(new OleDbParameter("?", district));
listComm.Parameters.Add(new OleDbParameter("?", taxable));
listComm.Parameters.Add(new OleDbParameter("?", region));

try {
    listComm.ExecuteNonQuery();
    listComm.Parameters.Clear();
} catch (Exception x) {
    setStatusText("fatal error: " + x.Message.ToString();
}

I'm getting "Command contains unrecognized phrase/keyword". Using the same query in MS Access works fine when I plug the appropriate values in place of the '?' placeholders. In Visual Studio, using breakpoints I see everything appears normal - the connection is open and the parameter values are as expected. I have another program that works similarly, but only against a single table. I can't for the life of me figure out what's wrong with this query.

Answer 1

Whoever designed that system, sounds to not have much knowledge about VFP. VFP is not totally ANSI SQL compatible and not only that have some naming rules. Your designer named the tables with dash in them? In documentation there are warnings as I remember. Anyway you can still go with that hoping, cd_key and cd_tax fields are only in 'c-disc' table (otherwise you need a little bit workaround).

using (OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB;Data Source=c:\MyDataFolder"))
{
    var sql = @"update [c-disc] 
set cd_distric = ? 
from [c-info] ci 
WHERE ci.keys = cd_key AND 
    cd_tax = ? and ci.ci_region = ?";

    var listComm = new OleDbCommand(sql, con);

    listComm.Parameters.Add("dst", OleDbType.Char);
    listComm.Parameters.Add("tx", OleDbType.Char);
    listComm.Parameters.Add("reg", OleDbType.Char);

    string taxable = "Y";

    listComm.Parameters["tx"].Value = taxable; // constant?

    con.Open();

    // Loop here
    // {

    // These paddings do not smell good
    string region = line.Substring(0, 4).PadRight(14); 
    string district = line.Substring(5, 4).PadLeft(4);

    listComm.Parameters["dst"].Value = district;
    listComm.Parameters["reg"].Value = region;
    try
    {
        listComm.ExecuteNonQuery();
    }
    catch (Exception x)
    {
        setStatusText("fatal error: " + x.Message.ToString());
    }
    // loop ends here
    // }
    con.Close();
}