468,268 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,268 developers. It's quick & easy.

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

Alright..I've found lots of info on this error and most say assign
System.DBNull.Value, System.Convert.DBNull, or
System.Data.SqlTypes.SqlDateTime.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.ParameterName = "@batch_ts"
prmBatchTsUpd.SqlDbType = SqlDbType.DateTime
prmBatchTsUpd.SourceColumn = "batch_ts"
sqlUpdateProCmd.Parameters.Add(prmBatchTsUpd)

prmBatchTsUpd.Value = System.Data.SqlTypes.SqlDateTime.Null

sqlUpdateProCmd.ExecuteNonQuery()

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

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

Help please?

Mar 30 '06 #1
5 34927
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********@yahoo.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...
Alright..I've found lots of info on this error and most say assign
System.DBNull.Value, System.Convert.DBNull, or
System.Data.SqlTypes.SqlDateTime.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.ParameterName = "@batch_ts"
prmBatchTsUpd.SqlDbType = SqlDbType.DateTime
prmBatchTsUpd.SourceColumn = "batch_ts"
sqlUpdateProCmd.Parameters.Add(prmBatchTsUpd)

prmBatchTsUpd.Value = System.Data.SqlTypes.SqlDateTime.Null

sqlUpdateProCmd.ExecuteNonQuery()

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

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

Help please?

Mar 30 '06 #2
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********@yahoo.com> schreef in bericht
news:11*********************@g10g2000cwb.googlegro ups.com...
Alright..I've found lots of info on this error and most say assign
System.DBNull.Value, System.Convert.DBNull, or
System.Data.SqlTypes.SqlDateTime.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.ParameterName = "@batch_ts"
prmBatchTsUpd.SqlDbType = SqlDbType.DateTime
prmBatchTsUpd.SourceColumn = "batch_ts"
sqlUpdateProCmd.Parameters.Add(prmBatchTsUpd)

prmBatchTsUpd.Value = System.Data.SqlTypes.SqlDateTime.Null

sqlUpdateProCmd.ExecuteNonQuery()

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

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

Help please?

Mar 30 '06 #3
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.

Mar 30 '06 #4
Ok..have narrowed it down some but still now solved...its complaing
about this statement.

prmBatchTsUpd.Value = System.Data.SqlTypes.SqlDateTime.Null

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

Mar 30 '06 #5
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.MinValue which is invalid for SQL Server.

Good luck

Chris

Mar 31 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by FrodoBaggins | last post: by
1 post views Thread by poppy | last post: by
1 post views Thread by Paperback Writer | last post: by
3 posts views Thread by baburk | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.