Home > Uncategorized > How to fix “Unable to convert MySQL date/time value to System.DateTime” exception when using MySQL .NET Connector

How to fix “Unable to convert MySQL date/time value to System.DateTime” exception when using MySQL .NET Connector

Recently I exported a MySQL database (from the opensource osCommerce e-shop PHP-based platform) using phpMyAdmin (the one included in Plesk Control Panel) and then imported it to another instance of osCommerce, using again phpMyAdmin, this time running in an OpenSolaris WebStack Virtual Machine (hosted in VirtualBox).

However, it inserted zeroes (0) as date values (which is by default an invalid value for a date) instead of null. This resulted to both the Visual Studio database explorer’s table view and my application itself throwing “Unable to convert MySQL date/time value to System.DateTime” exception when using that database via MySQL Connector for .NET.

One can either fix those dates from phpMyAdmin or use a connection string that converts zero (invalid) DateTime to the minimum DateTime value (DateTime.MinValue).

This can be set at “Server Explorer” pane, right click the wanted MySQL connection and press “Modify Connection…”, then press “Advanced…”. Set “Convert Zero Datetime” and press OK. Then you can copy the resulting connection string from the text field at the dialog’s bottom and go from the Solution Explorer to Project Settings, select the "Settings" tab there and replace the previous value of the connection string that your application is using.

There’s a good change you haven’t placed yourself that application setting entry there, but a Visual Studio designer may have done this for you already and be using it in autogenerated code (you can see the autogenerated files by toggling the "Show all Files" button at the Solution Explorer pane). So make sure you don’t rename that setting entry and just replace its value or edit it appropriately (for example you may not want to have authentication values hardcoded in that string as the value copied from the dialog depicted below may have).

image

Note that at the "Advanced Properties" dialog you can right click a row and select remove to make it not show at the connection string and use the connector’s default value instead (e.g. can do this at "Allow Zero Datetime" above to make it not show as bold anymore).

Advertisements
Categories: Uncategorized Tags:
  1. Mac
    2011/08/08 at 16:18

    I Solve my problem.

    Thanks,
    Mac

  2. Freddie Fabregas
    2012/06/08 at 07:31

    Thanks it helped me! 🙂

  3. 2013/02/18 at 06:09

    Thanks!
    for those who are using mysql connection as a string
    do this as an example:

    Dim dbconn As New MySqlConnection
    dbconn.ConnectionString = String.Format(“server=dbHost; user=dbUser; password=dbPass; database=dbName; pooling = false;convert zero datetime=True”)

  4. 2014/09/04 at 00:40

    Hi , Your article is helpful but i am still not able to fix my problem, i have tried the fix here and other fixes but to no success.

    I am using ASP.Net MVC Architecture in Visual studio 2013 , coding in VB and using a MySql Database , Entity Framework 6 data model. I have installed and referenced MySQL Connector/Net (Latest Version) as well as MySQL For VS msi, mysql.data.entity.

    I get this error when i click on my’Project’ Table to display while the application is debugging and this is the error i get:

    “An exception of type ‘MySql.Data.Types.MySqlConversionException’ occurred in mscorlib.dll but was not handled in user code

    Additional information: Unable to convert MySQL date/time value to System.DateTime”

    and the code seems to be breaking at this point of code;
    (This is in the ProjectController.vb class)
    Public Function Index() As ActionResult
    Return View(db.project.ToList())
    End Function

    Please Help

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: