• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Unable to convert MySQL date/time value to System.DateTime running update on MySQL table

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!

Phil
0
TeamLink
Asked:
TeamLink
1 Solution
 
TeamLinkAuthor Commented:
incase this helps anyone, I fixed this by upgrading to the mysql .net connector version 1.0.8

Phil
0
 
RomModCommented:
Question closed - 150 points refunded.

Best regards,
RomMod
Experts Exchange
Community Support Moderator
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now