Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-12-01
2
Medium Priority
?
546 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

963 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