Loop Through and Add All ListBox Items To an Access Database (into seperate rows)

Question

I am using the following code to try and loop through a list box and insert each item into separate rows in an access database. The code is working for the first listbox item only but inserts it as expected.

I some how need to loop through so that all items are inserted and not just the first one

        foreach (var listBoxItem in ServicePartsList.Items)
        {

            item = listBoxItem.ToString();
            string[] result = item.Split(',');
            MessageBox.Show(result[0] + result[1]);
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = connection.dbdataSource;
                // insert into database
                using (OleDbCommand addPart = new OleDbCommand())
                {
                    //Open Connection
                    conn.Open();
                    addPart.Connection = conn;

                    addPart.CommandText = "INSERT INTO servicePart (ServiceID, PartNo, Quantity) VALUES (@sID, " + "@partNo, " + "@quantity)";
                    addPart.Parameters.AddWithValue("sID", ModelCode + ModelYear + ButtonClick);
                    addPart.Parameters.AddWithValue("partNo", result[0]);
                    addPart.Parameters.AddWithValue("quantity", result[1]);

                    //execute SQL
                    int recordsAdded = addPart.ExecuteNonQuery();

                    //Close DB Connection
                    conn.Close();

                }

Answer 1

Might not be the exact solution, but instead of opening connection in a loop, Open it once and then use one connection for inserting multiple rows:

using (OleDbConnection conn = new OleDbConnection())
{
    conn.ConnectionString = connection.dbdataSource;
    conn.Open();

    foreach (var listBoxItem in ServicePartsList.Items)
    {
        item = listBoxItem.ToString();
        string[] result = item.Split(',');
        MessageBox.Show(result[0] + result[1]);
        // insert into database
        using (OleDbCommand addPart = new OleDbCommand())
        {
            //Open Connection

            addPart.Connection = conn;

            addPart.CommandText = "INSERT INTO servicePart (ServiceID, PartNo, Quantity) VALUES (@sID, " + "@partNo, " + "@quantity)";
            addPart.Parameters.AddWithValue("sID", ModelCode + ModelYear + ButtonClick);
            addPart.Parameters.AddWithValue("partNo", result[0]);
            addPart.Parameters.AddWithValue("quantity", result[1]);

            //execute SQL
            int recordsAdded = addPart.ExecuteNonQuery();

        }
    }
    //Close DB Connection
    conn.Close();
}