SQLDateTime Overflow inserting DateTime that is Nothing

Charles
Insert a value as string value with format YYYYMMDD

Post by Charles Law
I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime column
that is nullable.
Inserting dates normally is fine, but when I have a datetime variable in
VB that returns Nothing, I get an overflow exception from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?
TIA
Charles

Charles Law

2008-02-26 12:15:20 UTC

Hi Uri

I haven't tried this, but as I understand the problem it is that the
datetime variable is out of range for the database column.

A datetime variable in VB/C# that equates to Nothing is represented by the
string 1/1/01 12:00 A.M. If I try to insert this using the string format you
suggest I believe I will still get the same exception.

What I think I need is a way to insert the datetime variable in such a way
that when it is Nothing the database receives a Null value that it
understands.

Charles

Post by Charles Law
Charles
Insert a value as string value with format YYYYMMDD

Post by Charles Law
I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime
column that is nullable.
Inserting dates normally is fine, but when I have a datetime variable in
VB that returns Nothing, I get an overflow exception from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?
TIA
Charles

Uri Dimant

2008-02-26 12:21:57 UTC

Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format to be
inserted

Post by Charles Law
Hi Uri
I haven't tried this, but as I understand the problem it is that the
datetime variable is out of range for the database column.
A datetime variable in VB/C# that equates to Nothing is represented by the
string 1/1/01 12:00 A.M. If I try to insert this using the string format
you suggest I believe I will still get the same exception.
What I think I need is a way to insert the datetime variable in such a way
that when it is Nothing the database receives a Null value that it
understands.
Charles

Post by Charles Law
Charles
Insert a value as string value with format YYYYMMDD

Post by Charles Law
I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime
column that is nullable.
Inserting dates normally is fine, but when I have a datetime variable in
VB that returns Nothing, I get an overflow exception from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?
TIA
Charles

Armin Zingler

2008-02-26 12:33:09 UTC

Post by Uri Dimant
Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format
to be inserted

He wants to insert 'Null'. How do you expect him to format it to
'YYYYMMDD'?

It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.

Armin

Uri Dimant

2008-02-26 12:44:29 UTC

My bad Armin. I miss read it. I'm not a .NET expert , but I think the OP
needs specify System.DBNull to insert NULL

Post by Armin Zingler
It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.

It is safety using YYYYMMDD format to deal with dates, does not it base on
Regional Settings/Language if you not specify format

For examlpe the below does not work

SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

Post by Uri Dimant
Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format
to be inserted

He wants to insert 'Null'. How do you expect him to format it to
'YYYYMMDD'?
It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.
Armin

Armin Zingler

2008-02-26 12:52:59 UTC

Post by Uri Dimant
My bad Armin. I miss read it. I'm not a .NET expert , but I think
the OP needs specify System.DBNull to insert NULL

Post by Armin Zingler
It's always recommended to use the SqlCommand's Parameters
property. If you use it you don't have to care about the format at
all.

It is safety using YYYYMMDD format to deal with dates, does not it
base on Regional Settings/Language if you not specify format
For examlpe the below does not work
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

Ok, I see. Didn't see this one also reached a none .Net group. :-)

The advantage of a Parameter object is that you don't have to care about
the format at all. You simply assign the value - a valid date or
DBNull.Value (this is a kind of constant representing NULL in the .Net
world) - and the rest is done for you, independent from regional/culture
settings. And it's safe concerning SQL injection (discussed multiple
times in this (VB.Net) group and others).

Armin

Uri Dimant

2008-02-26 13:11:04 UTC

Armin

Post by Armin Zingler
The advantage of a Parameter object is that you don't have to care about
the format at all. You simply assign the value - a valid date or
DBNull.Value (this is a kind of constant representing NULL in the .Net
world) - and the rest is done for you, independent from regional/culture
settings. And it's safe concerning SQL injection (discussed multiple times
in this (VB.Net) group and others).

I fully agree with you, it is true especially when it comes to deal SQL
injection were discussed at this forum many times as well :-)))

Post by Uri Dimant
My bad Armin. I miss read it. I'm not a .NET expert , but I think
the OP needs specify System.DBNull to insert NULL

Post by Armin Zingler
It's always recommended to use the SqlCommand's Parameters
property. If you use it you don't have to care about the format at
all.

It is safety using YYYYMMDD format to deal with dates, does not it base
on Regional Settings/Language if you not specify format
For examlpe the below does not work
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

