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
– Chetan, Commented Apr 3, 2018 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
– niksrb, Commented Apr 3, 2018 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
– Igor, Commented Apr 3, 2018 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
– Chetan, Commented Apr 3, 2018 at 12:51

Zohar Peled · Accepted Answer · 2018-04-03 12:52:38Z

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 3, 2018 at 12:52

answered Apr 3, 2018 at 12:49

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

niksrb Over a year ago

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

Zohar Peled Over a year ago

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.

GuidoG Over a year ago

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 remarksis an empty string

user2366842 Over a year ago

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.

Zohar Peled Over a year ago

@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...

|

Collectives™ on Stack Overflow

Inserting null value into DateTime field using DBValue.null

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related