The SqlParameter is already contained by another SqlParamet

Krishna Kumar

Hello...

i am passing the stored procedure name and parameter collection to this method. but while executing it is giving the error, The SqlParameter is already contained by another SqlParameter. eventhough i have added the parameters.clear() function to clear the parameters. but it is giving the same error

public void executeSP(string spname, SqlParameterCollection sp)

{

int retobj = 0;

//open connection

con.Open();

//execute SP

cmd = new SqlCommand(spname, con);

cmd.CommandType = CommandType.StoredProcedure;

if (sp.Count > 0)

{

foreach (SqlParameter p in sp)

{

cmd.Parameters.Add(p);

cmd.Parameters.Clear();

}

retobj=cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

//Close connection

con.Close();

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted answer
Well, I don't have my laptop with me but I ll try to figure it out..

Create a function

public int executeSP(string spname, List para)
{
int retobj = 0;
//open connection
con.Open();
//execute SP
var cmd = new SqlCommand(spname, con);
cmd.CommandType = CommandType.StoredProcedure;
if (para.Count > 0)
cmd.Parameters.AddRange ( para.ToArray ());

retobj=cmd.ExecuteNonQuery();
//Close connection
con.Close();
return retobj;
}

--------------- - calling function -----------
public void btn_click (###)
{
var parameters = new List(); parameters.Add(new SqlParameter("@Username", NewUsername));
parameters.Add(new SqlParameter("@Password", Password));
// Call the function
execureSP ("sp_name", parameters);
}

Do let me know if it solves your problem
+1

Krishna Kumar9y ago

@Amit I have done what you have said but the error still persists..

can you post the code with solution

Amit Gupta9y ago
@pradeep, you cannot call clear method inside a loop because you are adding here and simultaneously you are calling clear..it will not add all the item available in the collection. The only solution to tackle this is to initialize SqlParameter with a new keyword.
+1
Pradeep Singh9y ago
its cause because of loop

foreach (SqlParameter p in sp)
{
cmd.Parameters.Add(p);
cmd.Parameters.Clear();
}
i think when a parameter hold a value then in second line its value is clear

foreach (SqlParameter p in sp)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(p);
}

and second think you are use same sqlParameter twice use new for each new parameter
0
Nitin Sontakke9y ago
The following line is actually not adding any "named" parameter to the collection:

sp.Add(new SqlParameter("@Userid", SqlDbType.Int, 32)).Value = L.Userid;

You are just creating an instance (notice within brackets), assigning it a value and adding it to the collection. If you just hover a mouse in debug mode on "sp", you will notice that parameters are not having any name. It is because of this, you are getting the error.

You will need to create a sqlparameter object, assign it a value then add that to the collection. Then it will work. I suggest you write a small function which will return SqlParameter.

Hope it helps!
0
Amit Gupta9y ago
This happen when you use same sqlParameter twice, just you need to initialize it again with new keyword and then use even if it declared as a global variable or local variable.

Do let me know if it solves your problem
+2
Krishna Kumar9y ago
Thepeiceofcodetopassparametercollectionis
SqlParameterCollection sp = new SqlCommand().Parameters;
sp.Add(new SqlParameter("@Userid", SqlDbType.Int, 32)).Value = L.Userid;
sp.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = L.UserName;
sp.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = L.Password;
sp.Add(new SqlParameter("@Email", SqlDbType.VarChar)).Value = L.Email;
sp.Add(new SqlParameter("@SecurityCode", SqlDbType.VarChar)).Value = L.SecurityCode;
sp.Add(new SqlParameter("@Expirydate", SqlDbType.Date)).Value = L.Expirydate;

cs.executeSP("sp_HR_Login_Insert", sp);

return 0;

0
Nitin Sontakke9y ago
The piece of code given is fine. We would like to see the code which is populating SqlParameterCollection which you are passing as sp to the method shown here.

It is likely that there are two parameters which are named identically.
0