Solved

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

Posted on 2006-12-01
2
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

636 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