Function that is supposed to insert a list of objects into a database only inserts one of the objects n times

Question

I've been trying to create a function that will set up an order for all the items that are lacking in my inventory.

RequiredStockForAllOrders basically assigns a value to each object in stockItems which lets me know how many items I need to order.

I checked with a messagebox which does the change in values (both ID and quantity) but when I run the loop that is supposed to insert each product with its respective quantity I only insert 1 product n times where n is the amount of items in the list.

    private void AddAllRequiredItems_Click(object sender, EventArgs e)
    {
        var stockItems = new List<MyData>();
        //MyData is an object with  a productID int and a productQuantity int 

        RequiredStockForAllOrders(stockItems);
        //determining the quantity required for each item

        OleDbConnection con = new OleDbConnection(DatabaseConnectionString);
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = con;
        con.Open();

        string sql2 = "INSERT INTO restockingDetails(RestockingID,ProductID,Quantity,Shop_ID) values (@restockingID,@productID,@quantity,@shop_id)";
        cmd.CommandText = sql2;


        int i = 0;

        while (i < stockItems.Count)
        {
            try
            {
                MessageBox.Show(stockItems[i].productId.ToString()); //For testing

                cmd.Parameters.AddWithValue("@restockingID", restockingOrder);
                cmd.Parameters.AddWithValue("@productID", stockItems[i].productId);
                cmd.Parameters.AddWithValue("@quantity", stockItems[i].productQuantity);
                cmd.Parameters.AddWithValue("@shop_id", shopIDGlobal);
                cmd.ExecuteNonQuery();
                MessageBox.Show(" Item added to list"); //for testing


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }


            i = i + 1;
        }

      con.Close()
    }

Answer 1

Just add this line before adding the parameters

MessageBox.Show(stockItems[i].productId.ToString()); //For testing
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@restockingID", restockingOrder);

You actual code continues to add parameters to the command collection, but the query uses only the first four. With other providers this code will result in an error (too many parameters) but OleDb is somebit limited in this point. Probably because it doesn't recognize parameters by their name, but by their position

A better approach could be to define the parameters just once and then updating their values inside the loop

private void AddAllRequiredItems_Click(object sender, EventArgs e)
{
    var stockItems = new List<MyData>();

    RequiredStockForAllOrders(stockItems);

    string sql2 = "INSERT INTO restockingDetails(RestockingID,ProductID,Quantity,Shop_ID) values (@restockingID,@productID,@quantity,@shop_id)";

    using(OleDbConnection con = new OleDbConnection(DatabaseConnectionString))
    using(OleDbCommand cmd = new OleDbCommand(sql2, con))
    {
        con.Open();
        cmd.Parameters.Add("@restockingID", OleDbType.Integer);
        cmd.Parameters.Add("@productID", OleDbType.Integer);
        cmd.Parameters.Add("@quantity", OleDbType.Integer);
        cmd.Parameters.Add("@shop_id", OleDbType.Integer);
        foreach(MyData item in stockItems)
        {
           try
           {
               cmd.Parameters["@restockingID"].Value = restockingOrder;
               cmd.Parameters["@productID"].Value = item.productId;
               cmd.Parameters["@quantity"].Value = item.productQuantity;
               cmd.Parameters["@shop_id"].Value = shopIDGlobal;
               cmd.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
      }
   }
}