Can't solve "Sqlparameter is already contained by another SqlparameterCollection"

Question

I am using 2 threads (from same class) in a windows service. I always getting the same error message:

"The SqlParameter is already contained by another SqlParameterCollection.
   at System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, Object value)
   at System.Data.SqlClient.SqlParameterCollection.Add(Object value)
   at System.Data.SqlClient.SqlParameterCollection.Add(SqlParameter value)
   at DataBaseLayer.SqlDataBaseLayer.FillDataSetFromProcedure(String strStoredProc, ArrayList parameterCollection)
   at TestThread.StartThreads()"

I tried to solve the problem by creating new instances of SqlParameters and an Arraylist. I also tried to clear the arraylists at the and of my for loop in the code. This does not solve the problem. I am open to any advice.

Why are you using the same SqlParameter object against multiple commands? Apologies if you're not, but the error message you're getting strongly suggests that you are. — Will A, Jun 09 '11 at 07:48
Try to clear command parameters list and then add new parameters. cmd.Parameters.Clear(); — Renatas M., Jun 09 '11 at 07:51

score 2 · Accepted Answer · answered Jun 09 '11 at 08:32

You are trying to add a SqlParameter to a SqlParameterCollection twice. This may or may not be happening across threads.

If this is a multi-threading issue then all your variables should be scoped locally because, if they are not you should be implemeting sychronisation on thier access, probably with lock.

If this is not a concurrency problem the issue should easily be identified by doing a Find All References on your SqlParameter variable.

Either way we can help you more if you post some code.

score 2 · Answer 2 · answered Jun 09 '11 at 08:59

2

Dont share the same sql objects between 2 thread. Use the pool collection by SQL

answered Jun 09 '11 at 08:59

Zenwalker

1,883
1
14
27

Can't solve "Sqlparameter is already contained by another SqlparameterCollection"

2 Answers2

Linked