SqlDateTime overflow error inserting a null date into a nullable sql2000 datetime column using ado.n

[Chris]

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

 

[Craig Yellick]

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]

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

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" <[email protected]> wrote in message

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