cannot insert null for date using dictionary

I'm trying to insert record using C# and SQL, but i got this error when date column is empty, i searched for a similar cases but didnt solve it yet. It inserts 01/01/1900, the column in table is Datetime any idea or similar case link.

[HttpPost]
public HttpResponseMessage save (Education edu)
{
    Dictionary<string, object> xmt = new Dictionary<string, object>();

    xmt.Add("@Staff_Key", edu.Staff_Key);
    xmt.Add("@Type_Education", edu.Type_Education);
    xmt.Add("@Qual", edu.Qual);
    xmt.Add("@Uni", edu.Uni);
    xmt.Add("@Date_Issue", edu.Date_Issue.ToString() == null ? "Null" : edu.Date_Issue.ToString());
    xmt.Add("@Notes", edu.Notes);

    obj.ExecNonQuery(
       @"insert into HR_DocEducation (Staff_Key,Type_Education,Qual,Uni,Date_Issue,Notes)
       values (@Staff_Key,@Type_Education,@Qual,@Uni,@Date_Issue,@Notes) ",xmt);

    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created);
    return response;

2 answers

  • answered 2018-04-17 04:18 Cataklysim

    As said by Astha Srivastava, you can find a short description of DateTime's behavior on null values.

    Question: DateTime “null” value

    A possible way is to use the propertie DBNull.Value which is referencing to a value used on databases. I can recommend to use that when using SQL Commands.

    After a short lookup I found a similar question of yours, using the DBNull.Value as example. Look it up and if it might help you give the answer a upvote.

    Question: Nullable DateTime and the Database

  • answered 2018-04-17 04:18 Abollo

    Hassan, I had the same issue, the trick in addition to use the nullable DateTime DateTime? object is correcting the ternary operator as follows:

    xmt.Add("@Date_Issue", edu.Date_Issue == null ? (object) DBNull.Value : (object)edu.Date_Issue);
    

    instead of

    xmt.Add("@Date_Issue", edu.Date_Issue.ToString() == null ? "Null" : edu.Date_Issue.ToString());