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();
        }
    }
}

You need to create new SqlParameter instances, not reuse the same one. They're passed by reference. You're overwriting it. As a result when you call add again you're trying to add a parameter that already exists since you updated the name of the first one. They're the same object. — dmeglio
– dmeglio, Commented Sep 3, 2015 at 2:57
Create new instance of your SqlParameter or make IEnumerable<SqlParameter> then cmd.Parameters.AddRange — dan
– dan, Commented Sep 3, 2015 at 2:57
Tips: Adding a parameter and setting the value may be done in a single statement: sqlCommand.Parameters.Add("@Filename", System.Data.SqlDbType.VarChar, 64).Value = "Foo";. Adding output or returnvalue parameters may be done in a single statement: sqlCommand.Parameters.Add("@Filename", System.Data.SqlDbType.VarChar, 64).Direction = System.Data.ParameterDirection.Output;. It's a bit odd that you are passing DateTime values as strings rather than DateTime or a more suitable data type (Date, DateTimeOffset, ... .). — HABO
– HABO, Commented Sep 3, 2015 at 3:46
@HABO ... thanks for the shortcut. This is a first pass on the function. I'll clean it up and adjust the DateTime values. — JPrez
– JPrez, Commented Sep 3, 2015 at 4:51

Theresa · Accepted Answer · 2017-11-15 14:29:14Z

4

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);
    }
}

edited Nov 15, 2017 at 14:29

Theresa

3,65510 gold badges45 silver badges47 bronze badges

answered Nov 15, 2017 at 13:34

Vladi

2,03823 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2015-09-03 04:51:38Z

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;

edited Sep 3, 2015 at 4:51

answered Sep 3, 2015 at 4:48

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

Darren Kopp Over a year ago

he's calling a sproc using an ad-hoc query though. he can't just directly change CommandType to StoredProcedure, the author would need to drop the parameter list. The author needs to pass 'Event' as a parameter as well if he were to change the command type.

JPrez Over a year ago

thank you both for the replies. The earlier responses did resolve the issue however your suggestions are appreciated.

KrazKjn · Accepted Answer · 2020-05-04 02:00:40Z

0

This is how I got this working:

        ILease lease = (ILease)_SqlParameterCollection.InitializeLifetimeService();
        if (lease.CurrentState == LeaseState.Initial)
        {
            lease.InitialLeaseTime = TimeSpan.FromMinutes(5);
            lease.SponsorshipTimeout = TimeSpan.FromMinutes(2);
            lease.RenewOnCallTime = TimeSpan.FromMinutes(2);
            lease.Renew(new TimeSpan(0, 5, 0));

answered May 4, 2020 at 2:00

KrazKjn

1

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related