Ok, I see. Didn't see this one also reached a none .Net group. :-)
The advantage of a Parameter object is that you don't have to care about
the format at all. You simply assign the value - a valid date or
DBNull.Value (this is a kind of constant representing NULL in the .Net
world) - and the rest is done for you, independent from regional/culture
settings. And it's safe concerning SQL injection (discussed multiple times
in this (VB.Net) group and others).
Armin

Dan Guzman

2008-02-26 13:09:52 UTC

Uri, the string datetime format only applies in the T_SQL world. Values can
be passed from application code natively as described by Armin.

See
http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx
for my spin on the subject.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Uri Dimant
My bad Armin. I miss read it. I'm not a .NET expert , but I think the OP
needs specify System.DBNull to insert NULL

Post by Armin Zingler
It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.

It is safety using YYYYMMDD format to deal with dates, does not it base
on Regional Settings/Language if you not specify format
For examlpe the below does not work
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

Post by Uri Dimant
Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format
to be inserted

He wants to insert 'Null'. How do you expect him to format it to
'YYYYMMDD'?
It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.
Armin

Uri Dimant

2008-02-26 13:21:58 UTC

Dan

Post by Dan Guzman
Uri, the string datetime format only applies in the T_SQL world.

Yes , I have already realized it , just long time did touch on .NET
:-))))))

I added your blog to my favorite links and hope you will keep on writing
great articles in the future :-))

Post by Dan Guzman
Uri, the string datetime format only applies in the T_SQL world. Values
can be passed from application code natively as described by Armin.
See
http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx
for my spin on the subject.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Uri Dimant
My bad Armin. I miss read it. I'm not a .NET expert , but I think the OP
needs specify System.DBNull to insert NULL

Post by Armin Zingler
It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.

It is safety using YYYYMMDD format to deal with dates, does not it base
on Regional Settings/Language if you not specify format
For examlpe the below does not work
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

Post by Uri Dimant
Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format
to be inserted

He wants to insert 'Null'. How do you expect him to format it to
'YYYYMMDD'?
It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.
Armin

Armin Zingler

2008-02-26 12:19:04 UTC

How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

(unfortunatelly the type is named 'Nullable'; don't mix it with DBNull.
'Nothing' is clearer in VB.Net)

Post by Charles Law
I get an overflow exception
from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?

Unfortunatelly, VB.Net allows

ValueTypeVar = Nothing

However, this does *not* assign Nothing, in the sense of 'no reference'.
Instead, it assigns the type's default value, which is DateTime.MinValue
(January 1, 0001). This value is out of the data type range in SQL
server (years 1753-9999; smalldatetime=1900 - 6th Jan 2079). Therfore
the error.

So, never use 'Nothing' with value types. It's only confusing.
Use 'Nothing' only with reference types.

Post by Charles Law
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems
like a kludge. What is the designed Microsoft approach to this
problem?

I'm not MSFT, but... It depends on your (in-memory) data storage. If
you use a DataAdapter and a typed DataSet, it should do the work for
you. Internally it's stored 'As Object' which allows DateTime as well as
DBNull.Value.
Otherwise, use Nullable(Of DateTime) and translate it to DBNull.Value if
HasValue=False.

Armin

Charles Law

2008-02-26 12:34:20 UTC

Hi Armin

Post by Armin Zingler
How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

Because

MyDateTimeVar.Equals(Nothing)

returns True.

Post by Armin Zingler
Unfortunatelly, VB.Net allows
ValueTypeVar = Nothing

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

That said, I take your points entirely. They completely explain why I have
the problem.

Post by Armin Zingler
If you use a DataAdapter and a typed DataSet, it should do the work for
you. Internally it's stored 'As Object' which allows DateTime as well as
DBNull.Value.

I do use a DataAdapter and DataSet, but still get the exception.

Post by Armin Zingler
Otherwise, use Nullable(Of DateTime) and translate it to DBNull.Value if
HasValue=False.

I'm not quite sure what you mean here. Could you explain a bit further?

Thanks.

Charles

How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?
(unfortunatelly the type is named 'Nullable'; don't mix it with DBNull.
'Nothing' is clearer in VB.Net)

Post by Charles Law
I get an overflow exception
from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?

Unfortunatelly, VB.Net allows
ValueTypeVar = Nothing
However, this does *not* assign Nothing, in the sense of 'no reference'.
Instead, it assigns the type's default value, which is DateTime.MinValue
(January 1, 0001). This value is out of the data type range in SQL
server (years 1753-9999; smalldatetime=1900 - 6th Jan 2079). Therfore
the error.
So, never use 'Nothing' with value types. It's only confusing.
Use 'Nothing' only with reference types.

