Understanding 'The SqlParameter is already contained by another SqlParameterCollection'

Question

I have the following piece of code:

SqlParameter invidParam = new SqlParameter("@invid",obj.INVID);
SqlParameter prodParam = new SqlParameter("@prod", obj.PROD);
SqlParameter descrParam = new SqlParameter("@descr", obj.DESCR);      
SqlParameter qtyParam = new SqlParameter("@qty", 1);

if(SessionHelper.TryGetSession("curUser",out User user))
{
    SqlParameter useridParamCheck = new SqlParameter("@userid", user.Username);

    var queryResult = db.Database.SqlQuery<int>("EXEC CheckIfAlreadyInPR#invid#userid @invid, @userid", invidParam, useridParamCheck);
    var qty = queryResult.FirstOrDefault();

    if (qty == 0)
    {
        SqlParameter useridParamInsert = new SqlParameter("@userid", user.Username);
        var insertResult = db.Database.ExecuteSqlCommand("InsertIntoPR @invid, @prod, @descr, @userid, @qty", invidParam, prodParam, descrParam, useridParamInsert, qtyParam);
    }
    else if (qty > 0)
    {
        SqlParameter qtyParameter = new SqlParameter("@newqty", (qty + 1));
        SqlParameter userIdParamUpdate = new SqlParameter("@userid", user.Username);

        var updateResult = db.Database.ExecuteSqlCommand("UpdatePRQty#invid#userid#newqty @invid,@userid,@newqty",invidParam,userIdParamUpdate,qtyParameter);
    }
}

My program runs and hits an exception at the line var insertResult = db...

I am trying to understand which SqlParameter is already contained in another collection. Is it because the parameter @userid is already used in the previous stored procedure call ?

I declared the SqlParameter("@userid") 3 times, with different variable names:

useridParamCheck, useridParamInsert, userIdParamUpdate

Did this cause the error because the name @userid is used? If I use @userid1, @userid2, @userid3 will the problem be resolved? Or the SqlParameter that is already contained is some other SQL parameters that I declared above?

Thank you

Possible duplicate of The SqlParameter is already contained by another SqlParameterCollection - Does using() {} cheat? — SehaxX
– SehaxX, Commented Jul 3, 2018 at 8:51
You can't use the same instance of SqlParamater for two different sql-commands. Look for example at invidParam — Tim Schmelter
– Tim Schmelter, Commented Jul 3, 2018 at 9:03

Aaron · Accepted Answer · 2018-07-03 09:00:07Z

3

I'm going to suggest that invidparam is your problem parameter as it is the only parameter I can see that is used twice. Once in db.Database.SqlQuery<int>("EXEC CheckIfAlreadyInPR#invid#userid @invid, @userid", invidParam, useridParamCheck); and again in both instances of db.Database.ExecuteCommand().

The error itself relates to an SqlParameter object being used multiple times rather than the same parameter name being used multiple times.

answered Jul 3, 2018 at 9:00

Aaron

1853 silver badges11 bronze badges

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

Comments

Kirsten · Accepted Answer · 2020-10-21 07:37:26Z

0

I was getting this error with the following code

  var p = new System.Data.SqlClient.SqlParameter{ParameterName = "@p0", SqlDbType 
  SqlDbType.DateTime ,Value = ut.ReadyByDate, };
  var nextTasksResults = db.Database.SqlQuery<nextTask>(sql2, p);
  var isAny = nextTaskResults.Any() // error

The work around was to send to an array and use that

  var nextTasks = nextTasksResults.ToArray();
  var isAny = nextTasks.Any();

answered Oct 21, 2020 at 7:37

Kirsten

18.8k52 gold badges213 silver badges368 bronze badges

Collectives™ on Stack Overflow

Understanding 'The SqlParameter is already contained by another SqlParameterCollection'

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related