I have 2 tables:

1. Order – containing customer orders
2. Composition – containing the composition of each order.

I added them from DataSource Window

I wanted that when selecting a row in the Order table, the corresponding composition order would be displayed in the Composition table and implemented this through DataSource by linking data to tables.

At this stage, everything works, but when I add a new order, it is added to the database, but not updated in the order table in the app, until I restart the application. There is part of the code from my form:

        private Utilities _utilities;
        private DataGridViewRow _line;
        private ArrayList _comboBoxList;
        private int _amountLeft;

        public ClientForm()
        {
            InitializeComponent();
        }

        private void ClientForm_Load(object sender, EventArgs e)
        {
            _utilities = new Utilities();
            _comboBoxList = new ArrayList() { "Закончен", "Начат", "В обработке" };

            this.compositionTableAdapter.Fill(this.fabricDBDataSet.Composition);
            this.orderTableAdapter.Fill(this.fabricDBDataSet.Order);
            this.modelTableAdapter.Fill(this.fabricDBDataSet.Model);
            this.fabricTableAdapter.Fill(this.fabricDBDataSet.Fabric);

            comboBoxStatusFilter.Items.AddRange(_comboBoxList.ToArray());
            comboBoxColorFilter.DataSource = _utilities.LoadDistinctItems("SELECT Color FROM Fabric");
            comboBoxFabricNameFilter.DataSource = _utilities.LoadDistinctItems("SELECT Name FROM Fabric");

            _utilities.ShowTable(fabricDataGridView, "[Fabric]");
        }

        private void addProductButton_Click(object sender, EventArgs e)
        {
            _line = fabricDataGridView.SelectedRows[0];

            string fabricName = _line.Cells[0].Value.ToString();
            int fabricAreaInput = Convert.ToInt32(textBoxFabricCount.Text);
            int fabricPrice = Convert.ToInt32(_line.Cells[2].Value);
            string fabricColor = _line.Cells[3].Value.ToString();
            int fabricId = Convert.ToInt32(_line.Cells[4].Value);

            if (Convert.ToInt32(_line.Cells[1].Value) >= fabricAreaInput)
            {
                _amountLeft = Convert.ToInt32(_line.Cells[1].Value) - fabricAreaInput;
                _line.Cells[1].Value = _amountLeft;
                SqlCommand commandFabric = new SqlCommand($"Update [Fabric] Set Area='{_amountLeft}' WHERE Id='{_line.Cells[4].Value}'", MainForm.Connection);
                commandFabric.ExecuteNonQuery();

                foreach (DataGridViewRow row in shoppingCartDataGridView.Rows)
                {
                    if (Convert.ToInt32(row.Cells[4].Value) == fabricId)
                    {
                        row.Cells[1].Value = Convert.ToInt32(row.Cells[1].Value) + fabricAreaInput;
                        return;
                    }
                }
            }
            else
            {
                MessageBox.Show("Введите корректное количество!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            shoppingCartDataGridView.Rows.Add(fabricName, fabricAreaInput, fabricPrice, fabricColor, fabricId);
        }

        private void buttonBuy_Click(object sender, EventArgs e)
        {
            List<DataGridViewRow> selectedRows = new List<DataGridViewRow>();
            int orderId = 0;
            int totalPrice = 0;

            foreach (DataGridViewRow row in shoppingCartDataGridView.Rows)
            {
                DataGridViewCheckBoxCell checkBoxCell = row.Cells["IsSelectedToBuy"] as DataGridViewCheckBoxCell;
                totalPrice = Convert.ToInt32(row.Cells[1].Value) * Convert.ToInt32(row.Cells[2].Value);

                if (Convert.ToBoolean(checkBoxCell.Value) == true)
                {
                    selectedRows.Add(row);
                }
            }

            if (totalPrice != 0)
            {
                try
                {
                    SqlCommand commandOrder = new SqlCommand($"INSERT INTO [Order] (CustomerName, OrderDate, TotalPrice, Status) VALUES(@CustomerName, @OrderDate, @TotalPrice, @Status); SELECT SCOPE_IDENTITY();", MainForm.Connection);
                    commandOrder.Parameters.AddWithValue("@CustomerName", textBoxCustomerName.Text);
                    commandOrder.Parameters.AddWithValue("@OrderDate", DateTime.Now.Date);
                    commandOrder.Parameters.AddWithValue("@TotalPrice", totalPrice);
                    commandOrder.Parameters.AddWithValue("@Status", "Начат");
                    orderId = Convert.ToInt32(commandOrder.ExecuteScalar());

                    var result = MessageBox.Show("Заказ успешно сохранен!nЖелаете сохранить чек?", "Успех", MessageBoxButtons.YesNo, MessageBoxIcon.Information);

                    if (result == DialogResult.Yes)
                    {
                        SavePurchaseReceipt();
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("Не удалось выполнить заказ, убедитесь, что корректно выбрали товар и количество!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }

            if (orderId != 0)
            {
                foreach (var row in selectedRows)
                {
                    try
                    {
                        SqlCommand commandComposition = new SqlCommand(
                            $"INSERT INTO [Composition] (FabricName, UnitPrice, Amount, FabricId, Color, OrderId) VALUES(@FabricName, @UnitPrice, @Amount, @FabricId, @Color, @OrderId)", MainForm.Connection);

                        commandComposition.Parameters.AddWithValue("@FabricId", row.Cells[4].Value);
                        commandComposition.Parameters.AddWithValue("@Amount", row.Cells[1].Value);
                        commandComposition.Parameters.AddWithValue("@FabricName", row.Cells[0].Value);
                        commandComposition.Parameters.AddWithValue("@UnitPrice", row.Cells[2].Value);
                        commandComposition.Parameters.AddWithValue("@Color", row.Cells[3].Value);
                        commandComposition.Parameters.AddWithValue("@OrderId", orderId);

                        commandComposition.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Не удалось добавить в корзину, убедитесь, что корректно выбрали товар и его количество!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
            }
        }

If I try to output updated data to the order table by placing the data of the query I created there, the connection is broken and even though I see the added the order, I cannot interact with the composition of the order, because it stops responding to the order selection.

I tried to update the table by executing an sql query by selecting the entire value of the table, but then the relationship between the tables disappears.

How can this be fixed?