C# INSERT to SQL method exception when DateTime parameter is null

Question

One of my parameter looks like this:

cmd.Parameters.Add("@last_sign_in_at", SqlDbType.DateTime).Value = user.last_sign_in_at;

I know that User has null value in last_sign_in_at. Database accept nulls for this column but I get an exception:

SqlException: The parameterized query '(@id int,@name nvarchar(18),@username nvarchar(11),@state nvarch' expects the parameter '@last_sign_in_at', which was not supplied.

Tip: if you use Dapper, it get things like this right for you, and avoids a lot of code: connection.Execute(@"...SQL...", new { user.last_sign_in_at }); — Marc Gravell
– Marc Gravell, Commented Nov 3, 2020 at 16:57

Nikita Chayka · Accepted Answer · 2020-11-03 16:42:33Z

5

You should be using DBNull.Value:

cmd.Parameters.Add("@last_sign_in_at", SqlDbType.DateTime).Value = (object)user.last_sign_in_at ?? (object)DBNull.Value;

Casting to object here is just for operator ?? to not complain about different types

edited Nov 3, 2020 at 16:42

answered Nov 3, 2020 at 15:35

Nikita Chayka

2,14512 silver badges16 bronze badges

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

4 Comments

aks Over a year ago

Operator '??' cannot be applied to operands of type 'DateTime?' and 'DBNull'

Nikita Chayka Over a year ago

expected that, you could use (object)user.last_sign_in_at?? (object)DBNull.Value

aks Over a year ago

I used cmd.Parameters.Add("@last_sign_in_at", SqlDbType.DateTime).Value = user.last_sign_in_at ?? DateTime.Today;

Nikita Chayka Over a year ago

but that's not null, what do you mean? You inserted Today's time, you should use DBNull.Value, that's also obviously if your column is Nullable in SQL Table

Jason Goemaat · Accepted Answer · 2020-11-03 15:37:29Z

1

When using raw .net framework, I've always had to use the value DBNull.Value.

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

answered Nov 3, 2020 at 15:37

Jason Goemaat

29.3k15 gold badges89 silver badges119 bronze badges

1 Comment

aks Over a year ago

but user.last_sign_in_at is not always NULL but when it's null method trows an exception

Collectives™ on Stack Overflow

C# INSERT to SQL method exception when DateTime parameter is null

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related