SQLDateTime Overflow inserting DateTime that is Nothing

[Herfried K. Wagner [MVP]]

Because

MyDateTimeVar.Equals(Nothing)

returns True.


Not just unfortunate, I would say, but simply wrong.

Why? 'Nothing' in VB simply means "the type's default value", which is a
'Nothing' reference for value types and "all zeroes" for value types. Note
that you'd have to write 'default(<Type>)' in C# to archieve the same result
as setting a variable of a value type to 'Nothing' in VB. BTW, the behavior
is specified.

In addition, you could even write 'If MyDateTimeVar = Nothing Then' in VB to
compare a 'Date' to its default value.

 

[Charles Law]

Hi Herfried

My initial mistake was forgetting that datetime is a value type, and
therefore cannot be set to a null reference, which is what I thought I was
doing with

Dim MyVar As DateTime

MyVar = Nothing

I appreciate that I am setting it to a defined value that can be interpreted
as Nothing, but that clearly isn't the same as a 'null' value.

Not just unfortunate, I would say, but simply wrong.

Why? 'Nothing' in VB simply means "the type's default value", which is a
'Nothing' reference for value types and "all zeroes" for value [sic]
types.

I would say because it has led to confusion here, and I suspect for other
people too. All zeros: fine, but the type's default value is a bit too
obscure a definition for me, even if it is documented.

Charles

 

[Steve Gerrard]

Er, no. I remember now looking at typed datasets a while ago, and
being horrified at the amount of code generated for even a small
table. It put me off.

Perhaps I should revisit :-(

I had the same reaction. Yikes! Plus it is a pain if you want to add an
expression column or whatever...

On the original subject: I have been putting Nulls into date fields for a while,
and was wondering why I haven't had a problem. I don't use the Nullable type,
which I guess explains it. If you want a Date to be Null, you need to assign it
the DBNull value, not Nothing.

Where is the input coming from? If it is from a user, you should be able to
catch it when they enter it. I use a control that includes a property for what
goes into the datasource for a blank date, preset to DBNull. If it is assigned
in code, just assign DBNull instead of Nothing.

I think you will find that if you drop the Nullable type, and just use plain
Dates, the ADONet part will work just fine on its own, and it is not difficult
to assign DBNull where needed.

 

[Charles Law]

Hi Steve

If you want a Date to be Null, you need to assign it the DBNull value, not
Nothing.

Initially, I thought *Brilliant*, why didn't I think of that. But I tried
it, and I think I see what you mean now.

If I try

Dim MyVar As DateTime

MyVar = DBNull.Value

I get a compile time error, because DBNull can't be converted to a Date.

I suspect that isn't what you meant, or is it? If you meant that I should
put DBNull into the database then I know that will work. That is

If MyVar.Equals(Nothing) Then
MyDataRow("ColumnOfTypeDateTime") = DBNull.Value
Else
MyDataRow("ColumnOfTypeDateTime") = MyVar
Endif

But what I was really hoping for was something that works like this:

Dim MyVar As DateTypeThatCanBeNullOrNothing

MyVar = Nothing

MyDataRow("ColumnOfTypeDateTime") = MyVar


where the last line works whether MyVar has a real value or is set to
Nothing/Null.

Charles
[You're not a footballer in your spare time are you? ... I bet you get that
all the time]

 

[Steve Gerrard]

Hi Steve


Initially, I thought *Brilliant*, why didn't I think of that. But I
tried it, and I think I see what you mean now.

I realized after posting that I wasn't very clear about it. Thanks for the
moment of brilliance, anyway.

I suspect that isn't what you meant, or is it?

I'm going to claim no.

But what I was really hoping for was something that works like this:

Dim MyVar As DateTypeThatCanBeNullOrNothing

MyVar = Nothing

MyDataRow("ColumnOfTypeDateTime") = MyVar


where the last line works whether MyVar has a real value or is set to
Nothing/Null.

I suppose you could do

Dim MyVar As Object

and do that, but it isn't quite the same, since now it is
DateTypeThanCanBeJustAboutAnything.

And you would still have to do

MyVar = System.DBNull.Value

and test for Null, not Nothing. Probably not worth it.

I guess I just found that I with the GUI bound to the data directly, whenever I
needed a date value, I could just check if the column value was Null, or set it
to Null if that was needed.