Inserting null value into DateTime field using DBValue.null

Question

Next code is giving me an exception:

cmd.CommandText = @"insert Table ";
cmd.CommandText += @"(StartTime,
                      EndTime)
               values(@StartTime,
                      @EndTime)
                      SELECT CAST(scope_identity() AS int)";
cmd.Parameters.AddWithValue ( "@StartTime", DBNull.Value );
cmd.Parameters.AddWithValue ( "@EndTime", DBNull.Value );
cmd.ExecuteScalar();

The exception I am getting is Must declare '@StartTime' variable and same thing for @EndTime. Isn't the DBNull.Value used for things like this, what am I doing wrong?

Two things, 1. You need to do `INSERT INTO TABLE` 2. There are not spaces between `table` and `(starttime`, `EndTime)` and `values`, `@EndTime) and `SELECT` — Chetan, Apr 03 '18 at 12:46
@ChetanRanpariya As Zohar said, `INTO` is optional. And also, I've got these spaces, this is just sample of the real code, so that's not the problem... — niksrb, Apr 03 '18 at 12:49
I can't see how the above code would throw that exception. Are you sure that the version throwing the exception is the compiled code you have posted above? Are there any mis-typed words like `@StarTime` in your actual code? Try to debug further by using `Sql Profiler` to capture the actual prepared statement that is executed along with the sent parameters. — Igor, Apr 03 '18 at 12:51
Agree with Zohar. Is you query generated as `"insert Table (StartTime, EndTime) values(@StartTime, @EndTime) SELECT CAST(scope_identity() AS int)"` ? — Chetan, Apr 03 '18 at 12:51
AddWithValue will leave out the entire parameter if the value is NULL. For some reason microsoft has found this logical. Use the answer of @ZoharPeled instead — GuidoG, Apr 03 '18 at 12:59
The closest thing I could find was this but this assumes that `null` is being passed and not `DBNull.Value`. Doing this would cause the above exception. [Exception when AddWithValue parameter is NULL](https://stackoverflow.com/questions/13451085) — Igor, Apr 03 '18 at 13:02
Why don't you make a stored procedure for your insert instead of putting all your sql in the application? You can have all the same functionality but separate layers. Especially since you say this a big shop you need to start doing this. — Sean Lange, Apr 03 '18 at 13:16

Zohar Peled · Accepted Answer · 2018-04-03T12:52:38.050

5

I think the reason is the fact you are using AddWithValue. You see, AddWithValue have to infer the data type of the parameter from the value (and meta data, if exists). When you use DBNull.Value and an inline SQL (as apposed to a stored procedure), there is simply no way to infer the data type.

Change the AddWithValue to Add:

cmd.Parameters.Add("@StartTime", SqlDbType.DateTime).Value = DBNull.Value;
cmd.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = DBNull.Value

For more information, read Can we stop using AddWithValue() already?

edited Apr 03 '18 at 12:52

answered Apr 03 '18 at 12:49

Zohar Peled

79,642
10
69
121

I am doing same thing in the other part of code and it's working tottaly fine. As I said those two fields `@StartTime` and `@EndTime` are bouth of `DateTime` format. So `AddWithValue` is working for all other types except `DateTime`. Not sure why – niksrb Apr 03 '18 at 12:52
1

Well, if you read the article in the link you'll see it's bad practice to use `AddWithValue` altogether. Give it a go, if it doesn't help, post a comment here so we will know. If it does help, you should stop using `AddWithValue` and accept this answer. – Zohar Peled Apr 03 '18 at 12:54
for string value I like this better `command.Parameters.Add(new SqlParameter("@Remarks", SqlDbType.VarChar) { Value = (remarks == "") ? (object)DBNull.Value : remarks });` This will put NULL in stead of an empty varchar in your database in case the string variable `remarks`is an empty string – GuidoG Apr 03 '18 at 12:55
I'd personally be inclined to at least read and try what someone with 40k rep throws out as an answer. Usually people with that much rep have a solid grip on what they're suggesting. – user2366842 Apr 03 '18 at 12:56
@GuidoG first, we are talking about `DateTime`. Second, it's easier with `cmd.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = (remarks == "") ? (object)DBNull.Value : remarks;` – Zohar Peled Apr 03 '18 at 13:00
@user2366842 usually being the key word here... I've seen people with over 100K that get most of their reputation from questions, not answers. This doesn't mean they don't know what they are talking about, it just means that reputation is not the only thing to consider on stackoverflow. In any case, thanks for your support. – Zohar Peled Apr 03 '18 at 13:06
@ZoharPeled Of course I am going to listen someone who is better then me, I don't even dare to say that you are not right here. It's just that I don't have that much freedom in changing the code as I am working in a big company and I should type as much similar code as possible with other programmers. I have tried your way, but still getting the same error. Check it out: http://prntscr.com/j08vb0 – niksrb Apr 03 '18 at 13:10
1

@niksrb Why do you think I'm better than you? I'm only here a longer time... as I wrote to user2366842 - reputation is not the only thing to look for in stackoverflow. In the image you posted, you are adding the `@startDate` and `@endDate` to a different command - you execute `cmdRtrBizDayInfEvntIn` but use `Parameters.Add` on `cmdRtrBizDayInf`... – Zohar Peled Apr 03 '18 at 13:15
Oh my god, that's right. Stupid copy/paste. Now it works with `DBValue.Null` even it's not best solution. Thank you, I fill ashamed now... – niksrb Apr 03 '18 at 13:21
Nothing to be ashamed about... every programmer I know fell into the copy-paste trap at least once or twice. Some of us still do it... – Zohar Peled Apr 03 '18 at 13:25

Inserting null value into DateTime field using DBValue.null

1 Answers1