I'm not MSFT, but... It depends on your (in-memory) data storage. If you
use a DataAdapter and a typed DataSet, it should do the work for you.
Internally it's stored 'As Object' which allows DateTime as well as
DBNull.Value.
Otherwise, use Nullable(Of DateTime) and translate it to DBNull.Value if
HasValue=False.
Armin

Armin Zingler

2008-02-26 12:48:57 UTC

Post by Charles Law
Hi Armin

Post by Armin Zingler
How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

Because
MyDateTimeVar.Equals(Nothing)
returns True.

Post by Armin Zingler
Unfortunatelly, VB.Net allows
ValueTypeVar = Nothing

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

Well, it's 'by design' and documented this way. Well, bad design IMO
(and IYO). At least there should be an option that makes this a warning
or compile error.

Post by Charles Law
That said, I take your points entirely. They completely explain why
I have the problem.

Post by Armin Zingler
If you use a DataAdapter and a typed DataSet, it should do the
work for you. Internally it's stored 'As Object' which allows
DateTime as well as DBNull.Value.

I do use a DataAdapter and DataSet, but still get the exception.

I meant, if it contains DBNull.Value you don't have to care about it on
your own. But if you assign Nothing, you actually assign 1/1/0001. The
Dataadapter doesn't know that this is out of range.

Post by Armin Zingler
Otherwise, use Nullable(Of DateTime) and translate it to
DBNull.Value if HasValue=False.

I'm not quite sure what you mean here. Could you explain a bit
further?

I said it without knowing how you store the value, and I meant:

dim dt as Nullable(of datetime)

'...

if dt.HasValue then
cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if

But because you are using a DataSet+DataAdapter, this is "oboslete"; but
you get the point.

So, if you work with the DataSet, assign DBNull.Value if you need to.
The DataAdapter will do the required insert/update of NULL into the
database.

Armin

Charles Law

2008-02-26 13:37:14 UTC

Post by Armin Zingler
if dt.HasValue then
cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if

and

Post by Armin Zingler
So, if you work with the DataSet, assign DBNull.Value if you need to.
The DataAdapter will do the required insert/update of NULL into the
database.

I understand the technique then, but these are both what I mean by a kludge.
Given that one of the most common things one might want to do is put data in
a database, why do I need to start testing the values of a built-in data
type and make a special case out of some values? If this is the case for
datetime types then presumably it affects other types, in different ways?

It means that everywhere I want to store a datetime variable in a column I
have to test it first. This just seems like it is designed to be awkward.

Am I missing something? Isn't there a more natural way to do this?

Charles

Post by Charles Law
Hi Armin

Post by Armin Zingler
How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

Because
MyDateTimeVar.Equals(Nothing)
returns True.

Post by Armin Zingler
Unfortunatelly, VB.Net allows
ValueTypeVar = Nothing

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

Well, it's 'by design' and documented this way. Well, bad design IMO
(and IYO). At least there should be an option that makes this a warning
or compile error.

Post by Charles Law
That said, I take your points entirely. They completely explain why
I have the problem.

Post by Armin Zingler
If you use a DataAdapter and a typed DataSet, it should do the
work for you. Internally it's stored 'As Object' which allows
DateTime as well as DBNull.Value.

I do use a DataAdapter and DataSet, but still get the exception.

I meant, if it contains DBNull.Value you don't have to care about it on
your own. But if you assign Nothing, you actually assign 1/1/0001. The
Dataadapter doesn't know that this is out of range.

Post by Armin Zingler
Otherwise, use Nullable(Of DateTime) and translate it to
DBNull.Value if HasValue=False.

I'm not quite sure what you mean here. Could you explain a bit further?

dim dt as Nullable(of datetime)
'...
if dt.HasValue then
cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if
But because you are using a DataSet+DataAdapter, this is "oboslete"; but
you get the point.
So, if you work with the DataSet, assign DBNull.Value if you need to.
The DataAdapter will do the required insert/update of NULL into the
database.
Armin

Armin Zingler

2008-02-26 13:56:41 UTC

Post by Armin Zingler
if dt.HasValue then
cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if

and

Post by Armin Zingler
So, if you work with the DataSet, assign DBNull.Value if you need
to. The DataAdapter will do the required insert/update of NULL
into the database.

Because it _is_ a special case. Though,... (see below)

