initializing sql date variables issue...

[Brad Pears]

I am working on a vb.net 2005 project using sql server 2000 as the backend .

I am having a bit of problems with date variables... Here is the scenario...

I have a table that includes a couple "smalldatetime" fields.

In my class for this table, when inserting new or saving a row in the table,
both of these date values may possibly be empty. (ie I am allowing nulls on
the db side... )

On an initial insert what is happening is that in my class I have a property
defined as type 'Date'. This 'Date' variable's value (field called
"dtOfferValidUntil") is then used in the stored procedure that inserts or
updates the table as a passed in parameter. What I have discovered is that a
"date" field's initiall value is something along these lines... "#12:00:00
AM#". So, when my stored procedure executes and an attempt is made to set my
smalldatetime field to this value, an error is generated that says
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999
11:59:59 PM".

My question is this... I want the date property in my class to be initially
set to null somehow so that the date being passed into the stored procedure
will just be null and not "#12:00:00 AM#"... In fact, what I want is to do a
check for a valid date in my class and if it is a valid date, then I will
pass that value onto the stored procedure - otherwise I want to set the date
variable to null and pass that value in. My SQL stored procedure checks for
null values before updating the field. If it is null, then the original
value is left there. If it is not null then the field is updated.

What is the best way to go about doing this? I hate dates...

Thanks, Brad

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

Brad,

One option is to use Nullable (Of DateTime).

Kerry Moorman
"Brad Pears" wrote:

I am working on a vb.net 2005 project using sql server 2000 as the backend .

I am having a bit of problems with date variables... Here is the scenario...

I have a table that includes a couple "smalldatetime" fields.

In my class for this table, when inserting new or saving a row in the table,
both of these date values may possibly be empty. (ie I am allowing nulls on
the db side... )

On an initial insert what is happening is that in my class I have a property
defined as type 'Date'. This 'Date' variable's value (field called
"dtOfferValidUntil") is then used in the stored procedure that inserts or
updates the table as a passed in parameter. What I have discovered is that a
"date" field's initiall value is something along these lines... "#12:00:00
AM#". So, when my stored procedure executes and an attempt is made to set my
smalldatetime field to this value, an error is generated that says
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999
11:59:59 PM".

My question is this... I want the date property in my class to be initially
set to null somehow so that the date being passed into the stored procedure
will just be null and not "#12:00:00 AM#"... In fact, what I want is to do a
check for a valid date in my class and if it is a valid date, then I will
pass that value onto the stored procedure - otherwise I want to set the date
variable to null and pass that value in. My SQL stored procedure checks for
null values before updating the field. If it is null, then the original
value is left there. If it is not null then the field is updated.

What is the best way to go about doing this? I hate dates...

Thanks, Brad

[Brad Pears]

Can you give me a few lines of code using this?? Just not 100% sure what you
mean...

Thanks!

Brad
"Kerry Moorman" <Ke**********@discussions.microsoft.comwrote in message
news:11**********************************@microsof t.com...

Brad,

One option is to use Nullable (Of DateTime).

Kerry Moorman
"Brad Pears" wrote:

>I am working on a vb.net 2005 project using sql server 2000 as the
backend .

I am having a bit of problems with date variables... Here is the
scenario...

I have a table that includes a couple "smalldatetime" fields.

In my class for this table, when inserting new or saving a row in the
table,
both of these date values may possibly be empty. (ie I am allowing nulls
on
the db side... )

On an initial insert what is happening is that in my class I have a
property
defined as type 'Date'. This 'Date' variable's value (field called
"dtOfferValidUntil") is then used in the stored procedure that inserts or
updates the table as a passed in parameter. What I have discovered is
that a
"date" field's initiall value is something along these lines...
"#12:00:00
AM#". So, when my stored procedure executes and an attempt is made to set
my
smalldatetime field to this value, an error is generated that says
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and
12/31/9999
11:59:59 PM".

My question is this... I want the date property in my class to be
initially
set to null somehow so that the date being passed into the stored
procedure
will just be null and not "#12:00:00 AM#"... In fact, what I want is to
do a
check for a valid date in my class and if it is a valid date, then I will
pass that value onto the stored procedure - otherwise I want to set the
date
variable to null and pass that value in. My SQL stored procedure checks
for
null values before updating the field. If it is null, then the original
value is left there. If it is not null then the field is updated.

What is the best way to go about doing this? I hate dates...

Thanks, Brad