Insert Data into Parent and child table using MVC

Question

Below is sql table structure

Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50))

Create table #Probale (Prdno int,Codeitem int,QTY int,PID int,Orderno int)

Create table #tbl_BalPacM (PID int,Orderno int,Date date)

Create table #tbl_PckDetail (PDID int, Prdno int,Codeitem int,QTY int,PID int)

Insert #ItemMasterFile values(11,'Baby Ligth')

Insert #Probale values (11111,11,1,Null,2)
Insert #Probale values (11112,11,1,Null,2)
Insert #Probale values (11113,11,1,Null,2)
Insert #Probale values (11114,11,1,Null,3)


Insert #tbl_BalPacM values(1001,2,'2023-03-24')

Insert #tbl_PckDetail values (1,11111,11,1,1001)
Insert #tbl_PckDetail values (2,11112,11,1,1001)
Insert #tbl_PckDetail values (3,11113,11,1,1001)

I am retrieving data into html table from #Probale table Sql, and inserting into #tabl_PckDetail table,but

i also want ,at the same time ,PID get insert into #tbl_BalPacM table ,when i press Save button.

#tbl_BalPacM is parent table and #tbl_PckDetail is child table.

Below is View

@model ERP_APP.Models.OrderVm

@{
/**/

}


Index



    


            

                

                    
                        
                            @Html.LabelFor(model => model.PID, "PID", htmlAttributes: new { @class = "control-label col-md-4" })

                            @Html.EditorFor(model => model.PID, new { htmlAttributes = new { @class = "form-control col-md-10", @id = "Record_ID" } })
                            @Html.ValidationMessageFor(model => model.PID, "", new { @class = "text-danger" })
                        

            



                        @Html.LabelFor(model => model.OrderNo, "Select Order", htmlAttributes: new { @class = "control-label col-md-5" })

                        @Html.DropDownList("OrderNo", (SelectList)ViewBag.OrderNo, "Select", htmlAttributes: new { @class = "form-control", @id = "select2-1" })

                        @Html.ValidationMessageFor(model => model.OrderNo, "", new { @class = "text-danger" })

                    

                    @Html.LabelFor(model => model.OrderNo, "OrderNo", htmlAttributes: new { @class = "control-label col-md-2", @id = "Orderno-label-5" })
                    @Html.HiddenFor(Model => Model.OrderNo)

                    
                        Order Status
                        @Html.LabelFor(model => model.orderqty, "Order", htmlAttributes: new { @class = "control-label col-md-2" })
                        @Html.LabelFor(model => model.prdqty, "Produce", htmlAttributes: new { @class = "control-label col-md-2" })
                        @Html.LabelFor(model => model.packQty, "Packed", htmlAttributes: new { @class = "control-label col-md-2" })
                        


                        @Html.LabelFor(model => model.orderqty, "Order", htmlAttributes: new { @class = "control-label  col-md-2", @id = "quantity-label-2" })
                        @Html.HiddenFor(Model => Model.orderqty)



                        @Html.LabelFor(model => model.prdqty, "Produce", htmlAttributes: new { @class = "control-label  col-md-2", @id = "quantity-label-3" })
                        @Html.HiddenFor(Model => Model.prdqty)



                        @Html.LabelFor(model => model.packQty, "Packed", htmlAttributes: new { @class = "control-label col-md-2", @id = "quantity-label-4" })
                        @Html.HiddenFor(Model => Model.packQty)
                    


                
            
   

        @Html.DropDownList("FooBarDropDown", new List
    {
    new SelectListItem{ Text="One", Value = "1" },
    new SelectListItem{ Text="Two", Value = "2" },
    new SelectListItem{ Text="Three", Value = "3" },
    }, "Select Type", htmlAttributes : new { @class = "form-control col-md-3 ", @id = "select2-2" } )
    

    

        
            
                
                    @Html.LabelFor(model => model.Prdno, "Barcode", htmlAttributes: new { @class = "control-label col-md-4" })

                    @Html.EditorFor(model => model.Prdno, new { htmlAttributes = new { @class = "form-control col-md-10", @id = "Search_Prdno" } })
                    @Html.ValidationMessageFor(model => model.Prdno, "", new { @class = "text-danger" })
                
            

            
                
                    @Html.LabelFor(model => model.Codeitem, "Select Item", htmlAttributes: new { @class = "control-label col-md-3" })

                    @Html.DropDownList("codeitemId", (SelectList)ViewBag.Codeitem, "Select", htmlAttributes: new { @class = "form-control", @id = "select2-3" })

                    @Html.ValidationMessageFor(model => model.Codeitem, "", new { @class = "text-danger" })
                
            
         
                
                    
                        @Html.LabelFor(model => model.Bale_QTY, "Bale_QTY", htmlAttributes: new { @class = "control-label col-md-4" })
                        


                        @Html.EditorFor(model => model.Bale_QTY, new { htmlAttributes = new { @class = "form-control col-md-10", @id = "Bale_Qty", @Value = "1" , @disabled = "disabled" } })
                        @Html.ValidationMessageFor(model => model.Bale_QTY, "", new { @class = "text-danger" })
                    
                
                
                    
                        @Html.LabelFor(model => model.Barcode_Bale, "BarCode", htmlAttributes: new { @class = "control-label col-md-4" })

                        @Html.EditorFor(model => model.Barcode_Bale, new { htmlAttributes = new { @class = "form-control col-md-10", @id = "Barcode_Bale", @disabled = "disabled" } })
                        @Html.ValidationMessageFor(model => model.Barcode_Bale, "", new { @class = "text-danger" })
                    
                
         
            
                
                    @Html.LabelFor(model => model.PPID, "Pack_ID", htmlAttributes: new { @class = "control-label col-md-4" })

                    @Html.EditorFor(model => model.PPID, new { htmlAttributes = new { @class = "form-control col-md-10", @id = "Pack_ID", @disabled = "disabled" } })
                    @Html.ValidationMessageFor(model => model.PPID, "", new { @class = "text-danger" })
                
            
            

                @Html.LabelFor(model => model.OrderNo, "OrderNo", htmlAttributes: new { @class = "control-label col-md-2", @id = "P_OrderNo" })
                @Html.HiddenFor(Model => Model.OrderNo)

            
            

                @Html.LabelFor(model => model.weight, "Weight", htmlAttributes: new { @class = "control-label col-md-2", @id = "I_Weigth" })
                @Html.HiddenFor(Model => Model.weight)

            

            

                @Html.LabelFor(model => model.R_QTY, "Count", htmlAttributes: new { @class = "control-label col-md-2", @id = "R_QTY" })
             

            
        
        
            @**@
            

            

            
                
                    
                        Barcode
                        QTY

                        Item
                        Codeitem
                        Pack No
                        Action