Post by Charles Law
If this is the case for datetime types then presumably it
affects other types, in different ways?
It means that everywhere I want to store a datetime variable in a
column I have to test it first. This just seems like it is designed
to be awkward.
Am I missing something? Isn't there a more natural way to do this?

Where is the problem if you use a typed dataset?

I have to add to my previous post: Assigning DBNull.Value only works
with DataSets that are not typed. With typed Datasets, you have to call
MyDS.SetXYZColumNull to set it to NULL and MyDS.IsXYZColumnNull to check
for NULL. Though, this doesn't affect the way the DataAdapter updates
the table.

Anyways, I don't see any usage for 'Nothing' in this case. If you don't
use it, you won't have a problem.

Armin

Charles Law

2008-02-26 14:17:28 UTC

I'm struggling to come to terms with the fact that what seems to be a
straight forward problem, does not have [what I consider to be] a straight
forward solution.

As I replied to Dan, I recall looking at typed DataSets some time ago, but
was very concerned by the amount of code generated in the background, that
could perhaps be rendered useless if the schema of my table changed even
slightly; or have I got that bit wrong as well?

I think I need to lie down in a darkened room for a bit and get my head
round this ;-)

Charles

Post by Armin Zingler
if dt.HasValue then
cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if

and

Post by Armin Zingler
So, if you work with the DataSet, assign DBNull.Value if you need
to. The DataAdapter will do the required insert/update of NULL
into the database.

Because it _is_ a special case. Though,... (see below)

Where is the problem if you use a typed dataset?
I have to add to my previous post: Assigning DBNull.Value only works
with DataSets that are not typed. With typed Datasets, you have to call
MyDS.SetXYZColumNull to set it to NULL and MyDS.IsXYZColumnNull to check
for NULL. Though, this doesn't affect the way the DataAdapter updates
the table.
Anyways, I don't see any usage for 'Nothing' in this case. If you don't
use it, you won't have a problem.
Armin

Herfried K. Wagner [MVP]

2008-02-26 15:00:40 UTC

Post by Armin Zingler
How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

Because
MyDateTimeVar.Equals(Nothing)
returns True.

Post by Armin Zingler
Unfortunatelly, VB.Net allows
ValueTypeVar = Nothing

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.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Charles Law

2008-02-26 15:12:11 UTC

Post by Herfried K. Wagner [MVP]

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.

Post by Herfried K. Wagner [MVP]

Unfortunately, VB.Net allows
ValueTypeVar = Nothing

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

How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

Because
MyDateTimeVar.Equals(Nothing)
returns True.

Unfortunatelly, VB.Net allows
ValueTypeVar = Nothing

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.
--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Dan Guzman

2008-02-26 12:26:38 UTC

Post by Charles Law
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?

Unfortunately, you'll need to specify System.DBNull.Value when you want a
database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types do
not necessarily map directly to SqlDbTypes. For example, System.DataTime
has a greater range than a SQL Server datetime. Personally, I use helper
classes to take care the conversion and null values.

I can't say why you are getting an overflow error without seeing your code.
It seems to me that the supplied datetime value supplied is a value outside
of the allowable SQL datetime range rather than Nothing.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Charles Law

2008-02-26 12:43:10 UTC

Hi Dan

Post by Dan Guzman
Unfortunately, you'll need to specify System.DBNull.Value when you want a
database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types
do not necessarily map directly to SqlDbTypes. For example,
System.DataTime has a greater range than a SQL Server datetime.

From Armin's comments and the nature of the exception, I now understand the
issue here. What I was hoping was that Microsoft would have a built-in way
to handle this scenario. It must be very common to want to put a datetime
variable into a SQL Server database when the variable has a value that
represents an uninitialised datetime.

Armin indicated that it should be handled for me behind the scenes if I use
a DataAdapter and DataSet, but id doesn't seem to be.

I'm sure I'm still doing something wrong, and I'd like to change whatever I
need to so that it works as MS intended, but not if it means using a kludge.

Charles

Post by Charles Law
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?

Unfortunately, you'll need to specify System.DBNull.Value when you want a
database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types
do not necessarily map directly to SqlDbTypes. For example,
System.DataTime has a greater range than a SQL Server datetime.
Personally, I use helper classes to take care the conversion and null
values.
I can't say why you are getting an overflow error without seeing your
code. It seems to me that the supplied datetime value supplied is a value
outside of the allowable SQL datetime range rather than Nothing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Charles Law
I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime
column that is nullable.
Inserting dates normally is fine, but when I have a datetime variable in
VB that returns Nothing, I get an overflow exception from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?
TIA
Charles

