cannot insert null for date using dictionary

Question

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;

DateTime is not a nullable type. If you don't supply a value it's equal to DateTime.MinValue that is why it is taking 01/01/1900 and inserting in the table. — AsthaUndefined, Apr 17 '18 at 05:06
Possible duplicate of [DateTime "null" value](https://stackoverflow.com/questions/221732/datetime-null-value) — AsthaUndefined, Apr 17 '18 at 05:07
Note that checking whether the result of a `ToString()` call is `null` is pretty much *never* going to work. But it's hard to help without knowing more details - what is the type of `edu.Date_Issue`? — Jon Skeet, Apr 17 '18 at 06:19

Abollo · Accepted Answer · 2018-04-17T06:37:52.640

3

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());

edited Apr 17 '18 at 06:37

answered Apr 17 '18 at 06:19

Abollo

66
7

1

I'd argue that converting to a string at all is likely to be a bad idea, to be honest. (It doesn't help that we don't even know the type of `Date_Issue` for sure.) – Jon Skeet Apr 17 '18 at 06:20
I knew that `Date_Issue` is string from the question based on the way he's trying to assign it with string values, `xmt.Add("@Date_Issue", edu.Date_Issue.ToString() == null ? "Null" : edu.Date_Issue.ToString());` – Abollo Apr 17 '18 at 06:26
2

That never treats `edu.Date_Issue` as a string. The second and third operands of the conditional operator are both string because the third one involves another `ToString()` call. If you believe the type of `Date_Issue` is `string`, why include `ToString()` calls *anywhere*? – Jon Skeet Apr 17 '18 at 06:37
1

But in case that it's of a different type then yes you are correct there is no need to make the conversion to string, I've edited my answer to make it more generic. – Abollo Apr 17 '18 at 06:38
1

correct, I thought you were speaking about the dictionary member `Date_Issue` not the `edu.Date_Issue`. I've edited my answer accordingly. Thanks. – Abollo Apr 17 '18 at 06:40
Except I strongly suspect that the type of `Date_Issue` is `DateTime`, but the OP hasn't responded to my comment asking for more information. I would urge the OP to avoid using a string representation of the `DateTime` *anywhere* except the UI if at all possible, and just calling `ToString()` is a really unreliable way of getting it into a suitable form for a database. Fundamentally I don't think we can give really good advice without knowing more information here. – Jon Skeet Apr 17 '18 at 06:43

score 2 · Answer 2 · answered Apr 17 '18 at 05:17

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

cannot insert null for date using dictionary

2 Answers2