Receiving an error when insert data into a .dbf file

Question

Backgournd

I am currently working on a project which will need me to create a local .dbf file which I must then populate with a value. I am currently able to create a .dbf file in a test dir and adding a column to it, however when I later try to add a value to the column, it errors out.

Problem

I am currently not able to write to the column Public in the .dbf file which I created. When the code goes into ExecuteNonQuery();, it an error and is caught in my catch statement.

Working code

public static bool CreateDBF()
{
    try
    {
        string dbfDirectory = @"c:\Users\me\Desktop\New911";
        string connectionString = "Provider=VFPOLEDB;Data Source=" + dbfDirectory;
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();
            OleDbCommand command = connection.CreateCommand();
            command.CommandText = "create table CustomProperties(Public C(60))";
            command.ExecuteNonQuery();
            connection.Close();
            InsertDataIntoDBF(dbfDirectory + "\\CustomProperties.DBF");
        }
        return true;
    }
    catch (Exception ex)
    {
        throw;
    }
}

Working Code - appendix:

The above block of code successfully create the .dbf file with the column which I want as the following image will show

Not working code:

public static bool InsertDataIntoDBF(string path)
{
    try
    {
        string strLogConnectionString = "Provider=VFPOLEDB;Data Source=" + path + ";Collating Sequence=machine;Mode=ReadWrite;";
        string query = @"INSERT INTO CustomProperties (Public)";

        using (OleDbConnection connection = new OleDbConnection(strLogConnectionString))
        {                    
            OleDbCommand command = new OleDbCommand(query, connection);
            command.Parameters.AddWithValue("@Public", "True");

            connection.Open();

            new OleDbCommand("set null off", connection).ExecuteNonQuery();

            command.ExecuteNonQuery();
            connection.Close();
        }

        return true;
    }
    catch (Exception ex)
    {
        throw;
    }
}

Updated code blocks

Working block:

public static bool CreateDBF()
{
  try
  {
    string dbfDirectory = @"c:\Users\er4505\Desktop\New911";
    string connectionString = "Provider=VFPOLEDB;Data Source=" + dbfDirectory;

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
      connection.Open();
      OleDbCommand command = connection.CreateCommand();
      command.CommandText = "create table CustomProperties(Public C(60))";
      command.ExecuteNonQuery();
      connection.Close();
    }

    InsertDataIntoDBF(dbfDirectory + "\\CustomProperties.DBF");
    return true;
  }
  catch (Exception ex)
  {
    string viewError = JsonConvert.SerializeObject(ex);
    return false; << I have a breakpoint here 
  }
}

Non-working block:

public static bool InsertDataIntoDBF(string path)
{
  try
  {
    string strLogConnectionString = "Provider=VFPOLEDB;Data Source=" + path + ";Collating Sequence=machine;Mode=ReadWrite;";
    string query = @"INSERT INTO CustomProperties (Public) VALUES (@Public)";
    using (OleDbConnection connection = new OleDbConnection(strLogConnectionString))
    {                    
      OleDbCommand command = new OleDbCommand(query, connection);
      command.Parameters.AddWithValue("@Public", "True");
      connection.Open();
      command.ExecuteNonQuery();
      connection.Close();
    }
    return true;
  }
  catch (Exception ex)
  {
    string viewError = JsonConvert.SerializeObject(ex);
    return false; << breakpoint here 
  }
}

Error caught:

oledbErrors
Message: Syntax error.
NativeError: 0
Source: Microsoft OLE DB Provider for Visual FoxPro

ClassName: System.Data.OleDb.OleDbException
Message: Syntax error.

Answer 1

The error was purely syntax, and I was guided by a really good example which I found in the following link

https://social.msdn.microsoft.com/Forums/en-US/24eac4c5-3a4d-43f4-8607-ef684919c4af/command-contains-unrecognized-phrasekeyword-vbnet?forum=visualfoxprogeneral

Working Code Blocks

public static bool CreateDBF()
{
  try
  {
    string dbfDirectory = @"c:\Users\er4505\Desktop\New911";
    string connectionString = "Provider=VFPOLEDB;Data Source=" + dbfDirectory;
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
      connection.Open();
      OleDbCommand command = connection.CreateCommand();
      command.CommandText = "create table CustomProperties(Public C(60))";
      command.ExecuteNonQuery();
      connection.Close();
    }
    InsertDataIntoDBF(dbfDirectory + "\\CustomProperties.DBF");
    return true;
  }
  catch (Exception ex)
  {
    string viewError = JsonConvert.SerializeObject(ex);
    return false;
  }
}

public static bool InsertDataIntoDBF(string path)
{
  try
  {
    string strLogConnectionString = "Provider=VFPOLEDB;Data Source=" + path + ";Collating Sequence=machine;Mode=ReadWrite;";
    string query = "INSERT INTO CustomProperties(Public) VALUES (?)";
    using (OleDbConnection connection = new OleDbConnection(strLogConnectionString))
    {
      connection.Open();
      OleDbCommand cmdInit = new OleDbCommand("set null off", connection);
      cmdInit.ExecuteNonQuery();
      OleDbCommand command = new OleDbCommand(query, connection);
      OleDbParameter publicStatus = command.Parameters.Add("Public", OleDbType.Char);
      publicStatus.Value = "True";
      command.ExecuteNonQuery();
      connection.Close();
    }
    return true;
  }
  catch (Exception ex)
  {
     log4net.LogManager.GetLogger("EmailLogger").Error(JsonConvert.SerializeObject(ex));
            string viewError = JsonConvert.SerializeObject(ex);
            return false;
        }
    }