MySQL Bugs: #26054: Unable to convert MySQL date/time value to System.DateTime using Microsoft Visua

Bug #26054	Unable to convert MySQL date/time value to System.DateTime using Microsoft Visua
Submitted:	3 Feb 2007 21:06	Modified:	15 May 2007 8:45
Reporter:	Chang-Ping Hsiao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Visual Studio Plugin	Severity:	S3 (Non-critical)
Version:	1.0.2 and 1.1	OS:	Windows (Windows XP Professional)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
In Microsoft Visual Studio 2005 Professional Edition, when I select Tools -> Connect to Database..., I am able to connect and see table information of a database, but when I try to "Browse or Edit Data", using both 1.0.2 and 1.1 (alpha), I get the following two error messages.

Unable to convert MySQL date/time value to System.DateTime.
Failed to reload document data! Document will be closed.

There are two columns designed as "date" and "datetime".  If there is no data for it, the default values 0000-00-00 and 0000-00-00 00:00:00 are used.  I don't know what happened.  This only happens in one database (schema); "Browse and Edit Data" for another table with datatime column on the same MySQL server does not have any problem.

How to repeat:
In Microsoft Visual Studio 2005 Professtional Edition
1. Select "Tools" menu
2. Select "Connect to database..."
3. Enter Server name, User name, password and Database name then click OK button
4. Double click on the data connection
5. Double click on Tables
6. Right mouse click on the table interested
7. Select :Browse and Edit Data"
8. MessageBoxes pop up.

Error message

Attachment: capture1.GIF (image/gif, text), 8.73 KiB.

Error message

Attachment: capture2.GIF (image/gif, text), 6.59 KiB.

Hi.

This report lacks basic info needed for verification:
 - MySQL server version
 - c/NET version
 - NET FW version
 - Is table in question checked for errors? If not, please do check it.
 - What is *exact* table structure that leads to a crash every time you tried above steps?

So far, I am unable to repeat this problem:
 - MySQL 5.0.38BK on WinXP Pro SP2 localhost
 - VS 2005 Pro
 - c/NET 5.0.3GA
 - Net FW 2.0
 - MySQL VS plugin 1.1.2-alpha

I am also having the same problem.
Any help will be really appriciated. I am having expiry date field in MySQL database and datagrid is posing this error if expiry date is blank.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Well, everybody's having problems but I don't see the info requested is posted.
Changing to "Can't repeat".

Samemproblem here.
Here are the details:

MySQL Version 4.1.11 (debian sarge 7)
Table definition:

CREATE TABLE `Shipment` (
  `id` int(11) NOT NULL auto_increment,
  `order_id` varchar(10) NOT NULL default '',
  `delivery_code` varchar(30) NOT NULL default '',
  `weight` double NOT NULL default '0',
  `weigh_date_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `batch_id` int(10) unsigned NOT NULL default '0',
  `cancelled` tinyint(1) unsigned NOT NULL default '0',
  `cancelled_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `orderid` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_ORDER` (`order_id`),
  KEY `IDX_BATCH` (`batch_id`),
  KEY `orderid` (`orderid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then insert a row with:
insert into Shipment (id, order_id, orderid, delivery_code, batch_id) values (5, "AR500000", 500000, "GB12345678SD", 50);

then use the MySQL Connector/.Net and try and load a datatable with the statement "SELECT * FROM Shipment" and you will get the reported error message.
The ONLY way to get around the problem is to set the columns in the database to NULL and remove any existing default stamps with zeros. 
There are cases where this is not desirable or possible. In those cases the only other option are some really ugly type casts in the query (cast to string and then handle the value as a string in the code).

Any more details required, please ask and I will provide.

From my perspective this is a problem in the Connector/.NET. For date/time values of '0000-00-00' or similar it should return a DBNULL value to the DataTable in .NET, otherwise the Date/Time types in .Net come up with some funny values of their own (e.g. Date = 01/01/0001 etc.)

regards
Wolfgang Schulze-Zachau

Ahem, some more information:

.NET Framework 1.1
Connector/.Net 1.0.7

Table has been checked for errors (this is our production database).

regards
Wolfgang

Wolfgang thanks, at least some info I can work with...

Hi all. This is not a bug but expected behavior. Please check manual under connect options and set "Allow Zero Datetime" to true, as on attached pictures, and the error will go away. I used Wolfgang's table.

Environment:
 - MySQL server 4.1.23BK on Suse 10.0 host
 - WinXP Pro SP2 client with NET FW 2.0, c/NET 1.0.9
 - MS VS2005Pro

Wolfgang's table opened for browsing / editing

Attachment: 26054-1.jpg (image/jpeg, text), 16.31 KiB.

Setting advanced connection properties

Attachment: 26054-2.jpg (image/jpeg, text), 25.12 KiB.

Sorry if this Comes 6 years too late... :)

Well I did as advised but am still running into the Exception.

Using MySql.Data 6.6.5 / MySql.Data.Entity 6.6.5 with Entity Framework 5 on .Net 4.5

Check out the Picture, thanks.

Unable to convert MySQL date/time value to System.DateTime

Attachment: 05-07-2013 23-42-01.jpg (image/jpeg, text), 128.20 KiB.

Just to clarify a couple of things with this and I know its an old reported bug but I did find it useful when searching Google.

.NET 4.0 / 4.5 : MySQL 6.1

I was able to resolve this particular issue by setting the connection string property "Allow Zero Datetime = True".

However, this now introduces a new problem.  If you try serialise the data table returned by a query, it falls over.  Stating that there is an invalid data type (MySQL Date Time type).

See other bug #52141
http://bugs.mysql.com/bug.php?id=52141

So each resolution is contradictory to each other.  I would suggest this be resubmitted as a bug.

Regards
Barry

However, adding "Convert Zero Datetime=True" to the connection string seems to get over this hurdle.

Barry