null dates in mysql throw exception when loaded into datagridview

[toomutch]

Hi all,
Having problems with null dates in a mysql table. Have eventually found out that I can store a 'null' date in mysql as "0000-00-00" and I can run the 'insert' command to add a record to the mysql table. When I check on mysql workbench I can see the row has been inserted correctly and the date shows as "0000-00-00".

However...
When I try to load the table into a datagridview, I get an error = mysql Conversion Exception was unhandled = "Unable to convert MySQL date/time value to System.DateTime".

how do I get the datagridview (or VB.NET as a whole?) to handle zero dates. Alternatively is there a different method of storing a null date, rather than zero date.

Many Thanks, Toomutch

[Reverend Jim]

I don't know if this is available in MySql but it is a feature of MS SQL (and in a slightly different form, Access). The COALESCE function can be used in a sql query to replace null values with a defailt value. For example

select lastName, firstName, COALESCE(title,'n/a') from table1

returns "n/a" as the value of the field, title, where the actual value of the field is NULL. You might see if MySql has a similar feature which would allow you to specify an appropriate default date in the query.

[hericles]

Coalesce is available in MySql and it works as defined above by reverend_jim.

[Pgmer]

or else in front end before assigning to grid u can use IIF( function) if ur doing looing. but its nice and good to handle in SQL server.

[toomutch]

Hi Guys, couldn't get the dgv to display what I wanted (I either wanted a date, or blank but couldn't get the blank to come out properly). I ended up changing the date column to an integer column and storing the date as an integer with zero for blank, and adding a new column to my table called "DisplayedDate" defined as varchar(10). I then show the DisplayedDate in the dgv, but can still do the calculations on my integer column. Thanks for the input.
Toomutch