MySQL Bugs: #9619: Cannot update row using DbDataAdapter when row contains an invalid date

Bug #9619	Cannot update row using DbDataAdapter when row contains an invalid date
Submitted:	4 Apr 2005 18:20	Modified:	29 Oct 2006 4:28
Reporter:	David Christian	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.04	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When a row has an invalid date, i.e. '0000-00-00', I am unable to post a change to any column in the row through the DataAdapter.  I   I receive the following exception:

MySql.Data.Types.MySqlConversionException
Unable to convert MySQL date/time value to System.DateTime

How to repeat:
Use the MySqlDataAdapter to post an update on an existing row that contains a date column with an invalid date '0000-00-00'.

I have almost the same problem except this problem is for ALL the dates, not only the wrong one.

I have a column in my table which name is "date_insertion" and which type is "date"

i try to fill my dataset with :

strSQL = "SELECT *" & _ 
" FROM inserer, utilisateur, element" & _ 
" WHERE date_insertion >= (curdate() - INTERVAL 1 day)" & _ 
" AND inserer.id_user = utilisateur.id_user" & _ 
" AND element.id_element = inserer.id_element" & _ 
" ORDER BY date_insertion" 

Dim dadate As New MySqlDataAdapter(strSQL, objConn) 
Dim objDS As New DataSet 
Console.WriteLine(strSQL) 
dadate.Fill(objDS, "temp") ' <-------------------------- HERE IS THE ERROR ! 

I get the following error for any table with a date/time field

An unhandled exception of type 'MySql.Data.Types.MySqlConversionException' occurred in system.data.dll

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

in fact, there's only a problem when trying to access an field in which there is an invalid date like '0000-00-00'.

Tested on Win 2000 Sp4,  Microsoft Visual Basic .NET, Connector .NET 1.0.4
Test case is attached.

Tested case

Attachment: 9619.zip (application/x-zip-compressed, text), 6.19 KiB.

I found here the solution:

http://mdid.org/mdidwiki/index.php?title=Unable_to_convert_MySQL_date/time_value_to_System...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12579

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12580

Fixed in 1.0.8 and 5.0.1

A note has been added to the 1.0.8 and 5.0.1 changelogs.