sql parameter contained by another SqlParameterCollection

  • Thread starter Thread starter andy.goss
  • Start date Start date
A

andy.goss

I have written a class to handle my sql server connections. I am not
sure that I am taking the best approach, however I am running into a
specific problem. In a console app that I am developing, i create a
new instance of the class and then call a method that executes a stored
procedure based on the stored procedure name that I pass it and an
ArrayList of SqlParameter objects containing the data that I need to
add. This works beautifuly the first time I call it. The stored
procedure is called and the data is inserted into the table. However,
the next call from my main app causes it to give the error message that
the parameter is contained by another SqlParameterCollection. I have
tried to clear the SqlCommand.Parameters however when I debugged and
looked, the commad has no parameters. Does any one have any
suggestions for how to solve this problem or a suggestion on a sql
server connection handler class? I have included the code from my
class for your reference. I appreciate any feedback. thanks.

public void executeSP(string spName, ArrayList parameters)
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlCon;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = spName;
sqlCmd.Parameters.Clear();

foreach(SqlParameter param in parameters)
{
sqlCmd.Parameters.Add(param);
}
try
{
sqlCon.Open();
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
}
catch(Exception e)
{
Console.WriteLine("SQL Error: " + e.Message);
throw new Exception(e.Message, e.InnerException);
}

}
 
The text of the error tell almost everything about the error.

You add the same SqlParameter object to two SqlParameterCollection objects.

After you add a SqlParameter to a SqlCommand.Parameters collection, this
parameter is bounded to this SqlParameterCollection object. You can not add
the same SqlParameter to another Parameters collection.

It is strange what you do as a pattern of ado.net programming; to pass the
SqlParameter-s as an argument to stored procedure runner method, and here
you add them to the collection. And this mode of sending the parameters as
an argument made me to believe that you pass the same SqlParameter object
from the first call to the second call. Because that, your code works for
the first time, but not on the second call.

Also, watch for closing/disposing the connection and the command; put the
calls in a finally block or use the using statement.

Dumitru Sbenghe
 
I create a new instance of a SqlCommand each time I call the stored
procedure function. The SqlParameterCollection that I am adding to is
the SqlCommand.Parameters collection. If I create a new instance of
the SqlCommand, this should get rid of the old one shouldnt it? What I
can't figure out is where this SqlParameterCollection is that these
SqlParameters belong to.

If you have any suggestions as to how I should write my sql interfacing
class, i would appreciate it.

Thanks,
Andy
 
Back
Top