Null database fields displaying as 01/00/1900 *RESOLVED *

[davidrobin]

I have a databoud grid where I am formatting the date with the following
{0:dd/mm/yyyy}

When the date is null in the database it is displaying
01/00/1900 as the date. How can I suppress this.

Using VB.Net

[techgnome]

You first have to check for it against 1/1/1900 and if the date is 1/1/1900 don't display it..... It seems stupid, but for some reason MS decided that when you assign A NULL to a data it would be 1/1/1900.... *shakes head*

TG

[davidrobin]

for all the technically complicated achievements Microsoft make they sometimes seem to forget the simple things to make life better.

[hellswraith]

This is because NULL is nothing, zilch, blah...

So, how would you ever be able to format a Null value? The answer is you can't. So MS does the next best thing and gives you a MinDate.

[techgnome]

That's like saying that you can't store a NULL value for an integer, so we should get 0 instead. Or an empty string for a varchar. It's not a format issue, it's about having (or not having) the correct data. Let's say I have an import process, and one of the data peices that comes in is a date field, but it's optional. I should be able to stuff a NULL value in there (which oddly enough, I can do w/o errors) and then retrieve it as such. Naturaly I'd wrap a IS NULL around it to make sure I don't do anything stupid with it.

It's a silly limitation in my opinion. But, it's not going to change, so one has to work around it. Still doesn't make it right.

TG

[hellswraith]

Originally posted by techgnome
That's like saying that you can't store a NULL value for an integer, so we should get 0 instead. Or an empty string for a varchar. It's not a format issue, it's about having (or not having) the correct data. Let's say I have an import process, and one of the data peices that comes in is a date field, but it's optional. I should be able to stuff a NULL value in there (which oddly enough, I can do w/o errors) and then retrieve it as such. Naturaly I'd wrap a IS NULL around it to make sure I don't do anything stupid with it.

It's a silly limitation in my opinion. But, it's not going to change, so one has to work around it. Still doesn't make it right.

TG

You can't store a null value for an integer, you do get zero...guess I don't understand... (unless you are refering to sql, which in that case you can)

When you retrieve the value from the database, it is null, but if you assign that null value to a datetime type, you have to convert it to something other than null because the datetime structure doesn't support being null.

Next version of the framework (2.0) will have support for nullable value types. I think that is going to solve some problems. But right now, you have to check for null before trying to do a format on it, otherwise you get a min date.

[davidrobin]

When binding data to controls with ADO if a field had a null value then the cell (suppose we were using a datagrid) would be blank, no value as the field held no value.
Now when you bind data to a datagrid in .NET as hellswraith mentioned you get the minimum values.
This causes more work, it would be nice if you had a choice whether you wanted to use the minimum value.

[davidrobin]

Originally posted by techgnome
You first have to check for it against 1/1/1900 and if the date is 1/1/1900 don't display it..... It seems stupid, but for some reason MS decided that when you assign A NULL to a data it would be 1/1/1900.... *shakes head*
TG

So if I have a datagrid that is bound to a dataset how do I intercept the binding and check the value of the date?

[hellswraith]

Call a public method that takes the value as a argument. Basically take where the field is specified in the HTML, and call a method that returns a string and takes in a object. Inside the method do the deed.

[Magiaus]

are we talking System.Data.SqlTypes.SqlDateTime or System.DateTime?

[davidrobin]

Originally posted by hellswraith
Basically take where the field is specified in the HTML, and call a method that returns a string and takes in a object. Inside the method do the deed.

It isn't specified in the HTML

are we talking System.Data.SqlTypes.SqlDateTime or System.DateTime?

That is a good question, I would say it is SQLDateTime type.


This is the code I am using to bind the datagrid.

Private Function PopulateGrid() As DataSet
 
        Dim strSQL As String
 
        strsql="blah blah"
 
        Dim dadData As New SqlDataAdapter(strSQL, clsLib.Connection)
        Dim dstData As New DataSet
        Try
            dadData.Fill(dstData)
            PopulateGrid = dstData
 
        Catch ex As SqlException
            clsLib.HandleErr(ex)
        End Try
    End Function

At what point would I format the date?

[davidrobin]

I'm a little bit embarassed because I have found the problem in my SQL expression.

I have a union so in making sure both queries returned the same data type fields I used

'' as start_date

and this is interpreted as 01/01/1900 00:00:000 when the query returns.
What I now have is

null as start_date

. So now when the data is bound to the datagrid where the field data is null no date is displayed.
Of course this was always working correctly I had confused myself and lost the plot with my SQL query.