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

[mlafarlett]

Alright..I've found lots of info on this error and most say assign
System.DBNull.V alue, System.Convert. DBNull, or
System.Data.Sql Types.SqlDateTi me.Null to my stored proc param and the
problem will be solved...NOT. I suspect all of these are the same
thing, however, none solve my problem. What I have is vb.net code
trying to put a null in a sql2000 datatime field. To further confuse
things, it works on my PC and not from another. I checked the DSN
being used on each machine were the same and they were. I confirmed
the SQL data type is a 'datetime' and that it is NULLable.

Code:

Dim prmBatchTsUpd As SqlParameter = new SqlParameter
prmBatchTsUpd.P arameterName = "@batch_ts"
prmBatchTsUpd.S qlDbType = SqlDbType.DateT ime
prmBatchTsUpd.S ourceColumn = "batch_ts"
sqlUpdateProCmd .Parameters.Add (prmBatchTsUpd)

prmBatchTsUpd.V alue = System.Data.Sql Types.SqlDateTi me.Null

sqlUpdateProCmd .ExecuteNonQuer y()

Stored Procedure Definition:
CREATE Procedure dbo.si_sp_XFERS ERVICE_update_x fer_profile
(
@program_name varchar(40),
@active char(1),
@batch_ts datetime
)
as

update css$steph.dbo.x fer_profile
set active = @active,
batch_ts = @batch_ts
where program_name = @program_name

Help please?

[Marina Levit [MVP]]

If the same code is working on one machine and not another, then I am
guessing the other machine is going to a different database. That, or it is
running an older copy of the code or something.

<ml********@yah oo.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.com...

Alright..I've found lots of info on this error and most say assign
System.DBNull.V alue, System.Convert. DBNull, or
System.Data.Sql Types.SqlDateTi me.Null to my stored proc param and the
problem will be solved...NOT. I suspect all of these are the same
thing, however, none solve my problem. What I have is vb.net code
trying to put a null in a sql2000 datatime field. To further confuse
things, it works on my PC and not from another. I checked the DSN
being used on each machine were the same and they were. I confirmed
the SQL data type is a 'datetime' and that it is NULLable.

Code:

Dim prmBatchTsUpd As SqlParameter = new SqlParameter
prmBatchTsUpd.P arameterName = "@batch_ts"
prmBatchTsUpd.S qlDbType = SqlDbType.DateT ime
prmBatchTsUpd.S ourceColumn = "batch_ts"
sqlUpdateProCmd .Parameters.Add (prmBatchTsUpd)

prmBatchTsUpd.V alue = System.Data.Sql Types.SqlDateTi me.Null

sqlUpdateProCmd .ExecuteNonQuer y()

Stored Procedure Definition:
CREATE Procedure dbo.si_sp_XFERS ERVICE_update_x fer_profile
(
@program_name varchar(40),
@active char(1),
@batch_ts datetime
)
as

update css$steph.dbo.x fer_profile
set active = @active,
batch_ts = @batch_ts
where program_name = @program_name

Help please?

[Cor Ligthert [MVP]]

I do not completely understand your question.

But 32 december 2006 is not allowed, you should than use 1 january 2007.

Setting a VB dbnull.value in a SQL datetime field should not be a problem
especially not as you use parameters.

Cor
<ml********@yah oo.com> schreef in bericht
news:11******** *************@g 10g2000cwb.goog legroups.com...

Alright..I've found lots of info on this error and most say assign
System.DBNull.V alue, System.Convert. DBNull, or
System.Data.Sql Types.SqlDateTi me.Null to my stored proc param and the
problem will be solved...NOT. I suspect all of these are the same
thing, however, none solve my problem. What I have is vb.net code
trying to put a null in a sql2000 datatime field. To further confuse
things, it works on my PC and not from another. I checked the DSN
being used on each machine were the same and they were. I confirmed
the SQL data type is a 'datetime' and that it is NULLable.

Code:

Dim prmBatchTsUpd As SqlParameter = new SqlParameter
prmBatchTsUpd.P arameterName = "@batch_ts"
prmBatchTsUpd.S qlDbType = SqlDbType.DateT ime
prmBatchTsUpd.S ourceColumn = "batch_ts"
sqlUpdateProCmd .Parameters.Add (prmBatchTsUpd)

prmBatchTsUpd.V alue = System.Data.Sql Types.SqlDateTi me.Null

sqlUpdateProCmd .ExecuteNonQuer y()

Stored Procedure Definition:
CREATE Procedure dbo.si_sp_XFERS ERVICE_update_x fer_profile
(
@program_name varchar(40),
@active char(1),
@batch_ts datetime
)
as

update css$steph.dbo.x fer_profile
set active = @active,
batch_ts = @batch_ts
where program_name = @program_name

Help please?

[mlafarlett]

I've confirmed, twice, that I'm running the same code and as for
pointing at a different DB, no way, the code would not even run if that
were true. My tables / stored procs are vastly different, meaning, I'm
referencing columns that do not even exist in prior environments and an
error stating so would be occurring. The 'subject' of this thread is
what's being returned as the error when attempting to pass the null.
I'm not sure where you're getting the 32 december thing...sorry if my
question is not clear.

[mlafarlett]

Ok..have narrowed it down some but still now solved...its complaing
about this statement.

prmBatchTsUpd.V alue = System.Data.Sql Types.SqlDateTi me.Null

keeping in mind i've also tried :
prmBatchTsUpd.V alue = System.DBNull.V alue
prmBatchTsUpd.V alue = System.Convert. DBNull

[Chris Dunaway]

Why don't you fire up SQL Profiler and see what is actually getting
submitted to the database.

I had this error once, but it turned out I was trying to update a
datetime column in the db with a datetime variable that had the value
DateTime.MinVal ue which is invalid for SQL Server.

Good luck

Chris