Controller

  public class OrderPackController : Controller
    {
        SilverProductionEntities DB = new SilverProductionEntities();
        // GET: OrderPack


        // GET: BigBale

       

        RecordID sdb = new RecordID();
        public ActionResult Index()
        {

            var OrderNo = 0;
            ViewBag.OrderNo = new SelectList(DB.SalesOrders.Where(bt => bt.OrderNo > OrderNo && bt.Status == "Open"), "Orderno", "Order_Ref_No", "0");


            var codeitemId = 0;
            ViewBag.codeitemId = new SelectList(DB.ItemMasterFiles.Where(bt => bt.CodeItem > codeitemId ), "CodeItem", "Descriptionitem", "0");


            var pacKMV = new OrderVm();

            pacKMV.Pida = sdb.GeneatePackID();

            pacKMV.PID =Convert.ToInt32( pacKMV.Pida[0].PID);

            return View(pacKMV);

        }

        [HttpGet]
        public JsonResult GetOrderqty(int orderId)
        {
            OrderVm vm = new OrderVm();
            vm.OrderNo = orderId;
            vm.orderqty = DB.OrderDetails.Where(o => o.OrderNO == orderId).Sum(o => o.orderqty);
            return Json(vm, JsonRequestBehavior.AllowGet);


        }
        [HttpGet]
        public JsonResult GetProduceqty(int orderId)
        {
            OrderVm vm = new OrderVm();
            vm.OrderNo = orderId;
            vm.prdqty = DB.Probales.Where(o => o.OrderNo == orderId).Sum(o => o.prdqty);
            return Json(vm, JsonRequestBehavior.AllowGet);


        }
        [HttpGet]
        public JsonResult GetPackedqty(int orderId)
        {
            OrderVm vm = new OrderVm();
            vm.OrderNo = orderId;
            vm.packQty = (from e in DB.tbl_BalPacM.Where(e => e.OrderNo == orderId)
                          join d in DB.tbl_PckDetail on e.PID equals d.PID
                          select d).Sum(d => d.QTY);
            return Json(vm, JsonRequestBehavior.AllowGet);


        }

        [HttpGet]
        public JsonResult GetOrdernoID(int orderId)
        {
            OrderVm vm = new OrderVm();
            vm.OrderNo = orderId;
            vm.OrderNo = (from o in DB.SalesOrders.Where(o => o.OrderNo == orderId) select o.OrderNo).FirstOrDefault();      // .Sum(o => o.OrderNo);
            return Json(vm, JsonRequestBehavior.AllowGet);


        }

        [HttpGet]
        public JsonResult GetBarcodeSB(int prdno)
        {
           // int? Codeitem = DB.Probales.First(a => a.Prdno == prdno).Codeitem;
            var STATUS_LIST = (from p in DB.Probales.Where(p => p.Prdno == prdno)
                               join i in DB.ItemMasterFiles on p.Codeitem equals i.CodeItem
                               where p.Prdno == prdno
                               select
                                   new SelectListItem()
                                   {
                                       Text = i.Descriptionitem,
                                       Value = i.CodeItem.ToString(),

                                   }).ToList();
            return Json(STATUS_LIST, JsonRequestBehavior.AllowGet);

        }
        [HttpGet]
        public JsonResult GetBarcodeweight(int prdno)
        {



            var query = (from p in DB.Probales.Where(p => p.Prdno == prdno && p.DelID ==null)
                         join i in DB.ItemMasterFiles on p.Codeitem equals i.CodeItem

                         select new { ItemMasterFile = i, Probale = p }).ToList();

            var user = query.FirstOrDefault();
            var model = new OrderVm()
            {

                Prdno = user.Probale.Prdno,
                OrderNo = user.Probale.OrderNo,
                weight = user.ItemMasterFile.weight,
                PPID =user.Probale.PID

            };
            return Json(model, JsonRequestBehavior.AllowGet);

        }

       
        public JsonResult InsertPack(List smallbales)
        {
          
            var newpackM = new tbl_BalPacM();
            newpackM.PID = smallbales.PID;

            newpackM.Date = DateTime.Now;
            DB.tbl_BalPacM.Add(newpackM);
            DB.SaveChanges();




            if (smallbales == null)
            {
                smallbales = new List();
            }



            foreach (OrderVm smallbale in smallbales)
                {

                if (smallbale.Prdno != 0)
                {
                    var newbigbale = new tbl_PckDetail();
                    newbigbale.Prdno = smallbale.Barcode_Bale;
                    newbigbale.QTY = smallbale.Bale_QTY;
                    newbigbale.Codeitem = smallbale.Codeitem;
                    newbigbale.PID = smallbale.PID;
                    DB.tbl_PckDetail.Add(newbigbale);

                }
            }
          
          int  insertedRecords = DB.SaveChanges();
            return Json(insertedRecords);
        }



    }
}

