Unable to convert MySQL date/time value to System.DateTime running update on MySQL table
Posted on 2006-12-01
I have a real problem with a .net application I am writing.
basically all it does is connect to a MySQL database, use a query to get a list of records, loads this into a local dataset, runs some rules and updates the local dataset and then passes the dataset into the Update command of the MySQL .Net DataAdapter
It all goes wrong when one of the records in the database contains a date value of "0000-00-00". I understand from some googling that .net doesn't like this very much.
I did get the problem when simply gathering the records, but rectified that by adding "Allow Zero DateTime=True" to the connection string.
However, my program still fails when running the Update command from the MySql .net data adapter to commit the changes back to the database.
This happens despite me looping through the rows in my dataset, finding any such date values and setting them to something else before doing the Update. I have tried setting them to a string value which , as you would expect threw up an error telling me I couldn't set it as a string, I've set it to DBNull, which returns the same "Unable to convert...." error, and I've tried setting it to a System.DateTime value which again gives "Unable to convert...".
Each time I do this, I debug and run a watch on the dataset I am committing back to MySQL, and I can see the updated date value in the dataset, so why does .net STILL throw the exception!! Arrrrrrghghgh!
Infact, even if I DELETE the offending row from the dataset that gets passed to the Update command, I still get the problem. I am sure I am doing something really dumb, but I'm totally stuck now!
help me please!