The SqlParameter is already contained by another SqlParameterCollection
A few days ago, I closed the laptop lid on my Toshiba and went to where I was going. It never came out of standby and as a result - had to cold power it. Unfortunately for me, I had Visual Studio open, and when I re-opened my solution - only 36 of the 90 some projects were showing. Considering a majority of the projects are module specific DALs, I realized that after spending 6 hours getting the projects back into the solution and properly configuring them, that the time was now and not later to consolidate all of those DALs to use the Generics based 'global' DAL that I had created.
In the process, I needed to write some form of a generic method that would handle populating a dataset from multiple queries - using the least bit of code.
Example:
public DataSet ReturnDatasetFromStoredProcedure(ArrayList SP_AND_TABLENAMES, List<ParmInfo2> SELECT_PARMS)
{ SqlDataAdapter cmd = new SqlDataAdapter();
DataSet dst = new DataSet();
int i = 0;
try
{
foreach (string p in SP_AND_TABLENAMES)
{
// [0] is the table name [1] is the name to assign the table when we do a fill
string[] args = p.Split(',');
cmd = new SqlDataAdapter(args[0], CommunityGlobals.ConnectionString);
foreach (ParmInfo2 item in SELECT_PARMS)
{
cmd.SelectCommand.Parameters.Add(item.SQLPARM);
}
cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
cmd.Fill(dst, args[1]);
}
Using the code above - generates a: "The SqlParameter is already contained by another SqlParameterCollection" error." (see bold highlight in the code above).
You would figure that by calling: cmd = new SqlDataAdapter(args[0], CommunityGlobals.ConnectionString);
Would clear automatically the parameters associated with. Practically every other new() something does. Not the case here and so a quick search and a discusion thread pointed out that adding a cmd.SelectCommand.Parameters.Clear(); after the cmd.Fill, would allow me to re-use the parameter.
It worked.
An interesting discussion on why here this is an issue but when using some of the other ADO commands - you can re-use the parameter assignment...
see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=339961&SiteID=1