Solved

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

Posted on 2006-12-01
2
538 Views
Last Modified: 2008-01-09
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
Comment
Question by:TeamLink
2 Comments
 

Author Comment

by:TeamLink
ID: 18124717
incase this helps anyone, I fixed this by upgrading to the mysql .net connector version 1.0.8

Phil
0
 

Accepted Solution

by:
RomMod earned 0 total points
ID: 18143663
Question closed - 150 points refunded.

Best regards,
RomMod
Experts Exchange
Community Support Moderator
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question