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

@{
/**/

}


<h2>Index</h2>
<div class="card-body">


    <div class="form-horizontal">


            <div class="form-group">

                <div class="row">

                    <div class="col-md-4">
                        <div>
                            @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" })
                        </div>

            



                        @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" })

                    </div>

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

                    <div class="col-md-4" style="background-color:lightskyblue; text-align:center;font-family:'Times New Roman'">
                        <h3 style="font-family:'Times New Roman'">Order Status</h3>
                        @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" })
                        <br />

                        @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)
                    </div>


                </div>
            </div>
   

        @Html.DropDownList("FooBarDropDown", new List<SelectListItem>
    {
    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" } )
    </div>

    <div>

        <div class="row">
            <div style="display: inline-block;">
                <div>
                    @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" })
                </div>
            </div>

            <div style="display: inline-block;">
                <div>
                    @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" })
                </div>
            </div>
         
                <div style="display: inline-block;">
                    <div>
                        @Html.LabelFor(model => model.Bale_QTY, "Bale_QTY", htmlAttributes: new { @class = "control-label col-md-4" })
                        <br />

                        @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" })
                    </div>
                </div>
                <div style="display: inline-block;">
                    <div>
                        @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" })
                    </div>
                </div>
         
            <div style="display: inline-block;">
                <div>
                    @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" })
                </div>
            </div>
            <div style="display: inline-block;">

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

            </div>
            <div style="display: inline-block;">

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

            </div>

            <div style="display: inline-block;">

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

            </div>
        </div>
        <div>
            @*<input type="button" value="Insert" id="btnInsert" />*@
            <br />
            <br />
            <table id="DataInsert" class="table table-striped table-bordered table-hover">
                <thead style="font-family:'Times New Roman'" class="nav-heading">
                    <tr>
                        <th>Barcode</th>
                        <th>QTY</th>

                        <th>Item</th>
                        <th >Codeitem</th>
                        <th >Pack No</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
            <br />
            <input type="button" value="Save" id="btnSave" />
        </div>
    </div>


</div>

<script src="~/Content/Template/vendor/ddlsearch.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $("#select2-1").change(function () {
            //console.log($("#select2-1").val());

            var orderId = $("#select2-1").val();
            if (orderId != "") {
                GetOrderqty(orderId);
                GetProduceqty(orderId);
                GetPackedqty(orderId);
                GetOrdernoID(orderId);
            }
            else {
                $("#quantity-label-2").text("QTY");
                $("#orderqty").empty();
            }
        });
    });

    function GetOrderqty(orderId) {

        $.ajax({
            async: true,
            type: 'GET',
            dataType: 'JSON',
            url: '/OrderPack/GetOrderqty',
            data: { orderId: orderId },
            success: function (data) {
                console.log(data);
                //populate the didden field
                $("#orderqty").val(data.orderqty);
                //populate the label
                $("#quantity-label-2").text(data.orderqty);
            },
            error: function () {
                alert("There is some problem to get.");
            }
        });
    }

    function GetProduceqty(orderId) {

        $.ajax({
            async: true,
            type: 'GET',
            dataType: 'JSON',
            url: '/OrderPack/GetProduceqty',
            data: { orderId: orderId },
            success: function (data) {
                console.log(data);
                //populate the didden field
                $("#prdqty").val(data.prdqty);
                //populate the label
                $("#quantity-label-3").text(data.prdqty);
            },
            error: function () {
                alert("There is some problem to get.");
            }
        });
    }

    function GetPackedqty(orderId) {

        $.ajax({
            async: true,
            type: 'GET',
            dataType: 'JSON',
            url: '/OrderPack/GetPackedqty',
            data: { orderId: orderId },
            success: function (data) {
                console.log(data);
                //populate the didden field
                $("#packQty").val(data.packQty);
                //populate the label
                $("#quantity-label-4").text(data.packQty);
            },
            error: function () {
                alert("There is some problem to get.");
            }
        });
    }
    function GetOrdernoID(orderId) {
        $.ajax({
            async: true,
            type: 'GET',
            dataType: 'JSON',
            url: '/OrderPack/GetOrdernoID',
            data: { orderId: orderId },
            success: function (data) {
                console.log(data);
                //populate the didden field
                $("#Orderno").val(data.OrderNo);
                //populate the label
                $("#Orderno-label-5").text(data.OrderNo);
            },
            error: function () {
                alert("There is some problem to get.");
            }
        });
    }

    $(document).ready(function () {
        $("#Search_Prdno").change(function () {


            var prdno = $("#Search_Prdno").val();
            if (prdno != "") {
                GetBarcodeSB(prdno);
                GetBarcodeweight(prdno);

            }
            else {

                $("#select2-3").empty();
            }

        });
    });

    function GetBarcodeSB(prdno) {

        $.ajax({
            async: true,
            type: 'GET',
            dataType: 'JSON',
            contentType: 'application/json; charset=utf-8',
            url: '/OrderPack/GetBarcodeSB',
            data: { prdno: prdno },
            success: function (data) {
                $('#select2-3').empty();
                $(data).each(function (index, item) {
                    $('#select2-3').append($('<option/>', { value: item.Value, text: item.Text }))
                        ;

                })
            },
            error: function () {
                alert("There is some problem to get.");
                $("#P_Prdno").empty();
                $("#P_OrderNo").empty();
                $("#I_Weigth").empty();
            }
        });
    }


    function GetBarcodeweight(prdno) {

        $.ajax({
            async: true,
            type: 'GET',
            dataType: 'JSON',
            url: '/OrderPack/GetBarcodeweight',
            data: { prdno: prdno },
            success: function (data) {
                console.log(data);
                //populate the didden field
                $("#Prdno").val(data.Prdno);
                //populate the label
                $("#Barcode_Bale").val(data.Prdno);

                //populate the didden field
                $("#OrderNo").val(data.OrderNo);
                //populate the Orderno
                $("#P_OrderNo").text(data.OrderNo);

                //populate the didden field
                $("#weight").val(data.weight);
                //populate the label
                $("#I_Weigth").text(data.weight);
                //populate the didden field
                $("#Pack_ID").val(data.PPID);
                //populate the label
                $("#Pack_ID").text(data.PPID);

              



                var orderNo1 = $('#P_OrderNo').html();
                var orderNoM = $('#Orderno-label-5').html();
                var packID = $('#Pack_ID').val();
             


                if (orderNoM == "OrderNo")
                {
                    alert("Please Select Order.");
                    $('#Search_Prdno').val(null);
                    $('#Search_Prdno').focus();
                }
                else if (orderNo1 != orderNoM)
                {
                    alert("Please Input Same Order.");
                    $('#Search_Prdno').val(null);
                    $('#Search_Prdno').focus();

                }
                else if (packID == "" || packID == "1")
                {
                    addrow();
                }
                else
                {
                    alert("Bale Already Packed..");
                    $('#Search_Prdno').val(null);
                    $('#Search_Prdno').focus();
                 

                }


              
                ////console.log($("#select2-1").val());
             
               
            },
            //error: function () {
            //    //alert("There is some problem to get.");

            //}
        });
    }



    function addrow() {
        $('#btnInsert').add(function () {
            if ($('#DataInsert tr:contains("' + $("#Barcode_Bale").val() + '")').length > 0) {
                alert("found duplicate values");
                $('#Search_Prdno').val(null);
                $('#Search_Prdno').focus();
            }
            else {
                var rowCount = $('#DataInsert tr').length;
                $('#R_QTY').html(rowCount);


                var tbody = $('#DataInsert tbody');
                var tr = $('<tr></tr>');
                tr.append('<td>' + $("#Barcode_Bale").val() + '</td>');
                tr.append('<td>' + $('#Bale_Qty').val() + '</td>');
                tr.append('<td>' + $('#select2-3').text() + '</td>');
                tr.append('<td>' + $('#select2-3').val() + '</td>');
                tr.append('<td >' + $('#Record_ID').val() + '</td>');
                tr.append('<td><input class="del" type="button" value="Delete" /></td>')
                tbody.append(tr);
          


            $('#Search_Prdno').val(null);
            $('#Search_Prdno').focus();
            }

        

        });


       

    };
   
   
            $("body").on("click", "#btnSave", function () {
            var smallbales = new Array();
            $("#DataInsert tbody tr").each(function () {
                var row = $(this);
                var smallbale = {};
                smallbale.Barcode_Bale = row.find("TD").eq(0).html();
                smallbale.Bale_Qty = row.find("TD").eq(1).html();
                smallbale.select2 = row.find("TD").eq(2).html();
                smallbale.Codeitem = row.find("TD").eq(3).html();
                smallbale.PID = row.find("TD").eq(4).html();
                smallbales.push(smallbale);
            });
            $.ajax({
                type: "POST",
                url: '/OrderPack/InsertPack',
                data: JSON.stringify(smallbales),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    alert("Record(s) inserted.");
                },
                error: function (r) {
                    alert(r.responseText);
                }
            });

        });
  
</script>

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<OrderVm> 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<OrderVm>();
            }



            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<OrderVm> Pida { get; set; }




    }

Answer 1

Hi @akhter hussain,

You just put the code in the same place.

public JsonResult InsertPack(List<OrderVm> smallbales)
        {         
            if (smallbales == null)
            {
                smallbales = new List<OrderVm>();
            }
            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.