Error: "The SqlParameter is already contained by another SqlParameterCollection" when adding a Parameter

Question

I have a few similar functions that interact with my SQL server (selects, calls to stored procdures etc.) and all work with the exception of the one below. Each SqlConnection is contained within a using block with the SqlCommand also contained within a using block.

This code is failing when attempting to add the @LastUpdated parameter. I've tried some suggestions I've seen in other posts: cmd.Parameters.Clear(), wrapping in using, etc., but no luck. A few posts with the same error were resolved when duplicate attempts to set the same parameter where found. It's possible I'm missing that but I've looked over this for a few hours, even cleaned the glasses. Any direction would be appreciated.

private void _AddCartItem(bool hasEventInCart, _EventCart cartContents, ref int cartItemId)
{
    using (SqlConnection sqlConnection = new SqlConnection(_striMISConnection))
    {
        sqlConnection.Open();

        using (SqlCommand cmd = sqlConnection.CreateCommand())
        {
            SqlParameter param = new SqlParameter();
            cmd.Parameters.Clear();

            // add/update CartItem
            if (hasEventInCart)
            {
                // Update item
                cmd.CommandText = "SProc2Insert @CartItemId, @ID, 'Event', @Created, @LastUpdated";
                param.ParameterName = "@CartItemId";
                param.Value = cartItemId;
                cmd.Parameters.Add(param);
            }
            else
            {
                // add item
                cmd.CommandText = "SProc2Update @ID, 'Event', @Created, @LastUpdated";
            }

            cmd.CommandType = CommandType.Text;

            param.ParameterName = "@Created";
            param.Value = DateTime.Now.ToString();
            cmd.Parameters.Add(param);

            param.ParameterName = "@LastUpdated";
            param.Value = DateTime.Now.ToString();
            **cmd.Parameters.Add(param);**

            param.ParameterName = "@ID";
            param.Value = this.ID;
            cmd.Parameters.Add(param);

            if (hasEventInCart)
            {
                cmd.ExecuteNonQuery(); // do the update
            }
            else
            {
                cartItemId = (int)cmd.ExecuteScalar();

                foreach (var currentCartEvent in cartContents.CartEvents)
                {
                    if (currentCartEvent.EventCode == this.EventCode)
                    {
                        currentCartEvent.CartItemID = cartItemId;
                    }
                }
            }
            cmd.Parameters.Clear();
        }
    }
}

Answer 1

I had the same problem, you can solve it with the code below:

if (SqlParams != null && SqlParams.Count > 0)
{ 
    foreach (SqlParameter spp in SqlParams )
    {
        SqlParameter nameParam = new SqlParameter(spp.ParameterName, spp.SqlValue);

        mycmd.Parameters.Add(nameParam);
    }
}

Answer 2

First of all, if you're calling a stored procedure, you need to set your CommandType to CommandType.StoredProcedure - not Text :

cmd.CommandType = CommandType.StoredProcedure;

and just use the stored procedure name as your command query:

cmd.CommandText = "dbo.SProc2Update";

Second, I wouldn't use just a single SqlParameter instance and keep adding that over and over again - I would just use the .Add() method directly - and stop converting all date/time to string! Use the appropriate native datatype they have - DateTime - and specify as such in your Parameters.Add() call:

cmd.Parameters.Add("@Created", SqlDbType.DateTime).Value = DateTime.Now;

cmd.Parameters.Add("@LastUpdated", SqlDbType.DateTime).Value = DateTime.Now;