SqlParameterCollection only accepts non-null SqlParameter type objects, not DBNull objects

Question

When I add the SQL parameter p to the collection I get an InvalidCastException with the message from the post title.

parentId is a nullable integer and a nullable integer in the database.

Why do I get this exception and how can I solve it?

I do not use stored procedures and I have read the similar threads but they did not help me.

var p = new SqlParameter("ParentId", SqlDbType.Int).Value = parentId ?? (object) DBNull.Value;
cmd.Parameters.Add(p);

Not sure why this is getting downvoted. It's a well written question with code that reproduces the issue. — vcsjones
– vcsjones, Commented Nov 30, 2012 at 21:58

user743382 · Accepted Answer · 2012-11-30 21:41:58Z

18

You aren't adding your new SqlParameter. p is the result of new SqlParameter("ParentId", SqlDbType.Int).Value = parentId ?? (object) DBNull.Value. In other words, p itself is DBNull.Value.

Split the statement in two, like so:

var p = new SqlParameter("ParentId", SqlDbType.Int);
p.Value = parentId ?? (object) DBNull.Value;
cmd.Parameters.Add(p);

Alternatively,

var p = new SqlParameter("ParentId", SqlDbType.Int) { Value = parentId ?? (object) DBNull.Value };
cmd.Parameters.Add(p);

Either would make sure p is the parameter, not the parameter's value.

answered Nov 30, 2012 at 21:41

user743382

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

4 Comments

phoog Over a year ago

Or you could just use parentheses: var p = new SqlParameter("ParentId", SqlDbType.Int).Value = (parentId ?? (object) DBNull)

user743382 Over a year ago

@phoog That would mean the same thing (well, if you add the .Value after DBNull), so still wouldn't work.

phoog Over a year ago

Oh yes, I see. It must be Friday afternoon.

Elisabeth Over a year ago

oh yes thank to the "var" keyword that happened also on Friday midnight here... Would I have use SqlParameter that would not have happened.

satnhak · Accepted Answer · 2012-11-30 21:42:30Z

-2

You need to use:

System.Data.SqlTypes.SqlInt32.Null

answered Nov 30, 2012 at 21:42

satnhak

9,9707 gold badges68 silver badges87 bronze badges

Collectives™ on Stack Overflow

SqlParameterCollection only accepts non-null SqlParameter type objects, not DBNull objects

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related