Model

   public class OrderVm
    {

        [Display(Name = "Select Order")]
        public int? OrderNo { get; set; }
        public int? SOrderNo { get; set; }
        public int? Codeitem { get; set; }
        [Display(Name = "Input Barcode")]
        public int? Prdno { get; set; }
        public int? Barcode_Bale { get; set; }
        public int? orderqty { get; set; }
        public int? prdqty { get; set; }
        public int? packQty { get; set; }

        public int PID { get; set; }

        public int? PPID { get; set; }
        public int? Bale_QTY { get; set; }
        public int? R_QTY { get; set; }
        public int? weight { get; set; }

        public Probale smallbale { get; set; }
        public ItemMasterFile Itemasterfile { get; set; }


        public List Pida { get; set; }




    }

Accepted Answer

Hi @akhter hussain,

You just put the code in the same place.

public JsonResult InsertPack(List smallbales)
        {         
            if (smallbales == null)
            {
                smallbales = new List();
            }
            foreach (OrderVm smallbale in smallbales)
            {
                if (smallbale.Prdno != 0)
                {
                    var newbigbale = new tbl_PckDetail();
                    newbigbale.Prdno = smallbale.Barcode_Bale;
                    newbigbale.QTY = smallbale.Bale_QTY;
                    newbigbale.Codeitem = smallbale.Codeitem;
                    newbigbale.PID = smallbale.PID;
                    
                    var newpackM = new tbl_BalPacM();
                    newpackM.PID = smallbales.PID;
                    newpackM.Date = DateTime.Now;
                   
                    DB.tbl_BalPacM.Add(newpackM);
                 
                    DB.tbl_PckDetail.Add(newbigbale);
                }
            }

            int insertedRecords = DB.SaveChanges();
            return Json(insertedRecords);
        }

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Insert Data into Parent and child table using MVC

0 additional answers