Dan Guzman

2008-02-26 13:05:38 UTC

Post by Charles Law
Armin indicated that it should be handled for me behind the scenes if I
use a DataAdapter and DataSet, but id doesn't seem to be.

Armin specifically mentioned a typed DataSet. Is that what you are using?
I haven't used use a typed dataset since before .NET 2.0 but I would think
you would need to avoid assigning the datetime value to the DataRow to
retain a NULL value of the newly created row in the dataset. There really
is no such thing as a null (Nothing) DataTime reference so assigning a any
value except for a DbNull /Nullable will result in a non-NULL value.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Charles Law
Hi Dan

From Armin's comments and the nature of the exception, I now understand
the issue here. What I was hoping was that Microsoft would have a built-in
way to handle this scenario. It must be very common to want to put a
datetime variable into a SQL Server database when the variable has a value
that represents an uninitialised datetime.
Armin indicated that it should be handled for me behind the scenes if I
use a DataAdapter and DataSet, but id doesn't seem to be.
I'm sure I'm still doing something wrong, and I'd like to change whatever
I need to so that it works as MS intended, but not if it means using a
kludge.
Charles

Post by Charles Law
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?

Unfortunately, you'll need to specify System.DBNull.Value when you want a
database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types
do not necessarily map directly to SqlDbTypes. For example,
System.DataTime has a greater range than a SQL Server datetime.
Personally, I use helper classes to take care the conversion and null
values.
I can't say why you are getting an overflow error without seeing your
code. It seems to me that the supplied datetime value supplied is a value
outside of the allowable SQL datetime range rather than Nothing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Charles Law
I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime
column that is nullable.
Inserting dates normally is fine, but when I have a datetime variable in
VB that returns Nothing, I get an overflow exception from the database.
What is the correct way to deal with datetime types that evaluate to
Nothing?
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?
TIA
Charles

Charles Law

2008-02-26 14:11:33 UTC

Post by Dan Guzman
Armin specifically mentioned a typed DataSet. Is that what you are using?

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 :-(

Charles

Post by Charles Law
Armin indicated that it should be handled for me behind the scenes if I
use a DataAdapter and DataSet, but id doesn't seem to be.

Armin specifically mentioned a typed DataSet. Is that what you are using?
I haven't used use a typed dataset since before .NET 2.0 but I would think
you would need to avoid assigning the datetime value to the DataRow to
retain a NULL value of the newly created row in the dataset. There really
is no such thing as a null (Nothing) DataTime reference so assigning a any
value except for a DbNull /Nullable will result in a non-NULL value.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Charles Law
Hi Dan

Post by Dan Guzman
Unfortunately, you'll need to specify System.DBNull.Value when you want
a database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types
do not necessarily map directly to SqlDbTypes. For example,
System.DataTime has a greater range than a SQL Server datetime.

From Armin's comments and the nature of the exception, I now understand
the issue here. What I was hoping was that Microsoft would have a
built-in way to handle this scenario. It must be very common to want to
put a datetime variable into a SQL Server database when the variable has
a value that represents an uninitialised datetime.
Armin indicated that it should be handled for me behind the scenes if I
use a DataAdapter and DataSet, but id doesn't seem to be.
I'm sure I'm still doing something wrong, and I'd like to change whatever
I need to so that it works as MS intended, but not if it means using a
kludge.
Charles

Post by Charles Law
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?

Unfortunately, you'll need to specify System.DBNull.Value when you want
a database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types
do not necessarily map directly to SqlDbTypes. For example,
System.DataTime has a greater range than a SQL Server datetime.
Personally, I use helper classes to take care the conversion and null
values.
I can't say why you are getting an overflow error without seeing your
code. It seems to me that the supplied datetime value supplied is a
value outside of the allowable SQL datetime range rather than Nothing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Charles Law
I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime
column that is nullable.
Inserting dates normally is fine, but when I have a datetime variable
in VB that returns Nothing, I get an overflow exception from the
database.
What is the correct way to deal with datetime types that evaluate to
Nothing?
I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?
TIA
Charles

Steve Gerrard

2008-02-26 16:22:04 UTC

Post by Dan Guzman
Armin specifically mentioned a typed DataSet. Is that what you are using?

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

2008-02-26 16:45:04 UTC

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]

Post by Dan Guzman
Armin specifically mentioned a typed DataSet. Is that what you are using?

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.

Steve Gerrard

2008-02-27 03:36:37 UTC