set a DateTime to NULL

[Paulb1us]

I want to set a DateTime field to Null before passing it to the DB

//First I check to see if anything is in this datarow column, because
sometimes we have no data.
DateTime dt;
if ( datarow["date"].ToString().Len gth > 0)
{
//We assume the data is a date
dt = (DateTime)datar ow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Param eters.Add("@inv oicedate", SqlDbType.NVarC har, 40).Value =
dt.ToShortDateS tring();

If there is a date in 'datarow["date"]' everything works fine, but if there
is no data it throws an exception. So I set up the if statement to check for
something in that column, if there is something I assume it's a date and
assign it to dt. But if there no data I want to asign nothing to dt.

Thanks

Paul

[Jon Skeet [C# MVP]]

Pa******@newsgr oup.nospam <Pa******@newsg roup.nospam> wrote:

I want to set a DateTime field to Null before passing it to the DB
You just can't do that. A DateTime can't be null. It can't be
DBNull.Value, either (which is something different).
//First I check to see if anything is in this datarow column, because
sometimes we have no data.
DateTime dt;
if ( datarow["date"].ToString().Len gth > 0)
{
//We assume the data is a date
dt = (DateTime)datar ow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Param eters.Add("@inv oicedate", SqlDbType.NVarC har, 40).Value =
dt.ToShortDateS tring();
Yikes - is the field in the database really a string rather than a
date-related type? That's nasty.
If there is a date in 'datarow["date"]' everything works fine, but if there
is no data it throws an exception. So I set up the if statement to check for
something in that column, if there is something I assume it's a date and
assign it to dt. But if there no data I want to asign nothing to dt.

You can't do that. What you can do is:

object parameterValue;

if (!datarow.IsNul l("date"))
{
parameterValue = ((DateTime)data row["date"]).ToShortDateSt ring();
}
else
{
parameterValue = DBNull.Value;
}
cmdUpdate.Param eters.Add ("@invoicedate" , SqlDbType.NVarC har,
parameterValue) ;

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[dwight0]

I also had to use an if statement. I actually wrote a function to do it
for me. I know there has to be a a cleaner way but i couldnt find one.
Heres mine:

private object returnNullIfBla nk(object s)
{
if (s==null | s.ToString()==" " | s == (object)
System.DateTime .MinValue)
return System.Data.Sql Types.SqlString .Null;
//return System.Data.Sql Types.SqlDateTi me.Null;
else
return s;
}
so you might want a line like this:
dt=System.Data. SqlTypes.SqlDat eTime.Null

unlikely put you may possibly want this as a last resort:
(CAST_ME) System.Data.Sql Types.SqlString .Null
-Dwight
http://logical4.com

[Sahil Malik]

..NET 1.1 atleast does not support nullable value types. Even with .NET 2.0,
nullable value types donot fully solve this problem. You have to use the
DBNull structure to get around this issue as Jon explained.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
<Pa******@newsg roup.nospam> wrote in message
news:DF******** *************** ***********@mic rosoft.com...

I want to set a DateTime field to Null before passing it to the DB

//First I check to see if anything is in this datarow column, because
sometimes we have no data.
DateTime dt;
if ( datarow["date"].ToString().Len gth > 0)
{
//We assume the data is a date
dt = (DateTime)datar ow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Param eters.Add("@inv oicedate", SqlDbType.NVarC har, 40).Value =
dt.ToShortDateS tring();

If there is a date in 'datarow["date"]' everything works fine, but if
there
is no data it throws an exception. So I set up the if statement to check
for
something in that column, if there is something I assume it's a date and
assign it to dt. But if there no data I want to asign nothing to dt.

Thanks

Paul

[Abubakar]

> You just can't do that. A DateTime can't be null. It can't be

DBNull.Value, either (which is something different).
yea , and just to confirm that it is because that value types cant be null
and they are always initialized to there respective default values, unless
they accept a constructor for custom initial values. Right?

Ab.

"Jon Skeet [C# MVP]" <sk***@pobox.co m> wrote in message
news:MP******** *************** @msnews.microso ft.com... Pa******@newsgr oup.nospam <Pa******@newsg roup.nospam> wrote:

I want to set a DateTime field to Null before passing it to the DB

You just can't do that. A DateTime can't be null. It can't be
DBNull.Value, either (which is something different).

//First I check to see if anything is in this datarow column, because
sometimes we have no data.
DateTime dt;
if ( datarow["date"].ToString().Len gth > 0)
{
//We assume the data is a date
dt = (DateTime)datar ow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Param eters.Add("@inv oicedate", SqlDbType.NVarC har, 40).Value =
dt.ToShortDateS tring();

Yikes - is the field in the database really a string rather than a
date-related type? That's nasty.

If there is a date in 'datarow["date"]' everything works fine, but if there is no data it throws an exception. So I set up the if statement to check for something in that column, if there is something I assume it's a date and
assign it to dt. But if there no data I want to asign nothing to dt.

You can't do that. What you can do is:

object parameterValue;

if (!datarow.IsNul l("date"))
{
parameterValue = ((DateTime)data row["date"]).ToShortDateSt ring();
}
else
{
parameterValue = DBNull.Value;
}
cmdUpdate.Param eters.Add ("@invoicedate" , SqlDbType.NVarC har,
parameterValue) ;

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Paulb1us]

Thank you Jon,

That works just fine.

I know the DB field should be Date and not string but in this certain
instance it needs to be a string.

Thank you for your help.

Paul

"Jon Skeet [C# MVP]" wrote:

Pa******@newsgr oup.nospam <Pa******@newsg roup.nospam> wrote:

I want to set a DateTime field to Null before passing it to the DB

You just can't do that. A DateTime can't be null. It can't be
DBNull.Value, either (which is something different).

//First I check to see if anything is in this datarow column, because
sometimes we have no data.
DateTime dt;
if ( datarow["date"].ToString().Len gth > 0)
{
//We assume the data is a date
dt = (DateTime)datar ow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Param eters.Add("@inv oicedate", SqlDbType.NVarC har, 40).Value =
dt.ToShortDateS tring();

Yikes - is the field in the database really a string rather than a
date-related type? That's nasty.

If there is a date in 'datarow["date"]' everything works fine, but if there
is no data it throws an exception. So I set up the if statement to check for
something in that column, if there is something I assume it's a date and
assign it to dt. But if there no data I want to asign nothing to dt.

You can't do that. What you can do is:

object parameterValue;

if (!datarow.IsNul l("date"))
{
parameterValue = ((DateTime)data row["date"]).ToShortDateSt ring();
}
else
{
parameterValue = DBNull.Value;
}
cmdUpdate.Param eters.Add ("@invoicedate" , SqlDbType.NVarC har,
parameterValue) ;

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

Abubakar <em**********@y ahoo.com> wrote:

You just can't do that. A DateTime can't be null. It can't be
DBNull.Value, either (which is something different).

yea , and just to confirm that it is because that value types cant be null
and they are always initialized to there respective default values, unless
they accept a constructor for custom initial values. Right?

It's not really due to custom initial values - it's because null is a
reference, and the value of a value type isn't a reference, so can't be
null.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too