set a DateTime to NULL

[Guest]

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().Length > 0)
{
//We assume the data is a date
dt = (DateTime)datarow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Parameters.Add("@invoicedate", SqlDbType.NVarChar, 40).Value =
dt.ToShortDateString();

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

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().Length > 0)
{
//We assume the data is a date
dt = (DateTime)datarow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Parameters.Add("@invoicedate", SqlDbType.NVarChar, 40).Value =
dt.ToShortDateString();

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.IsNull("date"))
{
parameterValue = ((DateTime)datarow["date"]).ToShortDateString();
}
else
{
parameterValue = DBNull.Value;
}
cmdUpdate.Parameters.Add ("@invoicedate", SqlDbType.NVarChar,
parameterValue);

 

[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 returnNullIfBlank(object s)
{
if (s==null | s.ToString()=="" | s == (object)
System.DateTime.MinValue)
return System.Data.SqlTypes.SqlString.Null;
//return System.Data.SqlTypes.SqlDateTime.Null;
else
return s;
}


so you might want a line like this:
dt=System.Data.SqlTypes.SqlDateTime.Null

unlikely put you may possibly want this as a last resort:
(CAST_ME) System.Data.SqlTypes.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/

 

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

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().Length > 0)
{
//We assume the data is a date
dt = (DateTime)datarow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Parameters.Add("@invoicedate", SqlDbType.NVarChar, 40).Value =
dt.ToShortDateString();

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.IsNull("date"))
{
parameterValue = ((DateTime)datarow["date"]).ToShortDateString();
}
else
{
parameterValue = DBNull.Value;
}
cmdUpdate.Parameters.Add ("@invoicedate", SqlDbType.NVarChar,
parameterValue);

 

[Guest]

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

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().Length > 0)
{
//We assume the data is a date
dt = (DateTime)datarow["date"];
}
else
//We have no data in this column
{
dt = DBNull.Value; //this does not work
}

Then I set the param.

cmdUpdate.Parameters.Add("@invoicedate", SqlDbType.NVarChar, 40).Value =
dt.ToShortDateString();

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.IsNull("date"))
{
parameterValue = ((DateTime)datarow["date"]).ToShortDateString();
}
else
{
parameterValue = DBNull.Value;
}
cmdUpdate.Parameters.Add ("@invoicedate", SqlDbType.NVarChar,
parameterValue);

 

[Jon Skeet [C# MVP]]

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.