Implementation of adding SqlCeParameters in a loop

Question

I'm trying to add into table multiple values from string arrays dateOperation typeOperation bought and sold which have equal array length

This is the core I tried and it works but its not with a loop

using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Operations (date, type, bought, sold) VALUES (@date, @type, @bought, @sold)");
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;

    cmd.Parameters.AddWithValue("@date", dateOperation[0]);
    cmd.Parameters.AddWithValue("@type", typeOperation[0]);
    cmd.Parameters.AddWithValue("@bought", bought[0]);
    cmd.Parameters.AddWithValue("@sold", sold[0]);                    

    connection.Open();
    cmd.ExecuteNonQuery();
}

But this code snippet with loop isn't guess I'm missing something in the syntax

using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Operations (date, type, bought, sold) VALUES (@date, @type, @bought, @sold)");
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;

    for (int i = 0; i <= bought.Length; i++)
    {
        cmd.Parameters.AddWithValue("@date", dateOperation[i]);
        cmd.Parameters.AddWithValue("@type", typeOperation[i]);
        cmd.Parameters.AddWithValue("@bought", bought[i]);
        cmd.Parameters.AddWithValue("@sold", sold[i]);
    }

    connection.Open();
    cmd.ExecuteNonQuery();
}

Because sometimes dateOperations , typeOperation , bought and sold are not always the same length of array the code works fine but it gives me an error if the array is less than 4 length the table recieves data fine but the error is screwing my program how to fix it or how to make an exception "Parameterized query expects a parameter value which was not supplied."

string[] dateOperation = new string[4];
            string[] typeOperation = new string[4];
            string[] bought = new string[4];
            string[] sold = new string[4];

            for (int i = 0; i <= 3; i++)
            {
                if (tb[i].Text != "" && Odate[i].Text != "") 
                {
                    dateOperation[i] = Odate[i].Text.ToString();
                    typeOperation[i] = type[i].Text.ToString();
                    bought[i] = tb[i].Text.ToString();
                    sold[i] = BGN[i].Text.ToString();
                }
            }

            string connectionString = @"Data Source=C:\Users\FluksikartoN\Documents\BFDB.sdf;Password=******";
            using (SqlCeConnection connection = new SqlCeConnection(connectionString))
            {


                    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Operations (date, type, bought, sold) VALUES (@date, @type, @bought, @sold)");
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = connection;
                    cmd.Parameters.AddWithValue("@date", DbType.String);
                    cmd.Parameters.AddWithValue("@type", DbType.String);
                    cmd.Parameters.AddWithValue("@bought", DbType.String);
                    cmd.Parameters.AddWithValue("@sold", DbType.String);
                    connection.Open();
                    for (int i = 0; i < bought.Length; i++)
                    {
                        cmd.Parameters["@date"].Value = dateOperation[i];
                        cmd.Parameters["@type"].Value = typeOperation[i];
                        cmd.Parameters["@bought"].Value = bought[i];
                        cmd.Parameters["@sold"].Value = sold[i];
                        cmd.ExecuteNonQuery();
                    }

Answer 1

You need to add parameters to the command first (before the loop). Then in the loop set the values of the parameters and execute the command:

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Operations (date, type, bought, sold) VALUES (@date, @type, @bought, @sold)");
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;

cmd.Parameters.Add("@date", DbType.DateTime);
// other parameters

connection.Open();
for (int i = 0; i <= bought.Length; i++)
{
    cmd.Parameters["@date"].Value = dateOperation[i];
    // other parameters
    cmd.ExecuteNonQuery();
}