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…
Thanks, it saved me from tearing up my hears…
Thanks for the post. I suffered the same problem, but your solution saved me tons o’ time.
Thanks a ton.
Really thanks for your tip!!!!
Thanks so much for the tip. Saved me a lot of work.
Thanks for posting. Now that I know it’s trivial but you definitely saved me a ton of frustration.
Thanks for the post. I came across the same problem and this post was very useful and saved up my time.
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!
Thank you. It saved lot of time.
Another “thanks” message for you–saved me time which is so valuable.
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.
Found this via google for the error message. You saved me tons of time. Thanks.
Thank you. Cant imagine how much time this jus saved me!
Please do this as you run into future problems / oddities !
/JoLu
Awesome, thx
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
Thank you, Ben. This saved me a whole lot of grief and agony. I’ve been trying to figure it out for months now.