MySQL Date - problem with DataBindings

[KriZa]

Hi,

I got followin code:

Dim dt_antragsteller As DataTable
Dim da_antragsteller As MySqlDataAdapter
Dim cb_antragsteller As MySqlCommandBuilder
Dim bs_antragsteller As New BindingSource

dt_antragsteller = New DataTable
da_antragsteller = New MySqlDataAdapter("SELECT * FROM [...]", conn)
da_antragsteller.Fill(dt_antragsteller)
cb_antragsteller = New MySqlCommandBuilder(da_antragsteller)
bs_antragsteller.DataSource = dt_antragsteller

My.Forms.Formular.PLZ.DataBindings.Add(New Binding("Text", bs_antragsteller, "PLZ"))
My.Forms.Formular.Geb.DataBindings.Add(New Binding("Text", bs_antragsteller, "birthday"))

"birthday" is MySQL 'Date' Format
"PLZ" is Varchar(5)

my problem:
I bind the data to my forms textboxes - they are shown correctly.
But if I try to change the birthday-date an leave the textbox - the system overrides the input and there is still the old value. that is only in Date-Value-Textboxes. Text/Number (e.g. "PLZ") or other works fine!
there is no handle to do this event!

My connection string includes Allow Zero DateTime = Ture -> that is the problem! False works fine, but I have to use Null-Value Datefields! the strange thing is, that there is a validated date I try to change.

If I try

dt_antragsteller.Rows(0)("birthday") = "1990-08-22"
or
dt_antragsteller.Rows(0)("birthday") = "1990-08-22 00:00:00"

it fails with following german error:

Der Typ des Werts stimmt nicht mit dem Spaltentyp überein<1990-08-22> konnte nicht in der birthday-Spalte gespeichert werden. Erwarteter Typ: MySqlDateTime.

I try to translate:

the type of the value did not match to the column type <1990-08-22>
could not save the birthdy-column. expected Type: MySqlDateTime.

how can I bind date values - or what do I have to do to make it work.

thanks!

 

[cjard]

WHy are you using a textbox to represent a date? It probably cannot do the round-trip between date->string->date conversion

Use a date time picker that supports nulls (Google for NullableDateTimePicker)

 

[KriZa]

my solution:
declare MySQL Column Typa as Date
connection string: allow zero datetime = false
use masked textboxes instead of datetimepicker
catch the 'leave' Trigger of the textbox and change the datatable with following code:
dt_antragsteller.Rows(0)("birthday") = DBNull.Value

Take care! never use '0000-00-00' - causes problems!
Either a valid date or DBNull!

problem solved!