Cannot Fetch the Last_inserted_id in a foreach loop inside another foreach mysql C#

  ado, c++, mysql, windows, winforms

enter image description here

the image above shows the output inside mysql Tables.
my problem is it cannot get the value of last_inserted_id value in ORDERTABLE, as it is the reference value in ItemTable itemid.

this is my code in SAVEORDER and SAVEITEM

the description =is where I split the Data into a foreach and save it in item table.
each split should save each data with corresponding orderid, I tried last_inserted_id and not MaxID as i want it to be done by 1-3 user simultaneously without having problem in data integrity.

 private void SaveOrder()
        {
         
            try
                {
                        foreach (DataGridViewRow row in DGSingleOrders.Rows)
                        {
                            using (MySqlCommand cmd = new MySqlCommand())
                            {
                                cmd.CommandText = "INSERT INTO single_order (customerid, category, type, description, code, color, price, status, transaction, attendee) VALUES (@customerid, @category, @type, @description, @code, @color, @price, @status, @transaction, @attendee)";
                                cmd.CommandType = CommandType.Text;
                                cmd.Connection = db.con;
                                db.con.Open();
                                cmd.Parameters.Add("@customerid", MySqlDbType.VarChar).Value = OrderID;
                                cmd.Parameters.Add("@category", MySqlDbType.VarChar).Value = row.Cells[0].Value.ToString();
                                cmd.Parameters.Add("@type", MySqlDbType.VarChar).Value = row.Cells[1].Value.ToString();
                                cmd.Parameters.Add("@description", MySqlDbType.VarChar).Value = row.Cells[2].Value.ToString();
                                cmd.Parameters.Add("@code", MySqlDbType.VarChar).Value = row.Cells[3].Value.ToString();
                                cmd.Parameters.Add("@color", MySqlDbType.VarChar).Value = row.Cells[4].Value.ToString();
                                cmd.Parameters.Add("@price", MySqlDbType.VarChar).Value = row.Cells[5].Value.ToString();
                                cmd.Parameters.Add("@status", MySqlDbType.VarChar).Value = SaveAs;
                                cmd.Parameters.Add("@transaction", MySqlDbType.VarChar).Value = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss tt");
                                cmd.Parameters.Add("@attendee", MySqlDbType.VarChar).Value = attendee;
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                                db.con.Close();
                            }

                            //Check this first
                            using (MySqlCommand cmd = new MySqlCommand())
                            {
                                cmd.CommandText = "SELECT LAST_INSERT_ID()";
                                cmd.Connection = db.con;
                                db.con.Open();
                                using (MySqlDataReader reader = cmd.ExecuteReader())
                                {
                          
                                    long id;
                                    while (reader.Read())
                                    {
                                        id = reader.GetInt64(0);
                                        ItemID = id.ToString();                                      
                                    }
                                    db.con.Close();
                                }
                         
                            }

               
                    
                         }

                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message,"ORDER");
                    db.con.Close();
                    return;
                }
            
        }


  private void SaveItem()
        {

            //Getting Each Data
            foreach (DataGridViewRow row in DGSingleOrders.Rows)
            {
         
                string item = row.Cells[1].Value.ToString();
                string type = row.Cells[0].Value.ToString();
                string str = row.Cells[2].Value.ToString();
                string code = row.Cells[3].Value.ToString();
                string color = row.Cells[4].Value.ToString();

                List<string> descr = str.Split('+').ToList<string>();
                //Splitting Description

                //Adding Split to a DataTable

                foreach (var l in descr)
                {
                    OrderRow = l;

                        try
                        {
                            using (MySqlCommand cmd = new MySqlCommand())
                            {
                                cmd.CommandText = "INSERT INTO single_item (orderid, type, item, brand, code, color, eventdate, orderstatus, itemstatus, orderchecker, attendee)VALUES(@orderid, @type, @item, @brand, @code, @color, @eventdate, @orderstatus, @itemstatus, @orderchecker, @attendee)";
                                cmd.CommandType = CommandType.Text;
                                cmd.Connection = db.con;
                                cmd.Parameters.Add("@orderid", MySqlDbType.VarChar).Value = ItemID;
                                cmd.Parameters.Add("@type", MySqlDbType.VarChar).Value = type;
                                cmd.Parameters.Add("@item", MySqlDbType.VarChar).Value = OrderRow;
                                cmd.Parameters.Add("@brand", MySqlDbType.VarChar).Value = "-";
                                cmd.Parameters.Add("@code", MySqlDbType.VarChar).Value = code;
                                cmd.Parameters.Add("@color", MySqlDbType.VarChar).Value = color;
                                cmd.Parameters.Add("@eventdate", MySqlDbType.VarChar).Value = eventDay;
                                cmd.Parameters.Add("@orderstatus", MySqlDbType.VarChar).Value = SaveAs;
                                cmd.Parameters.Add("@itemstatus", MySqlDbType.VarChar).Value = "Good";
                                cmd.Parameters.Add("@orderchecker", MySqlDbType.VarChar).Value = "Not Updated";
                                cmd.Parameters.Add("@attendee", MySqlDbType.VarChar).Value = atendee;
                                db.con.Open();
                                cmd.ExecuteNonQuery();
                                db.con.Close();
                                dtItems.Clear();
                            }
                        }


                        catch (Exception e)
                        {
                            MessageBox.Show(e.Message);
                            db.con.Close();
                            return;
                        }
                    
                 SaveItem();
                }

            }

I added SaveItem(); Method inside SaveOrder(); ForeachLoop so that it can save together with the ORDER table, and can fetch the data of Last_inserted_id but it still get the first ID, I also tried putting it out the foreach loop, but then I can’t get the result of the Order ID, and save data differently.

Source: Windows Questions

LEAVE A COMMENT