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.

You are missing a part in the INSERT INTO statement. It should be _INSERT INTO CustomProperties (Public) VALUES (@Public)_ — Steve, Mar 23 '17 at 21:14
calling InsertDataIntoDBF() should be autside of the using block. You open connection two times- it may couses error — daniell89, Mar 23 '17 at 21:33
change 'throw' to 'throw ex' or use only 'catch(exception)' and then only 'throw'. Then you should see the error message — daniell89, Mar 23 '17 at 21:37
Your are right about opening the connection twice, thank you for the heads up. However, the exception is. not coming in at all, i usually use log 4 net and it also shows nothing. — EasyE, Mar 23 '17 at 21:40
@Steve Thank you for the response to my question, I tried your code but it stills errors out. — EasyE, Mar 24 '17 at 13:20
What do you mean with errors out? Do you have now an error message? If you hit the exception the ex.Message will tell you something about the error and sometime also ex.InnerException.Message contains useful informations — Steve, Mar 24 '17 at 13:27
Hey @Daniel, finally was able to read the error which it is giving me. I updated the question which now has the error which I am getting when I am running ExecuteNonQuery(); — EasyE, Mar 24 '17 at 13:33

daniell89 · Answer 1 · 2017-03-24T14:17:29.880

This works for me. Try it

    static void Main(string[] args)
    {
        try
        {
            CreateDBF();
            Console.WriteLine(ReadDB());
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

    private static string dbfDirectory = @"c:\Test\dbTest";
    private static string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbfDirectory + ";Extended Properties = dBase IV";
    public static bool CreateDBF()
    {            
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();
            OleDbCommand command = connection.CreateCommand();
            command.CommandText = "Create Table CustomProperties ([Public] char(50))";
            command.ExecuteNonQuery();
            connection.Close();
        }
        InsertDataIntoDBF(dbfDirectory + "\\CustomProperties.DBF");
        return true;
    }

    public static bool InsertDataIntoDBF(string path)
    {
        string query = @"INSERT INTO CustomProperties ([Public]) VALUES (@Public)";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand(query, connection);
            command.Parameters.AddWithValue("@Public", "True");
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
        return true;
    }

    public static string ReadDB()
    {
        string res = string.Empty;
        string query = @"SELECT * FROM CustomProperties";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand(query, connection);
            command.Parameters.AddWithValue("@Public", "True");
            connection.Open();
            res = (string)command.ExecuteScalar();
            connection.Close();
        }
        return res;
    }

Nice I like the search block at the end. – EasyE Mar 24 '17 at 18:48 — EasyE, Mar 24 '17 at 18:48

score 0 · Accepted Answer · answered Mar 24 '17 at 14:22

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;
        }
    }

daniell89 · Answer 3 · 2017-03-23T22:09:43.793

-1

public static bool CreateDBF()
{
   ...
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            ...                
        }
        InsertDataIntoDBF(dbfDirectory + "\\CustomProperties.DBF"); // change 1
        return true;
    }
    catch (Exception)
    {
        throw; //change2
    }
}

public static bool InsertDataIntoDBF(string path)
{
    try
    {
        ...       
    }
    catch (Exception)
    {
        throw; //change 3
    }
}

edited Mar 23 '17 at 22:09

answered Mar 23 '17 at 21:39

daniell89

1,832
16
28

1

Do not _throw ex_ http://stackoverflow.com/questions/730250/is-there-a-difference-between-throw-and-throw-ex – Steve Mar 23 '17 at 22:07
you're right, in this case better is not to reset the call stack. But if it would be a library for outside users then reset call stack is recommend. btw Thanks for comment. – daniell89 Mar 23 '17 at 22:09

Receiving an error when insert data into a .dbf file

3 Answers3