RE: Unable to convert MySQL date/time value to System.DateTime <--- at 1.0.3 - Reggie Burnett - com.mysql.lists.dotnet

32 messages in com.mysql.lists.dotnetRE: Unable to convert MySQL date/time...

From	Sent On	Attachments
Jorge Bastos	09 Dec 2004 09:11
Barry Zubel	09 Dec 2004 09:29
Jorge Bastos	09 Dec 2004 11:05
Reggie Burnett	09 Dec 2004 19:25
Jorge Bastos	13 Dec 2004 06:18
Reggie Burnett	13 Dec 2004 06:37
Reggie Burnett	13 Dec 2004 06:40
Jorge Bastos	13 Dec 2004 06:42
Jorge Bastos	13 Dec 2004 06:50
Matej Simikič	13 Dec 2004 07:26
Reggie Burnett	13 Dec 2004 07:28
Matej Simikič	13 Dec 2004 07:35
Reggie Burnett	13 Dec 2004 07:41
Matej Simikič	13 Dec 2004 07:47
Matej Simikič	13 Dec 2004 07:55
Reggie Burnett	13 Dec 2004 07:57
Jordan Sparks	13 Dec 2004 08:12
Reggie Burnett	13 Dec 2004 08:13
Jorge Bastos	13 Dec 2004 08:14
Reggie Burnett	13 Dec 2004 08:14
Reggie Burnett	13 Dec 2004 08:14
Matej Simikič	13 Dec 2004 08:16
Jorge Bastos	13 Dec 2004 08:19
Robert Rowe	13 Dec 2004 08:26
Reggie Burnett	13 Dec 2004 08:44
Reggie Burnett	13 Dec 2004 09:15
Robert Rowe	13 Dec 2004 09:25
Robert Rowe	13 Dec 2004 09:28
SGr...@unimin.com	13 Dec 2004 11:02
Reggie Burnett	13 Dec 2004 11:12
Reggie Burnett	13 Dec 2004 11:21
Robert Rowe	13 Dec 2004 11:41

Subject:	RE: Unable to convert MySQL date/time value to System.DateTime <--- at 1.0.3
From:	Reggie Burnett (reg...@mysql.com)
Date:	12/13/2004 08:44:32 AM
List:	com.mysql.lists.dotnet

If you want to support zero datetime values in the database, you'll need to run with "allow zero datetime=yes" on your connection string.

There is another possibility that I am working on. Let me flesh it out and I'll bring it up on the mailing list.

-reggie

-----Original Message----- From: Matej Simikic [mailto:mate...@gofer.si] Sent: Monday, December 13, 2004 10:16 AM To: 'Reggie Burnett'; dot...@lists.mysql.com Subject: RE: Unable to convert MySQL date/time value to System.DateTime <- -- at 1.0.3

This is a problem then yes. I store 0000-00-00 to database if user doesn't enter any date on a form. If i change it to NULL it would work?

Main problem is that this is a huge project with huge database (200+ tables) and it is almost impossible to change whole database to store NULL instead of 0000-00-00. And then also correct whole project to check for NULL whenever i retrieve date from database.

We were using MyODBC when we started with the project. There was no need for empty dates in database to be NULL.

Yes, any zero value for year, month, or day is illegal for .NET

-----Original Message----- From: Matej Simikic [mailto:mate...@gofer.si] Sent: Monday, December 13, 2004 9:56 AM To: 'Reggie Burnett'; dot...@lists.mysql.com Subject: RE: Unable to convert MySQL date/time value to System.DateTime <- -- at 1.0.3

Hm... i don't have any illegal dates in my database because i check them before they are entered in database. So i shouldn't get that error but i got it.

Is "empty" date 0000-00-00 also illegal?

Matej Simikič Gofer d.o.o.

-----Original Message----- From: Reggie Burnett [mailto:reg...@mysql.com] Sent: Monday, December 13, 2004 4:42 PM To: mate...@gofer.si; dot...@lists.mysql.com Subject: RE: Unable to convert MySQL date/time value to System.DateTime <-

-- at 1.0.3

Matej

MySQL supports datetime values that are illegal in .NET. By default, the driver attempts to convert them all to DateTime values. When these illegal values are discovered, an exception is thrown. It is suggested that no one use illegal date time values in a database. If you are dealing with a database that has these values, you can add "allow zero datetime=yes" to your connection string. This will cause the reader to return a MySqlDateTime object instead of a DateTime object. Now, this will have an effect on your webforms and winforms binding code and, in general, using a MySqlDateTime object is less convenient. This is why it is *STRONGLY* suggested that you not use illegal datetime values in your databases.

-reggie

-----Original Message----- From: Matej Simikic [mailto:mate...@gofer.si] Sent: Monday, December 13, 2004 9:36 AM To: 'Reggie Burnett'; dot...@lists.mysql.com Subject: RE: Unable to convert MySQL date/time value to

System.DateTime

-- at 1.0.3

Yes i tried using new 1.0.3 No i haven't put "allow zero datetime" at connection string... Should i put it? Maybe this is the cause?

What is typed dataset?

Matej Simikič Gofer d.o.o.

-----Original Message----- From: Reggie Burnett [mailto:reg...@mysql.com] Sent: Monday, December 13, 2004 4:29 PM To: mate...@gofer.si; dot...@lists.mysql.com Subject: RE: Unable to convert MySQL date/time value to

System.DateTime

-- at 1.0.3

Are you saying that you are using the new 1.0.3 download for this? Do you have any value for "allow zero datetime" set on your connection string? Are you using a typed dataset?

-reggie

-----Original Message----- From: Matej Simikic [mailto:mate...@gofer.si] Sent: Monday, December 13, 2004 9:27 AM To: dot...@lists.mysql.com Subject: Unable to convert MySQL date/time value to System.DateTime

at 1.0.3

Using code:

Dim DS as New Dataset()

strSQL = "SELECT * FROM table" Dim MyAdapter As New MySqlDataAdapter(strSQL, MyConn) MyAdapter.Fill(DS, "table") <------ here comes the error MyAdapter.Dispose() MyAdapter = Nothing

Here si the error output:

MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime at MySql.Data.Types.MySqlDateTime.GetDateTime() in \MySQLClient\1.0.3\MySqlClient\Types\MySqlDateTime.cs:line 217 at MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i) in \MySQLClient\1.0.3\MySqlClient\datareader.cs:line 529 at MySql.Data.MySqlClient.MySqlDataReader.GetValues(Object[]

values)

\MySQLClient\1.0.3\MySqlClient\datareader.cs:line 545 at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges) at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

http://lists.mysql.com/dotnet?unsub=reg...@mysql.com