Search code examples
c#exceldatedatetimevisual-foxpro

Convert a datetime() record to date in c# for importation into VFP database

I'm currently building a data export/import tool for pulling data into a Visual Fox Pro database from an excel or CSV document.

I believe the code to be functional, however upon execution I recieve a data type mismatch error.

After some investigation I've notice a difference between the format of the dates I'm pulling and the field I'm pushing to.

The Fox pro database is set up to take Date records, however the data i'm trying to push is in date time format (the original record is date) but as far as I'm aware c# can only natively do datetime conversion.

The code getting the date from excel is as such:

importCommand.Parameters["TENSDATE"].Value = exportReader.IsDBNull(0)
     ? (object) DBNull.Value
     : DateTime.Parse(exportReader.GetValue(0).ToString());

Now, I've seen a lot of people use something like:

exportReader.GetValue(0).ToString("dd/MM/yyyy")

However I can't seem to get this functioning. Can someone advise me on the best way to achieve my goal.

Solution

  • You need to supply the type of the field when adding it to parameters. In this specific case, OdbcType.DateTime for a date field.

    importCommand.Parameters.Add("@TENSDATE", OdbcType.DateTime).Value = exportReader.IsDBNull(0)
     ? (object) DBNull.Value
     : DateTime.Parse(exportReader.GetValue(0).ToString());