The SqlParameter is already contained by another SqlParameterCollection.

Yesterday, I came across a odd bug while debugging some code for a proof of concept application.

The code itself was simple connecting to the database, executing two queries using different SqlCommand objects, but the same SqlParameter collection and returning the results to the user.

The method itself look two parameters, a SQL query as a string and a SQL Parameter[]. I would then simply add the parameters to the SqlCommand:

SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
sqlCmd.Parameters.AddRange(queryParams);

 

After executing I would close the connection, then code would do some other stuff and a different query would be executed but using the same parameter collection.  In theory, there is no reason why this wouldn’t work however the code was throwing an exception with the error “The SqlParameter is already contained by another SqlParameterCollection.”  How odd, I had closed the connection and it shouldn’t be referred so I should be able to add it.

Turns out, or at least I think, that the garage collection isn’t kicking in quick enough and so the reference is still alive.  Quick fix was to clear the parameters on the SqlCommand object after execution.

sqlCmd.Parameters.Clear();

 

Tripped me up for a little bit so I thought I would post.  Carry on…

 

 

Technorati tags: ,

15 thoughts on “The SqlParameter is already contained by another SqlParameterCollection.”

  1. You know, I would think this might work in the other direction: I have a sqlParameter that I want to use more than once–with its value intact–for a parent/child relation. I’m just inserting the value in two different query operations. Once I go to re-use the param in the child, however, I get this error. Odd.

    Thanks much for this post: it’s been helpful!

  2. Wow, cool. Saved me a lot of time. So much abstractions, at last you forget about the things behind the whole system, like the Garbage Collector.

  3. Well….this is not solving my probelm.. I have cmd parameters on framework level which is used as generice method and get called from outside world. Hence, I can’t change using command parameters (cmd.parameters.clear())…

    Is there any other way to play around this…. thanks

Leave a Reply

Your email address will not be published. Required fields are marked *