Not able to insert value into a column in a .dbf file

Question

0.00/5 (No votes)

See more:

BackGround

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 add a column to it, however when I try to add a value to that column it is not allowing me to do so. I will show two blocks of code in order to show the entire picture of what I am doing. The first block will be the working code which is a function that creates the .dbf file, the second block is the non working code where I am trying to add a value to the column.

Problem

I am currently not able to write to the column Public in the dbf file which I create. When it goes into ExecuteNonQuery(); it errors out and is caught in my catch statement.

Working Code

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 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) 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 received

{"oledbErrors":[{"Message":"Syntax error.","NativeError":0,"Source":"Microsoft OLE DB Provider for Visual FoxPro","SQLState":""}],"ClassName":"System.Data.OleDb.OleDbException","Message":"Syntax error.","Data":null,"InnerException":null,"HelpURL":null,"StackTraceString":"   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()\r\n   at CasewareIntegrationWebApi.Services.DatabaseServices.InsertDataIntoDBF(String path) in C:\\Users\\er4505\\Desktop\\Engagement Retival Util\\CasewareIntegrationWebApi\\CasewareIntegrationWebApi\\Services\\DatabaseServices.cs:line 88","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":"8\nExecuteCommandTextErrorHandling\nSystem.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Data.OleDb.OleDbCommand\nVoid ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)","HResult":-2147217900,"Source":"Microsoft OLE DB Provider for Visual FoxPro","WatsonBuckets":null}

What I have tried:

I have tried to various examples which I have looked at online and none have seemed to work.

Posted 24-Mar-17 3:08am

erick manuel

Updated 24-Mar-17 5:04am

v3

Add a Solution

Comments

[no name] 24-Mar-17 9:23am

You simply must find out what the exception is being thrown.

erick manuel 24-Mar-17 9:41am

yea, I just posted the error which I am receiving, seems like it is a syntax error however I can find the syntax error which it does not like.

[no name] 24-Mar-17 9:44am

Well there is only one place it could be
string query = @"INSERT INTO CustomProperties (Public) VALUES (@Public)";
should probably be
string query = @"INSERT INTO CustomProperties (Public) VALUES (?)";

erick manuel 24-Mar-17 11:05am

Yea you where right, I found a good example and posted the answer. Thank you

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

erick manuel · Accepted Answer · 2017-03-24T05:04:00

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

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