12

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);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Elisabeth
  • 20,496
  • 52
  • 200
  • 321

2 Answers2

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.

  • 1
    Or you could just use parentheses: `var p = new SqlParameter("ParentId", SqlDbType.Int).Value = (parentId ?? (object) DBNull)` – phoog Nov 30 '12 at 21:43
  • @phoog That would mean the same thing (well, if you add the `.Value` after `DBNull`), so still wouldn't work. –  Nov 30 '12 at 21:44
  • Oh yes, I see. It must be Friday afternoon. – phoog Nov 30 '12 at 21:45
  • oh yes thank to the "var" keyword that happened also on Friday midnight here... Would I have use SqlParameter that would not have happened. – Elisabeth Nov 30 '12 at 21:54
-2

You need to use:

System.Data.SqlTypes.SqlInt32.Null
satnhak
  • 9,407
  • 5
  • 63
  • 81