This topic is locked

Making DateTime null

10/29/2016 12:42:55 PM
ASPRunner.NET General questions
jadach authorDevClub member

On my add page before record added and edit page record updated before events I have this code and it works fine
if (values["Status"] == "1")

{

values["StatusDate"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

}

else

{

}
if (values["Status"] == "2")

{

values["StatusDate"] = "";

}

else

{

}
On the after record added event I have this and it works fine.
dynamic Order1 = GlobalVars.dal.Table("dbo.Order1");

Order1.Value["OrderID"] = values["OrderID"];

Order1.Value["Name"] = values["Name"];

Order1.Value["Title"] = values["Title"];

Order1.Value["Status"] = values["Status"];

Order1.Value["StatusDate"] = values["StatusDate"];

Order1.Add();
I have this on the after recor updated event and I cannot get the datetime field to be null on Order1 table. I tried "" and null.
if (values["Status"] == "1")

{

dynamic Order1 = GlobalVars.dal.Table("dbo.Order1");

Order1.Param["OrderID"] = values["OrderID"];

Order1.Value["Name"] = values["Name"];

Order1.Value["Title"] = values["Title"];

Order1.Value["Status"] = values["Status"];

Order1.Value["StatusDate"] = values["StatusDate"];

Order1.Update();

}

else

{

dynamic Order1 = GlobalVars.dal.Table("dbo.Order1");

Order1.Param["OrderID"] = values["OrderID"];

Order1.Value["Name"] = values["Name"];

Order1.Value["Title"] = values["Title"];

Order1.Value["Status"] = values["Status"];

Order1.Value["StatusDate"] = null;

Order1.Update();

}
If the value of Order1 had a valid datetime and if the user edits the record to empty that value, I cannot get it to be null. I get 1900-01-01 00:00:00.000 in my SQL Server table. The Order table does empty the value, but Order1 table does not.
Sorry for all this, but the more I put out here, the better chance I can get an assist.

jadach authorDevClub member 10/31/2016

Well, I did figure out a way to make my application work. I ended up using a case statement in table Order1 to hide 1900-01-01 00:00:00.000 right in the ASPR.Net SQL Editor. Seems to work fine.
CASE WHEN CONVERT(DATE, StatusDate) = '1900-01-01' THEN '' ELSE CONVERT(CHAR(10), StatusDate, 120) + ' ' + CONVERT(CHAR(8), StatusDate, 108) END AS StatusDate
Not sure if this is the best approach, but I have no other ways to achieve this at this time.

Pete K 11/10/2016

Jerry, did you try setting it to the null character? i.e.:

Order1.Value["StatusDate"] =(char)0
jadach authorDevClub member 11/10/2016

Hmmm, didn't try that but will. Thanks. I will post back my results.

jadach authorDevClub member 11/11/2016

char can't work because i am using datetime field.

Pete K 11/14/2016



char can't work because i am using datetime field.


Of course. I didn't think of that. What if you first set a string variable to null, then cast that as a datetime?