SqlParameter Exception: The SqlParameter is already contained by another SqlParameterCollection

Question

I am getting an exception

The SqlParameter is already contained by another SqlParameterCollection

I have moved my SqlParameter calls out of the foreach loop as I thought that was the issue but I am still getting the same exception.

private void SendToSQLServer_FactionStandings(Faction faction)
{
    string sqlCnnString = "Server=tcp:horde.database.windows.net,1433;Initial Catalog=horde_db;Persist Security Info=False;User ID=USERNAME@horde;Password=PASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

    SqlParameter sqlParaFaction_Faction_ID = new SqlParameter("@Faction_ID", SqlDbType.Int);
    SqlParameter sqlParaFaction_ToFactionID = new SqlParameter("@ToFaction_ID", SqlDbType.Int);
    SqlParameter sqlParaFaction_Standing = new SqlParameter("@Standing", SqlDbType.SmallInt);

    using (SqlConnection sqlCnn = new SqlConnection(sqlCnnString))
    {
        sqlCnn.Open();

        using (SqlTransaction sqlTrans = sqlCnn.BeginTransaction())
        {
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.Parameters.Clear();

                foreach (FactionStanding factionStanding in faction.FactionStandings)
                {
                    //
                    // - Insert Faction Standing Table Data
                    //

                    sqlParaFaction_Faction_ID.Value = factionStanding.Faction_ID; 
                    sqlCmd.Parameters.Add(sqlParaFaction_Faction_ID); // <---- Getting exception here

                    sqlParaFaction_ToFactionID.Value = factionStanding.ToFaction_ID;
                    sqlCmd.Parameters.Add(sqlParaFaction_ToFactionID);

                    sqlParaFaction_Standing.Value = factionStanding.DefaultStanding;
                    sqlCmd.Parameters.Add(sqlParaFaction_Standing);

                    string sqlCmdFactionStandings = "INSERT INTO FactionStandings (Faction_ID, ToFaction_ID, Standing) VALUES (@Faction_ID, @ToFaction_ID, @Standing)";

                    sqlCmd.Connection = sqlCnn;
                    sqlCmd.CommandText = sqlCmdFactionStandings;
                    sqlCmd.Transaction = sqlTrans;

                    sqlCmd.ExecuteNonQuery();
                }

                sqlTrans.Commit();
                sqlCmd.Parameters.Clear();
            }
        }
    }
}

I added two lines to clear the parameters after they were being used in hopes that they were not being disposed by the using statement. Same problem. Updated code and line number. — Jon H, Dec 11 '16 at 01:41
Please check the help articles [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve), [How to ask a good question](http://stackoverflow.com/help/how-to-ask) and [Stack Overflow question checklist](http://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist). — David Ferenczy Rogožan, Dec 11 '16 at 02:14

score 3 · Accepted Answer · answered Dec 11 '16 at 01:45

You are adding the same SqlParameter multiple times to the same SqlCommand. The problem is that Parameters.Clear() removes the parameters from the command, but the parameters still think that the command 'owns' them. There's no way to remove this ownership. It's unintuitive and it's a common mistake :)

2 ways to fix this:

Create new SqlParameter objects for each loop iteration

Or use a single SqlCommand (like what you have) and pull the sqlCmd.Parameters.Add to outside the loop. e.g.

    SqlParameter sqlParaFaction_Faction_ID = new SqlParameter("@Faction_ID", SqlDbType.Int);
    SqlParameter sqlParaFaction_ToFactionID = new SqlParameter("@ToFaction_ID", SqlDbType.Int);
    SqlParameter sqlParaFaction_Standing = new SqlParameter("@Standing", SqlDbType.SmallInt);

    using (SqlConnection sqlCnn = new SqlConnection(sqlCnnString))
    {
        sqlCnn.Open();
        using (SqlTransaction sqlTrans = sqlCnn.BeginTransaction())
        {
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.Connection = sqlCnn;
                string sqlCmdFactionStandings = "INSERT INTO FactionStandings (Faction_ID, ToFaction_ID, Standing) VALUES (@Faction_ID, @ToFaction_ID, @Standing)";
                sqlCmd.CommandText = sqlCmdFactionStandings;
                sqlCmd.Transaction = sqlTrans;
                sqlCmd.Parameters.Add(sqlParaFaction_Faction_ID);
                sqlCmd.Parameters.Add(sqlParaFaction_ToFactionID);
                sqlCmd.Parameters.Add(sqlParaFaction_Standing);

                foreach (FactionStanding factionStanding in faction.FactionStandings)
                {
                    //
                    // - Insert Faction Standing Table Data
                    //
                    sqlParaFaction_Faction_ID.Value = factionStanding.Faction_ID; 
                    sqlParaFaction_ToFactionID.Value = factionStanding.ToFaction_ID;
                    sqlParaFaction_Standing.Value = factionStanding.DefaultStanding;

                    sqlCmd.ExecuteNonQuery();
                }

                sqlTrans.Commit();
            }
        }
    }
}

Thanks! I cant believe I overlooked that. I wont be making that mistake again! — Jon H, Dec 11 '16 at 01:50

SqlParameter Exception: The SqlParameter is already contained by another SqlParameterCollection

1 Answers1