mysql zerodate,无法将MySQL日期/时间值转换为System.DateTime.Couldn't存储0/0/0000 0:00:00值？...-CSDN博客

Me.TreatmentsTableAdapter.Fill(Me.UserDataSet1.treatments)

This line is producing error:

Unable to convert MySQL date/time value to System.DateTime. Couldn't store <0/0/0000 0:00:00> in TreatmentDateEdited Column. Expected type is DateTime.

TreatmentDateEdited column is DateTime column that unfortunately contains some NULL values.

I know that there is a problem in Dataset to convert NULL date to anything else, thus producing me this error.

in connection string I have already added: AllowZeroDateTime=True but that does not help

Now from the above error msg I see that NULL value is already converted to 0/0/0000 0:00:00 but it cant store it in date field.

What date format should I use here in order to have Tableadapter operational ?

EDIT: I found possible problem here:

MySQL column of type DateTime doesn't allow me to enter date in any format I tried. I foudn that the default date format is 1000-01-01 00:00:00 but it wont accept that ! this is the error

Invalid value for cell (row 3, column 8).

The changed value in this cell was not recognized as valid..Net Framework Data Type: MySqlDateTime

Error Message: Invalid cast from 'System.String' to 'MySql.Data.Types.MySqlDateTime'.

So if I cant write date in date column manually neither TableAdapter will do. Any ideas ?

EDIT2: @GoroundoVipa

The thing is I dont loop dataset but use it as datasource in form controls.

Here are 2 other things I have noticed:

1. When using VisualStudio ServerExplorer to access database I am not able to enter new data in datetime field (producing above error)

2. When I access DB through MySQL workbench I got another error:

"Error: DB.TABLE: table data is not editable because there is no primary key defined for the table"

Server: MySQL

Version: 5.1.73

How on earth I can not edit table row if it doesn't have PK. It was never required !

解决方案

The SQL NULL Value for Date is 1900-01-01 00:00:00.000, You won't be able to insert a Date in 0000-00-00 00:00:00.000

I've Experience it lately, my solution for that is store the NULL value and replace it when displaying in DataGridView or Etc...

Sample:

if DataGridView1.Rows(e.Index).Cells(10).Value.ToString = "1900-01-01 00:00:00.000" Then

DataGridView1.Rows(e.Index).Cells(10).Value = ""

End If