How do I set DBF column value to NULL (blank) when using VFPOLEDB and C#?

Leo Ayala 21 Reputation points
2021-12-23T23:55:06.11+00:00

I have created a WPF application that is to maintain existing DBF files. These files are also used by another legacy application so I cannot migrate the data to SQL server or other more current DB. Some of the DBF tables have columns whose values are not that important anymore. When inserting a new row, I was having a problem setting those values to NULL. I searched and found that by executing a command with SET NULL OFF then the INSERT command omitting the columns I did not care about, then their values would be set to blanks. This worked for the INSERT commands. The problem I'm having now is with the UPDATE commands. In an update command, I create an OleDbParameter for each column that's going to be updated. Some of the parameter values need to be set to NULL. When setting a NULL value, I set it to DBNull.Value. When I execute the command, I get an error. For example: "Field DATE does not accept null values."

Is it possible to set the value to blanks as in the INSERT command when the column was omitted? Below is a code example:

using (var connection = new OleDbConnection(@"Provider=VFPOLEDB.1;DSN=TEST123;"))
   using (var command = connection.CreateCommand())
   {
      connection.Open();

      var cmd = connection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "SET NULL OFF";
      cmd.ExecuteNonQuery();

      command.CommandText = @"UPDATE `customer` SET `date` = ? WHERE (customerid = ?)";
      command.Parameters.Add(new OleDbParameter("date", OleDbType.DBDate, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "date", DataRowVersion.Current, true, null));
      command.Parameters.Add(new OleDbParameter("Original_customeid", OleDbType.Char, 7, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "customerid", DataRowVersion.Original, false, null));

      command.Parameters[0].Value = DBNull.Value;
      command.Parameters[1].Value = "MRST0";

      try
      {
         retVal = command.ExecuteNonQuery();
      }
      catch (Exception e)
      {
         Debug.WriteLine(e.Message);
      }
   }
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,266 questions
{count} votes