Trying to run Insert with SqlParameters with EF, getting scalar variable error, How to fix?

Question

I am using EF 6 with MVC5/C#

I am trying to run an insert statement, using SqlParameter objects, via ExecuteStoreCommand over EF. I am doing it this way as I have some performance issues with AddObject.

My code is:

string insertCmd = "INSERT INTO TABLE (Value,FkId) VALUES (@Value,@FkId)";

var paramList = new[]{
    new SqlParameter("@Value", SqlDbType.VarChar).Value = value,
    new SqlParameter("@FkId", SqlDbType.Int).Value = fkId
};

db.ExecuteStoreCommand(insertCmd, paramList);

I am getting the error:

Must declare the scalar variable "@Value". Statement(s) could not be prepared.

I have noticed that it always complains about the first column in the Insert statement regardless of type. So if I swapped it around I would get:

Must declare the scalar variable "@FkId". Statement(s) could not be prepared.

Thoughts ?

EDIT:

Just noticed that the record gets inserted and saved to the DB, yet I still get this error. Puzzled...

StuartLC · Accepted Answer · 2019-04-08 07:23:14Z

2

If you look really carefully at the type inferred by the compiler for the variable:

var paramList = new[]
{
    new SqlParameter("@Value", SqlDbType.VarChar).Value = "SomeValue",
    new SqlParameter("@FkId", SqlDbType.Int).Value = 123
};

You'll see that paramList is a an Object[2] with the two scalar values Value(string) and 123(int), and the SqlParameters are lost altogether, because the result of an assignment is the assigned value! Object is inferred by the compiler as the lowest common denominator of string and int.

Unfortunately the call to ExecuteStoreCommand won't give you any warning that you aren't passing SqlParameter[], since it accepts the very weak type params object[] as per the docs:

The parameters value can be an array of DbParameter objects or an array of parameter values

To solve this, you can either use one of the SqlParameter constructors which take on the value directly, e.g.

var paramList = new[]
{
    new SqlParameter("@Value", "SomeValue"),
    new SqlParameter("@FkId", 123)
};

Or create the parameters, assign them, and then add to the array.

Out of interest, ExecuteStoreCommand is a method on ObjectContext, not DbContext.

There's an equivalent method DbContext.Database.ExecuteSqlCommand which should do the same thing.

edited Apr 8, 2019 at 7:23

answered Apr 7, 2019 at 14:09

StuartLC

108k18 gold badges225 silver badges295 bronze badges

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

1 Comment

SamJolly Over a year ago

Just tried your first suggestion, and it worked. Really love your very comprehensive reply. I see the Object Type now ! So grateful.

Collectives™ on Stack Overflow

Trying to run Insert with SqlParameters with EF, getting scalar variable error, How to fix?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related