0

I have an Access database. I'm using C# and Visual Studio I'm attempting to insert several rows within a transaction. However, instead of inserting each row, I end up with the first element inserted multiple times.

I can insert the data into the "Invoices" table one row at a time, so the table is in working order. I included the code below. The rows do have varied data. The debugger shows that.

private void insertInvoiceItemsIntoDatabase(int tableNumber)
{
    string category, food;
    double price
    string connectionString = _databaseConnectionString; 
    
    using (OleDbConnection connection =
               new OleDbConnection(connectionString))
    {
        OleDbCommand command = new OleDbCommand();
        OleDbTransaction transaction = null;

        // Set the Connection to the new OleDbConnection.
        command.Connection = connection;

        // Open the connection and execute the transaction.
        try
        {
            connection.Open();

            // Start a local transaction with ReadCommitted isolation level.
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            command.Connection = connection;
            command.Transaction = transaction;

            command.CommandText = "Insert into Invoices (TableNumber, Category, Food, Price) VALUES (?,?,?,?)";

            command.CommandType = CommandType.Text;

            Order order;
            order = tables[tableNumber - 1].getOrder();
            List<MenuItem> items = order.deepCopyMenuItems();
            for (int i = 0; i < items.Count(); i++)
            {
                FoodType ft =  items[i].getFoodType();
                category = "Main Course";
                if(ft == FoodType.maincourse)
                {
                    category = "Main Course";
                }else if(ft == FoodType.drink)
                {
                    category = "Drink";
                }else if(ft == FoodType.dessert)
                {
                    category = "Dessert";
                }

                food = items[i].getItemName();
                price = items[i].getItemPrice();

                command.Parameters.AddWithValue("TableNumber", tableNumber);
                command.Parameters.AddWithValue("Category", category);
                command.Parameters.AddWithValue("Food", food);
                command.Parameters.AddWithValue("Price", price);
                command.ExecuteNonQuery();
            }
           
            transaction.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            try
            {
                // Attempt to roll back the transaction.
                transaction.Rollback();
            }
            catch
            {
                // Do nothing here; transaction is not active.
            }
        }
    }
}
7
  • You're using a single command and adding the same parameters over and over to it. Just create a new OleDbCommand in each iteration of the for loop. Commented Jun 7, 2021 at 17:31
  • 3
    Stop using AddWithValue() dbdelta.com/addwithvalue-is-evil Commented Jun 7, 2021 at 17:37
  • SqlCommand Parameters Add vs. AddWithValue Commented Jun 7, 2021 at 17:58
  • @OlivierRogier Not in this case. Alejandro's solution worked. Commented Jun 7, 2021 at 21:06
  • @Alejandro -- Thanks! That fixed it for me. Commented Jun 7, 2021 at 21:06

1 Answer 1

2

Create params with a proper type before the loop. Set values in loop.

        ...
        command.CommandText = "Insert into Invoices (TableNumber, Category, Food, Price) VALUES (?,?,?,?)";

        command.CommandType = CommandType.Text;
        command.Parameters.Add("TableNumber", /*param type*/);
        command.Parameters.Add("Category", /*param type*/);
        command.Parameters.Add("Food", /*param type*/);
        command.Parameters.Add("Price", /*param type*/);

        Order order;
        order = tables[tableNumber - 1].getOrder();
        List<MenuItem> items = order.deepCopyMenuItems();
        for (int i = 0; i < items.Count(); i++)
        {
            FoodType ft =  items[i].getFoodType();
            category = "Main Course";
            if(ft == FoodType.maincourse)
            {
                category = "Main Course";
            }else if(ft == FoodType.drink)
            {
                category = "Drink";
            }else if(ft == FoodType.dessert)
            {
                category = "Dessert";
            }

            food = items[i].getItemName();
            price = items[i].getItemPrice();

            command.Parameters["TableNumber"].Value = tableNumber;
            command.Parameters["Category"].Value =  category;
            command.Parameters["Food"].Value =  food;
            command.Parameters["Price"].Value =  price;
            command.ExecuteNonQuery();
        }
        ...
Sign up to request clarification or add additional context in comments.

1 Comment

Since Alejandro's solution worked for me, I'm using that for now, but I am going to go over these "Add()"s. Thanks for the insight.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.