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

Eubanks

Not sure why this is getting downvoted. It's a well written question with code that reproduces the issue. – Lune

@Thanks vcsjones for helping me :) – Eubanks

Answer 1

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.

Condottiere

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

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

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

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

Answer 2

You need to use:

System.Data.SqlTypes.SqlInt32.Null

Postage