Why this SqlParameterCollection call is throwing error?

Question

This is code sample to call a stored procedure. Second process throws InvalidCastException at runtime. I am trying to add a new SqlParameter into the SqlParametersCollection. I have seen lots of example using the same syntax.

using (SqlCommand db = new SqlCommand("[StoredProcedureName]"))
{
    db.CommandType = CommandType.StoredProcedure;
    db.Connection = new SqlConnection(WebConfigurationAccess.ConnectionString);

    //1) works but this is long so wanted to try shortcut
    db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int));
    db.Parameters["@pID"].Value = 12345;
    //2) <<>>throws InvalidCastExpception. Id is set up as Int32
    db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int).Value = 12345);
    //3)but following codes does the job
    db.Parameters.Add("@pID", SqlDbType.Int).Value = 12345;
    db.Connection.Open();
    var dr = db.ExecuteReader();
}

Are you trying to figure out how to solve the problem or just why a certain syntax fails? — Gregory A Beamer, Aug 06 '15 at 19:00
@RonBeyer: Constructors *always* return values. That code is syntactically valid. — Jon Skeet, Aug 06 '15 at 19:00
Sorry @JonSkeet I was thinking of something else, I removed it when I realized what junk my fingers spewed out. — Ron Beyer, Aug 06 '15 at 19:01

score 3 · Accepted Answer · answered Aug 06 '15 at 18:59

I suspect you haven't seen that exact syntax. Look at what you've got:

db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int).Value = 12345);

That's calling db.Parameters.Add with an int argument. I suspect what you've actually seen is:

db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int)).Value = 12345;

Or more simply:

db.Parameters.Add("@pID", SqlDbType.Int).Value = 12345;

In both cases, the int is only used to set the Value property on the parameter, after it's been created and added to the collection.

Your code only compiles because there's a SqlParameterCollection.Add(object) overload - which should basically never be used, as far as I can see.

I thought I was creating a parameter, then assign value then add it to parameters collection. Thank you @JonSkeet — DevelopZen, Aug 06 '15 at 19:09

Why this SqlParameterCollection call is throwing error?

1 Answers1