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: | |
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 |
[3 Feb 2007 21:06]
Chang-Ping Hsiao
[3 Feb 2007 21:06]
Chang-Ping Hsiao
Error message
Attachment: capture1.GIF (image/gif, text), 8.73 KiB.
[3 Feb 2007 21:07]
Chang-Ping Hsiao
Error message
Attachment: capture2.GIF (image/gif, text), 6.59 KiB.
[12 Mar 2007 18:32]
Tonci Grgin
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
[19 Mar 2007 6:30]
nirav jariwala
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.
[12 Apr 2007 23:00]
Bugs System
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".
[14 Apr 2007 10:05]
Tonci Grgin
Well, everybody's having problems but I don't see the info requested is posted. Changing to "Can't repeat".
[14 May 2007 17:03]
Wolfgang Schulze-Zachau
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
[14 May 2007 17:05]
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
[14 May 2007 19:53]
Tonci Grgin
Wolfgang thanks, at least some info I can work with...
[15 May 2007 8:45]
Tonci Grgin
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
[15 May 2007 8:47]
Tonci Grgin
Wolfgang's table opened for browsing / editing
Attachment: 26054-1.jpg (image/jpeg, text), 16.31 KiB.
[15 May 2007 8:47]
Tonci Grgin
Setting advanced connection properties
Attachment: 26054-2.jpg (image/jpeg, text), 25.12 KiB.
[5 Jul 2013 21:45]
Giuseppe Crescimanno
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.
[5 Jul 2013 21:45]
Giuseppe Crescimanno
Unable to convert MySQL date/time value to System.DateTime
Attachment: 05-07-2013 23-42-01.jpg (image/jpeg, text), 128.20 KiB.
[4 Sep 2014 9:51]
Barry Cummins
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
[4 Sep 2014 10:23]
Barry Cummins
However, adding "Convert Zero Datetime=True" to the connection string seems to get over this hurdle. Barry