Unable to convert MySQL date/time value to System.

Question

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.

1. in connection string I have already added: AllowZeroDateTime=True but that does not help
2. 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 !

Answer 1

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