set a DateTime to NULL

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
I want to set a DateTime field to Null before passing it to the DB
Click to expand...

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();
Click to expand...

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.
Click to expand...

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);
 
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
 
You just can't do that. A DateTime can't be null. It can't be
DBNull.Value, either (which is something different).
Click to expand...

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 said:
I want to set a DateTime field to Null before passing it to the DB
Click to expand...

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();
Click to expand...

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.
Click to expand...

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);
Click to expand...
 
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 said:
I want to set a DateTime field to Null before passing it to the DB
Click to expand...

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();
Click to expand...

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.
Click to expand...

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);
Click to expand...
 
Abubakar said:
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?
Click to expand...

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.
 
Back
Top