CODEWITHSUNDEEP
c#stored-proceduresgridview
Prosper
Prosper

Reputation: 98

Error when inserting two or more GridView rows by calling a stored procedure

I am trying to insert GridView data to a table by invoking a stored procedure. The code is working just fine when the GridView have a single row, but how ever I am getting an error when it has more than one row. The error is bizarre enough,

Procedure or function sp_InsertOrderDetail has too many arguments specified.

private void inserOrderDetails()
{
conn = DB_Connect.GetConnection();

    if (validatition())
    {
        SqlCommand cmd = conn.CreateCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[dbo].[sp_InsertOrderDetail]";

        foreach (GridViewRow row in grdRequest.Rows)
        {
            //cmd.Parameters.Add("@OrderID", SqlDbType.Int, 32, "OrderID").Value = row.Cells[0].Text;
            cmd.Parameters.Add("@OrderID", SqlDbType.Int, 32, "OrderID").Value = lblOrderID.Text;
            cmd.Parameters.Add("@ItemID", SqlDbType.Int, 32, "ItemID").Value = row.Cells[1].Text;

            try
            {
                if (conn.State.ToString() == "Closed")
                {
                    conn.Open();
                }
                cmd.ExecuteNonQuery();

                //WebMsgBox.Show("Sucessfully saved.");
            }
            catch (Exception ex)
            {
                WebMsgBox.Show("Saving failed." + ex);
            }

            conn.Close();
            //refresh();
        }
    }
    else
    {
        WebMsgBox.Show("Please, Fill all data fields before saving.");
    }
}

Thanks, cheers!

Upvotes: 0

Views: 90

Answers (2)

rasso
rasso

Reputation: 2221

You keep adding parameters to same cmd object in foreach.

I'd declare the params outside of the loop as follows

cmd.Parameters.Add("@OrderID", SqlDbType.Int);
cmd.Parameters.Add("@ItemID", SqlDbType.Int);

and then in foreach, do

cmd.Parameters["@OrderID"].Value = lblOrderID.Text;
cmd.Parameters["@ItemID"].Value = row.Cells[1].Text;

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Clear parameters:

foreach (GridViewRow row in grdRequest.Rows)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@OrderID", SqlDbType.Int, 32, "OrderID").Value = lblOrderID.Text;
            cmd.Parameters.Add("@ItemID", SqlDbType.Int, 32, "ItemID").Value = row.Cells[1].Text;

Upvotes: 1

Related Questions