Procedure has too many arguments specified. it inserts the first row and gives that error on the second row

Question

I'm trying to insert all DataGridView rows into a database.

Here is my code:

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        using (SqlConnection sqlcon = new SqlConnection(con))
        {
            sqlcon.Open();

            SqlCommand cmd = new SqlCommand("dbo.SaveWorkerSchedulePerDay", sqlcon);
            cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < eachDayScheduleDG.Rows.Count; i++)
            {
                cmd.Parameters.AddWithValue("@day", DateTime.Now.ToString("yyyy-MM-dd"));
                cmd.Parameters.AddWithValue("@worker_id", eachDayScheduleDG.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@timeEnter", Convert.ToDateTime(eachDayScheduleDG.Rows[i].Cells[2].Value));
                cmd.Parameters.AddWithValue("@timeExit", Convert.ToDateTime(eachDayScheduleDG.Rows[i].Cells[3].Value));
                cmd.Parameters.AddWithValue("@costPerDay", getCostDays(eachDayScheduleDG.Rows[i].Cells[0].Value.ToString(), i));

                cmd.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("error: "+ ex);
    }
}

I get the following error:

Procedure has too many arguments specified.

I know that it's inserting the first row without any errors.

well the database is saying that you passed t many arguments to a stored procedure. / function. Since we dont get to see any of the source code we cant really say — pm100, Dec 14 '18 at 20:00
No need for the code of the proc. You aren't clearing the parameters collection of the SqlCommand between executions of your loop. See answer here https://stackoverflow.com/a/44272734/93922 — Tetsujin no Oni, Dec 14 '18 at 20:02
Learn about databinding and you wont have to loop thru DGV rows at all! One line of code adds, updates and deletes as needed — Ňɏssa Pøngjǣrdenlarp, Dec 14 '18 at 20:13

score 3 · Answer 1 · answered Dec 14 '18 at 20:14

The problem is that you are adding more and more parameters to your command at each iteration of the for-loop.

Instead, reuse the parameters like this:

// First, create the parameters without values.
cmd.Parameters.Add("@day", SqlDbType.DateTime);
cmd.Parameters.Add("@worker_id", SqlDbType.Int);
cmd.Parameters.Add("@timeEnter", SqlDbType.DateTime);
cmd.Parameters.Add("@timeExit", SqlDbType.DateTime);
cmd.Parameters.Add("@costPerDay", SqlDbType.Decimal);

for (int i = 0; i < eachDayScheduleDG.Rows.Count; i++)
{
    // Assign the values to the existing parameters.
    cmd.Parameters["@day"].Value = DateTime.Now.ToString("yyyy-MM-dd");
    cmd.Parameters["@worker_id"].Value = eachDayScheduleDG.Rows[i].Cells[0].Value);
    cmd.Parameters["@timeEnter"].Value = Convert.ToDateTime(eachDayScheduleDG.Rows[i].Cells[2].Value);
    cmd.Parameters["@timeExit"].Value = Convert.ToDateTime(eachDayScheduleDG.Rows[i].Cells[3].Value);
    cmd.Parameters["@costPerDay"].Value = getCostDays(eachDayScheduleDG.Rows[i].Cells[0].Value.ToString(), i);

    cmd.ExecuteNonQuery();
}

Make sure to use the correct SqlDbType constants.

score 0 · Accepted Answer · answered Dec 14 '18 at 20:03

0

Take the two lines that create the command and move them inside the for loop. That way you aren't trying to keep adding more parameters for each row all into the same command.

answered Dec 14 '18 at 20:03

Terry Tyson

629
8
18

Procedure has too many arguments specified. it inserts the first row and gives that error on the second row

2 Answers2