Resolved Procedure or function sp_updateItems has too many arguments specified.

[Anonymous]

I want to update the price of items in the database. I am extracting a list of items from another list( of class type having other information also). After that I am sending it(item number) one by one as a stored procedure parameter to get the price updated.
But I am getting a Procedure or function sp_updateItems has too many arguments specified exception even though i am sending only one item as a parameter at a time and my stored procedure also accepts only one parameter.

 private void UpdatePrice(List<ListItem> accounts)
        {

            var items = (from item in accounts
                                 select item.itemNumbers);

            con = new SqlConnection(connection);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_updateItems";
            con.Open();
            foreach (var item in items)
            {

                cmd.Parameters.AddWithValue("@itemnumber", item.ToString());
                cmd.Connection = con;
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                    Console.WriteLine("Price Updated Successfully");
                else
                    Console.WriteLine("Failed");
               
            }
            con.Close();
            //foreach(var item in items)
            //{
            //    Console.WriteLine(string.Join(", ", item));
            //}
            Console.ReadKey();
        }

My stored procedure is

create proc sp_updateItems
(
@itemnumber varchar(20)
)
as
begin
update station set Newprice = price*2
where itemnumber=@itemnumber
end

 

[Skydiver]

Notice on line 15 that you are calling AddWithValue() to the same cmd.

You'll want to add the parameter once, and just keep changing the value each time after the initial addition.

 

[Anonymous]

Notice on line 15 that you are calling AddWithValue() to the same cmd.

You'll want to add the parameter once, and just keep changing the value each time after the initial addition.

You mean to say I will have to create a new Sqlcommand object in every iteration? I really don't understand, isn't this code sending one item at a time for every iteration to the stored procedure. What exactly is going wrong?

 

[jmcilhinney]

You mean to say I will have to create a new Sqlcommand object in every iteration?

No, he means what he said. You have one command object and that is all you need. You then need to add one parameter to that command. Inside the loop, you need to set the Value of that one and only parameter. As it stands, you're adding a parameter on each iteration so you end up with two parameters on the second iteration, which is too many. If the code kept going, you'd end up with three parameters on the third iteration and N parameters on the Nth iteration. You need to have only one parameter the whole time, so just add one at the start.

 

[Anonymous]

No, he means what he said. You have one command object and that is all you need. You then need to add one parameter to that command. Inside the loop, you need to set the Value of that one and only parameter. As it stands, you're adding a parameter on each iteration so you end up with two parameters on the second iteration, which is too many. If the code kept going, you'd end up with three parameters on the third iteration and N parameters on the Nth iteration. You need to have only one parameter the whole time, so just add one at the start.

okay so I did this but getting The SqlParameter is already contained by another SqlParameterCollection. exception.

 private void UpdatePrice(List<ListItem> accounts)
        {

            var items = (from item in accounts
                                 select item.itemNumbers);

            con = new SqlConnection(connection);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_updateItems";
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@itemnumber";
            con.Open();
            foreach (var item in items)
            {

                param.Value = item.ToString();
                cmd.Parameters.Add(param);
                cmd.Connection = con;
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                    Console.WriteLine("Price Updated Successfully");
                else
                    Console.WriteLine("Failed");
                
            }
            con.Close();
            //foreach(var item in items)
            //{
            //    Console.WriteLine(string.Join(", ", item));
            //}
            Console.ReadKey();
        }

 

[JohnH]

In your last code you do Parameters.Add inside the loop (line 18). Do it outside the loop.

 

[Anonymous]

In your last code you do Parameters.Add inside the loop (line 18). Do it outside the loop.

Now I am not getting any exception but it's getting failed.

con = new SqlConnection(connection);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_updateItems";
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@itemnumber";
            cmd.Parameters.Add(param);
            con.Open();
            foreach (var item in items)
            {

                param.Value = item.ToString();
                
                cmd.Connection = con;
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                    Console.WriteLine("Price Updated Successfully");
                else
                    Console.WriteLine("Failed");
                
            }
            con.Close();

 

[Skydiver]

What is the failure? We are not clairvoyant. Nor are we affiliated with the 5Eyes and have hacking tools to be watching what you are doing on your machine.

 

[Anonymous]

What is the failure? We are not clairvoyant. Nor are we affiliated with the 5Eyes and have hacking tools to be watching what you are doing on your machine.

Actually I am also not sure what is the failure and why its failing. I debugged and the control is going to the console.writeline("Failed") .
The stored procedure is working correctly.

 

[Skydiver]

0 in that case that just means that no rows were updated. Very likely because there was no matching item number in that table you are trying to update.

 

[Anonymous]

0 in that case that just means that no rows were updated. Very likely because there was no matching item number in that table you are trying to update.

Nope. That is the main issue. I am sending a list of only those items which are present in the database.

 

[Skydiver]

Your stored procedure is only looking at the "station" table. Did your item numbers come from that same table, or some other table?

 

[JohnH]

You should also configure the SqlParameter to match the SqlDbType and Size of SP's parameter that is varchar(20).

 

[Anonymous]

You should also configure the SqlParameter to match the SqlDbType and Size of SP's parameter that is varchar(20).

I believe the issue is in the foreach loop here. Not sure if this is what is causing issue.

  foreach (var item in items)
            {

                param.Value = item.ToString();
               
                cmd.Connection = con;
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                    Console.WriteLine("Price Updated Successfully");
                else
                    Console.WriteLine("Failed");
               
            }

Instead of one it is taking two items at a time. The var item consist if count 2 in one iteration .

 

[Anonymous]

Your stored procedure is only looking at the "station" table. Did your item numbers come from that same table, or some other table?

Items are from the same table.
The Linq which I am using is storing items in a group At index 0, it has 2 items and at index 1, it has 6 items. This is I believe causing the issue. How can I simply store all the items serially in a list ?