I have the next piece of code:

SqlCommand cmd2 = new SqlCommand("InsertProducts", con);
        cmd2.CommandType = CommandType.StoredProcedure;

        foreach (ListViewItem item in ListaProduse.Items)
        {
            cmd2.Parameters.AddWithValue("@DenProd", item.Text);
            cmd2.Parameters.AddWithValue("@ProdQuant", item.SubItems[1].Text);
            cmd2.Parameters.AddWithValue("@ProdSize", item.SubItems[1].Text);
            cmd2.Parameters.AddWithValue("@ProdComm", item.SubItems[1].Text);
        }

        var IDProductParameter = cmd2.Parameters.Add("@ProdID", SqlDbType.Int);
        IDProductParameter.Direction = ParameterDirection.Output;
        con.Open();
        cmd2.ExecuteNonQuery();
        var IDProdus = (int)IDProductParameter.Value;
        con.Close();

And this is the procedure:

    CREATE PROCEDURE [dbo].[InsertProducts]
@DenProd nvarchar(50),
@ProdQuant nvarchar(50),
@ProdSize nvarchar(50),
@ProdComm nvarchar(50),
    @ProdID INT OUTPUT
AS
BEGIN
    INSERT INTO Produse (Denumire, Cantitate, Dimensiuni, Comentarii) VALUES (@DenProd, @ProdQuant, @ProdSize, @ProdComm);
    SELECT @ProdID = SCOPE_IDENTITY();
END

If i add one product in the listview and press the insert button, it works. If i add another product or more and try to insert both of them in the database, this is the error i get:

System.Data.SqlClient.SqlException: 'Procedure or function InsertProducts has too many arguments specified.'

I believe the problem is in the @ProdID but i need it to insert the product id in a joined table.

CodePudding user response：

you have to execute the query inside your loop, cause you're adding parameters in the loop (if two ListViewItem so 8 parameters), that's why you're getting this error.

CodePudding user response：

You are adding the parameters without clearing the originals. You can either use Parameters.Clear() or you can change the Value of existing parameters. You then need to execute the command within the loop.

Note that you must use using blocks to dispose the connection. And if you do so, the connection will be closed automatically

Specify the parameter types and lengths explicitly

using (var con = new SqlConnection(YourConnString))
using (var cmd2 = new SqlCommand("InsertProducts", con) { CommandType = CommandType.StoredProcedure })
{
    cmd2.Parameters.Add("@DenProd", SqlDbType.NVarChar, 50);
    cmd2.Parameters.Add("@ProdQuant", SqlDbType.NVarChar, 50);
    cmd2.Parameters.Add("@ProdSize", SqlDbType.NVarChar, 50);
    cmd2.Parameters.Add("@ProdComm", SqlDbType.NVarChar, 50);
    var IDProductParameter = cmd2.Parameters.Add("@ProdID", SqlDbType.Int);
    IDProductParameter.Direction = ParameterDirection.Output;
    con.Open();

    foreach (ListViewItem item in ListaProduse.Items)
    {
        cmd2.Parameters["@DenProd"].Value = item.Text;
        cmd2.Parameters["@ProdQuant"].Value = item.SubItems[1].Text;
        cmd2.Parameters["@ProdSize"].Value = item.SubItems[1].Text;
        cmd2.Parameters["@ProdComm"].Value = item.SubItems[1].Text;
        cmd2.ExecuteNonQuery();
        var IDProdus = (int)IDProductParameter.Value; // do something with this value
    }
}

I must say, ideally you should use either a Table Valued parameter or SqlBulkCopy to do this in bulk, it will not be performant for large numbers of rows.