How to store multiple rows from gridview to database through the procedure

Question

I want to store multiple rows from a gridview to the database through the procedure. If there is only one row in the gridview, my code executes properly, but if there are several rows, it throws an error

Procedure or function insertDetails has too many arguments specified

Here is my C# code:

protected void Button2_Click(object sender, EventArgs e)
{
    for (int i = 0; i < GridView1.Rows.Count;i++ )
    {
        cmd.CommandText = "insertDetails";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@id", GridView1.Rows[i].Cells[2].Text);
        cmd.Parameters.AddWithValue("@name", GridView1.Rows[i].Cells[3].Text);
        cmd.Parameters.AddWithValue("@email", GridView1.Rows[i].Cells[4].Text);
        cmd.Parameters.AddWithValue("@contact", GridView1.Rows[i].Cells[5].Text);
        cmd.Parameters.AddWithValue("@Addres", GridView1.Rows[i].Cells[6].Text);

        con.Open();
        cmd.ExecuteNonQuery();                
        con.Close();

        cmd.Dispose();
    }
}

You only want to open and close the connection once, outside the loop. Re the error - compare the params with those in stored procedure and in your table. — IrishChieftain, Apr 19 '18 at 18:11

score 0 · Answer 1 · answered Apr 20 '18 at 01:30

I found that for loop causing the problem, because new SqlParameter added for every iteration without clearing previously created ones, hence at second row iteration amount of passed parameters will exceed total parameters defined in stored procedure (more than 5).

You need to define parameters outside the loop first, then set their value inside the loop as in example below:

protected void Button2_Click(object sender, EventArgs e)
{
    using (var con = new SqlConnection("[DB connection string]"))
    {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = "insertDetails";
            cmd.CommandType = CommandType.StoredProcedure;

            // create all parameters outside the loop
            // no need to use `cmd.Parameters.Clear()` here
            cmd.Parameters.Add("@id", SqlDbType.Int);
            cmd.Parameters.Add("@name", SqlDbType.VarChar);
            cmd.Parameters.Add("@email", SqlDbType.VarChar);
            cmd.Parameters.Add("@contact", SqlDbType.VarChar);
            cmd.Parameters.Add("@Addres", SqlDbType.VarChar);

            con.Open();

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                // assign parameter values inside the loop and execute the query
                cmd.Parameters["@id"].Value = GridView1.Rows[i].Cells[2].Text);
                cmd.Parameters["@name"].Value = GridView1.Rows[i].Cells[3].Text);
                cmd.Parameters["@email"].Value = GridView1.Rows[i].Cells[4].Text);
                cmd.Parameters["@contact"].Value = GridView1.Rows[i].Cells[5].Text);
                cmd.Parameters["@Addres"].Value = GridView1.Rows[i].Cells[6].Text);

                cmd.ExecuteNonQuery();
            }
        }
    }
}

Side note: Use using statement for both SqlConnection and SqlCommand since they're implement IDisposable.

Similar issue:

Should I call Parameters.Clear when reusing a SqlCommand with a transation?

How to store multiple rows from gridview to database through the procedure

1 Answers1