SqlDateTime overflow error inserting a null date into a nullable sql2000 datetime column using ado.net/vb.net, 1.1 framework

I'm trying to insert a null date into a nullable sql2000 datetime column,
but i'm getting this error:

An unhandled exception of type 'System.Data.SqlTypes.SqlTypeException'
occurred in system.data.dll

Additional information: SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM.

See code below.

Any help is greatly appreciated!

Chris

-------START----------------------
Imports System.Data.SqlClient
MyFct(ByVal SomeDate As DateTime)
...
cmd.CommandText = "MyStoredProc"
cmd.Parameters.Add(New SqlParameter("@SomeDate", SqlDbType.DateTime, 8))
'IsDBNull(SomeDate) is True at this point (aka, #12:00:00 AM#).
cmd.Parameters("@SomeDate").Value = SomeDate
cn.Open()
cmd.ExecuteNonQuery() 'This line gives error (above).
end function
-----------------------------
SQL Server 2000 Stored Proc:
CREATE PROCEDURE MyStoredProc
@SomeDate datetime
AS
INSERT INTO MyTable (
SomeDate
)
VALUES (
@SomeDate
)
-----------------------------
SQL Server 2000 Partial Tbl Def:
CREATE TABLE [MyTable] (
...
[SomeDate] [datetime] NULL
)

-----------END------------------

CraigGoogle
Fri Sep 10 09:06:53 CDT 2004

You're running into the differences between the SqlServer versus .NET
definition of the datetime data type.

Your MyFct function accepts the parameter SomeDate as a datetime, and
I'll guess that you're sending the .NET value of "nothing" as a
parameter value. That's not the same as a SqlServer "null" value. You
get something for nothing with .NET datetime (sorry, could not resist
that one). Add the code fragment below, which converts the .NET
datetime into an actual null.

If SomeDate = DateTime.MinValue Then
cmd.Parameters("@SomeDate").Value = DBNull.Value
Else
cmd.Parameters("@SomeDate").Value = SomeDate
End If

-- Craig Yellick, Alto
www.altoConsulting.com
www.altoTraining.com

"Chris" <cevans@bugmonitor.com> wrote in message news:<C280d.105239$0o5.92390@bignews1.bellsouth.net>...
> I'm trying to insert a null date into a nullable sql2000 datetime column,
> but i'm getting this error:
>
> An unhandled exception of type 'System.Data.SqlTypes.SqlTypeException'
> occurred in system.data.dll
>
> Additional information: SqlDateTime overflow. Must be between 1/1/1753
> 12:00:00 AM and 12/31/9999 11:59:59 PM.
>
> See code below.
>
> Any help is greatly appreciated!
>
> Chris
>
> -------START----------------------
> Imports System.Data.SqlClient
> MyFct(ByVal SomeDate As DateTime)
> ...
> cmd.CommandText = "MyStoredProc"
> cmd.Parameters.Add(New SqlParameter("@SomeDate", SqlDbType.DateTime, 8))
> 'IsDBNull(SomeDate) is True at this point (aka, #12:00:00 AM#).
> cmd.Parameters("@SomeDate").Value = SomeDate
> cn.Open()
> cmd.ExecuteNonQuery() 'This line gives error (above).
> end function
> -----------------------------
> SQL Server 2000 Stored Proc:
> CREATE PROCEDURE MyStoredProc
> @SomeDate datetime
> AS
> INSERT INTO MyTable (
> SomeDate
> )
> VALUES (
> @SomeDate
> )
> -----------------------------
> SQL Server 2000 Partial Tbl Def:
> CREATE TABLE [MyTable] (
> ...
> [SomeDate] [datetime] NULL
> )
>
> -----------END------------------

Chris
Sat Sep 11 23:01:21 CDT 2004

Craig,

Thanks! That was it indeed. I thought i'd already tried that, but i think i
was trying to do it in one line. Now i've got a function for all these.

Thanks again,

Chris


"Craig Yellick" <CraigGoogle@Yahoo.com> wrote in message
news:181a3bdc.0409100606.592c88cd@posting.google.com...
> You're running into the differences between the SqlServer versus .NET
> definition of the datetime data type.
>
> Your MyFct function accepts the parameter SomeDate as a datetime, and
> I'll guess that you're sending the .NET value of "nothing" as a
> parameter value. That's not the same as a SqlServer "null" value. You
> get something for nothing with .NET datetime (sorry, could not resist
> that one). Add the code fragment below, which converts the .NET
> datetime into an actual null.
>
> If SomeDate = DateTime.MinValue Then
> cmd.Parameters("@SomeDate").Value = DBNull.Value
> Else
> cmd.Parameters("@SomeDate").Value = SomeDate
> End If
>
> -- Craig Yellick, Alto
> www.altoConsulting.com
> www.altoTraining.com
>
> "Chris" <cevans@bugmonitor.com> wrote in message
news:<C280d.105239$0o5.92390@bignews1.bellsouth.net>...
> > I'm trying to insert a null date into a nullable sql2000 datetime
column,
> > but i'm getting this error:
> >
> > An unhandled exception of type 'System.Data.SqlTypes.SqlTypeException'
> > occurred in system.data.dll
> >
> > Additional information: SqlDateTime overflow. Must be between 1/1/1753
> > 12:00:00 AM and 12/31/9999 11:59:59 PM.
> >
> > See code below.
> >
> > Any help is greatly appreciated!
> >
> > Chris
> >
> > -------START----------------------
> > Imports System.Data.SqlClient
> > MyFct(ByVal SomeDate As DateTime)
> > ...
> > cmd.CommandText = "MyStoredProc"
> > cmd.Parameters.Add(New SqlParameter("@SomeDate", SqlDbType.DateTime,
8))
> > 'IsDBNull(SomeDate) is True at this point (aka, #12:00:00 AM#).
> > cmd.Parameters("@SomeDate").Value = SomeDate
> > cn.Open()
> > cmd.ExecuteNonQuery() 'This line gives error (above).
> > end function
> > -----------------------------
> > SQL Server 2000 Stored Proc:
> > CREATE PROCEDURE MyStoredProc
> > @SomeDate datetime
> > AS
> > INSERT INTO MyTable (
> > SomeDate
> > )
> > VALUES (
> > @SomeDate
> > )
> > -----------------------------
> > SQL Server 2000 Partial Tbl Def:
> > CREATE TABLE [MyTable] (
> > ...
> > [SomeDate] [datetime] NULL
> > )
> >
> > -----------END------------------