Search code examples
c#datetimevisual-foxpro

Insert empty DateTime from C# into FoxPro

I am trying to insert an empty DateTime into a FoxPro database using DbParameter in C#. Our application marries FoxPro data along with SQL Server and .NET models/data.

My current issue is that most of our DateTime types in C# are not nullable, nor should they be. However, most of our legacy data in FoxPro are empty dates (shown as ' / / : : '). I am trying to do an insert into the FoxPro table where I do a check to see if the .NET DateTime meets certain criteria, then inserts an empty date. This last part has proven to be a nightmare.

What I have tried so far:

.Parameters.Add(string.Empty, new DateTime())

The above understandably inserts 01/01/0001 but is not what we want.

.Parameters.Add(string.Empty, "{}")
.Parameters.Add(string.Empty, "{:://}")
.Parameters.Add(string.Empty, "{//}")
.Parameters.Add(string.Empty, "'{}'")

The above all result in

System.Data.OleDb.OleDbException: 'Data type mismatch'.

which makes sense because I'm trying to send a string to a field with DateTime type.

Does anyone know how to insert an empty DateTime into FoxPro?

Solution

  • If I'm understanding you correctly, you're not using a DbParameter, but rather an OleDbParameter, and you're adding them through the OleDbParameterCollection.Add method?

    If so, consider that you are using the overload that is .Add(String, Object), and you could instead use the overload that is .Add(String, OleDbType):

    .Parameters.Add(string.Empty, OleDbType.Date)

    The default value of an OleDbParameter is null, so you don't need to do anything more for your empty dates.

    Also, depending on how the column is defined in your FoxPro database schema, it may be appropriate to pass OleDbType.Date, OleDbType.DBDate, OleDbType.DBTime, or OleDbType.DBTimeStamp. The full list of OleDB types is documented here, but I'm not entirely certain how they align to FoxPro's data types.