locked
After Retrieving Data from DataBase into Master Detail Form (Master Textboxes,Child Gridview),gridview data is not inserting. RRS feed

  • Question

  • User-367318540 posted

    I am Retrieving data from two table into webform,in which gridview and Textbox ,then again i am trying to inserting data into same table with new ID ,but data is inserting into Master Table ,but not getting insert into Child Table

    Here is my Retrieving  query 

     String strQuery = "select Orderno,Order_Ref_No From tbl_BalPacM where" +
                                               " PID = @PID";
                // SqlConnection con = new SqlConnection();
                SqlCommand cmde = new SqlCommand();
                cmde.Parameters.AddWithValue("@PID", PID);
                cmde.CommandType = CommandType.Text;
                cmde.CommandText = strQuery;
                cmde.Connection = con;
                try
                {
                    con.Open();
                    SqlDataReader sdr = cmde.ExecuteReader();
                    while (sdr.Read())
                    {
    
    
                        txtOrder.Text = sdr["Orderno"].ToString();
                        txtRefno.Text = sdr["Order_Ref_No"].ToString();
    
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                SqlCommand cmdbno = new SqlCommand("[Sp_PackDetailGV_CRUD]", con);
                cmdbno.Parameters.AddWithValue("@Action", "SELECT");
                cmdbno.CommandType = CommandType.StoredProcedure;
                cmdbno.Parameters.AddWithValue("@PID", PID);
                SqlDataAdapter adptbno = new SqlDataAdapter(cmdbno);
                DataTable dtbno = new DataTable();
                adptbno.Fill(dtbno);
                GVPACKLISted.DataSource = dtbno;
                GVPACKLISted.DataBind();
                cmdbno.Dispose();
                con.Close();

    Here is my inserting query behind button

     if
                    (txtRefno.Text == "")
                {
                    Response.Write("<script language='javascript'>alert('Please Input Orderno Ref.');</script>");
                }
                else
                {
    
                    using (SqlCommand cmd = new SqlCommand("[Sp_BalPakinsert]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        cmd.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
    
                        cmd.Parameters.AddWithValue("@OrderNo", txtOrder.Text);
    
                        con.Open();
                        PID = Convert.ToInt32(cmd.ExecuteScalar());
                        con.Close();
    
                        DataTable dt = (DataTable)ViewState["dt"];
                        int codeitem, qty, orderno, prdno;
    
                        foreach (DataRow row in dt.Rows)
    
                        {
    
                            //pid = int.Parse(row["_PID"].ToString());
                            prdno = int.Parse(row["Prdno"].ToString());
                            orderno = int.Parse(row["Orderno"].ToString());
                            qty = int.Parse(row["QTY"].ToString());
                            codeitem = int.Parse(row["Codeitem"].ToString());
    
                            this.InsertRows(codeitem, qty, orderno, prdno);
                        }
                    }
                }
            }
    
            private void InsertRows(int codeitem, int qty, int orderno, int prdno)
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con))
                {
    
                    cmd.CommandType = CommandType.StoredProcedure;
    
                    // cmd.Parameters.AddWithValue("@PDID", _PID);
                    cmd.Parameters.AddWithValue("@PID", PID);
                    cmd.Parameters.AddWithValue("@CodeItem", codeitem);
                    cmd.Parameters.AddWithValue("@QTY", qty);
                    cmd.Parameters.AddWithValue("@Orderno", orderno);
                    cmd.Parameters.AddWithValue("@Prdno", prdno);
    
                    cmd.ExecuteNonQuery();
    
                    con.Close();
                }
            }
    
    
            public object PID { get; set; }
        }
                 
            }
        
    

    Thursday, August 15, 2019 3:17 PM

Answers

  • User665608656 posted

    Hi akhterr,

    I found that you didn't save data in ViewState["dt"], so after you save the first table ,then you cannot enter the foreach loop to save the second table.

    And i found you save the viewstate in the BindGrid method which called ViewState["CurrentTable"], the name is different from the ViewState["dt"].

    I don't know the relationship between your ViewState["dt"] and ViewState["CurrentTable"], but if you are foreach cycling ViewState["dt"], your ViewState["dt"] does not have any rows of data, you need to bind the ViewState["dt"] data when you click ‘Add’ button and then store it cyclically.

     DataTable dt = (DataTable)ViewState["dt"];
                        int pid, codeitem, qty, orderno, prdno;
    
                        foreach (DataRow row in dt.Rows)
                        {
                            cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con);
    
    
                            cmd.CommandType = CommandType.StoredProcedure;
    
                            pid = int.Parse(row["PID"].ToString());
                            prdno = int.Parse(row["Prdno"].ToString());
                            orderno = int.Parse(row["Orderno"].ToString());
                            qty = int.Parse(row["QTY"].ToString());
                            codeitem = int.Parse(row["Codeitem"].ToString());
    
                            //this.InsertRows(codeitem, qty, orderno, prdno);
                            //            }
                            //        }
                            //    }
                            //}
    
                            //   private void InsertRows(int codeitem, int qty, int orderno, int prdno)
                            //  {
                            //     con.Open();
    
    
                            // cmd.Parameters.AddWithValue("@PDID", _PID);
                            cmd.Parameters.AddWithValue("@PID", PID);
                            cmd.Parameters.AddWithValue("@CodeItem", codeitem);
                            cmd.Parameters.AddWithValue("@QTY", qty);
                            cmd.Parameters.AddWithValue("@Orderno", orderno);
                            cmd.Parameters.AddWithValue("@Prdno", prdno);
    
                            cmd.ExecuteNonQuery();
    
                            con.Close();
                        }

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 20, 2019 2:18 AM

All replies

  • User665608656 posted

    Hi akhterr,

    According to your description, which tables are the Master table and the Child table you mentioned? And what the relationship between them?

    I see that you first call the stored procedure of "Sp_BalPakinsert" in the code of inserting query.

    It should store the first table and get the ID value of the current insert, then store the second table by calling the stored procedure of "[SP_BalPakDetail_Insert]", and store the second table with the ID obtained previously as a parameter, right?

    If so, you can refer to this link : Insert into multiple tables using INSERT_IDENTITY - ADO.Net

    I try to test your code and see your previous case, because you did not provide detailed data and structures of your database tables, as well as the contents of some stored procedures, I can not successfully test the code you gave.

    I hope you can provide us with all the details about the structure of the tables and the data and the stored procedures  so that we can find the issue more quickly.

    Best Regards,

    YongQing.

    Friday, August 16, 2019 2:21 AM
  • User-367318540 posted

    Hi YongQing,

    It should store the first table and get the ID value of the current insert, then store the second table by calling the stored procedure of "[SP_BalPakDetail_Insert]", and store the second table with the ID obtained previously as a parameter, right?

    Yes ,you said right,here is my Master Table structure with Insert Procedure

    CREATE TABLE [dbo].[tbl_BalPacM](
    	[PID] [int] IDENTITY(1,1) NOT NULL,
    	[OrderNo] [int] NULL,
    	[Order_Ref_No] [varchar](50) NULL,
    	[Date] [varchar](12) NULL,
     CONSTRAINT [PK_tbl_BalPacM] PRIMARY KEY CLUSTERED 
    (
    	[PID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    ---Store Procedure
    
    Create PROCEDURE [dbo].[Sp_BalPakinsert]
    
    
    
    
     @OrderNo int,
    @Order_Ref_No varchar(50)
    
    as 
     BEGIN
        INSERT INTO tbl_BalPacM (OrderNo,Order_Ref_No) VALUES (@OrderNo,@Order_Ref_No)
       SELECT SCOPE_IDENTITY()
    
        end

    Here Child table with Store Procedure...

    ----Table Structure---
    
    CREATE TABLE [dbo].[tbl_PckDetail](
    	[PDID] [int] IDENTITY(1,1) NOT NULL,
    	[Prdno] [int] NULL,
    	[Codeitem] [int] NULL,
    	[OrderNo] [int] NULL,
    	[QTY] [int] NULL,
    	[PID] [int] NULL,
     CONSTRAINT [PK_tbl_PckDetail] PRIMARY KEY CLUSTERED 
    (
    	[PDID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    
    ------Store Procedure---
    
    
    
    Create PROCEDURE [dbo].[SP_BalPakDetail_Insert]
    
    
    --@PDID int,
    @Orderno int,
    @Prdno int,
    @PID int Output,
    @Codeitem int,
    @QTY int
    as begin
    
    --Insert
    
       INSERT  INTO tbl_PckDetail (PID,Codeitem,QTY,Orderno,Prdno) Values(@PID,@Codeitem,@QTY,@Orderno,@Prdno)
      --  set @PID=SCOPE_IDENTITY()
      --  --Update
      --  UPDATE tbl_PckDetail
    		--SET
    		--	OrderNO=@OrderNO,
    		--    Prdno=@Prdno,
    		--	CodeItem=@CodeItem,
    		--	qty=@QTY,
    		-- 	PID=@PID
    		--WHERE PDID = @PDID
        
        end

    Note: Same query i am using for inserting data into same table ,but not retrieving data from database ,using viewstate ,but having issue when i am retrieving data from database then data from gridview is not getting inserted into db.

    Friday, August 16, 2019 4:02 AM
  • User665608656 posted

    Hi akhterr,

    According to your code , I have tested it.

    Let me confirm with you that when you store data to these two tables, there is no error. When you search the data stored from the database, there is no corresponding record display, right?

    If so, it has nothing to do with your storage process or logic.

    In your first post code, I found that you stored the PID parameters as public fields:

     public object PID {get; set;}

    This is true when you save synchronously.

    But after you save the data and read the new data from the database, the page has been refreshed and all the common variables including the PID field have been reloaded.

    So when you get the data from the database, the parameter PID is null, which is why you can't find the data (you can confirm the existence of the data in the database)

    If that's the case, I recommend that you use session or viewstate to store the value of the PID to ensure that you can still read the value after the page refreshes.

    You can refer to the following code:

      using (SqlCommand cmd = new SqlCommand("[Sp_BalPakinsert]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        cmd.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
    
                        cmd.Parameters.AddWithValue("@OrderNo", txtOrder.Text);
    
                        con.Open();
                        PID = Convert.ToInt32(cmd.ExecuteScalar());
    Session["PID"] = PID; con.Close(); DataTable dt = (DataTable)ViewState["dt"]; int codeitem, qty, orderno, prdno; foreach (DataRow row in dt.Rows) { //pid = int.Parse(row["_PID"].ToString()); prdno = int.Parse(row["Prdno"].ToString()); orderno = int.Parse(row["Orderno"].ToString()); qty = int.Parse(row["QTY"].ToString()); codeitem = int.Parse(row["Codeitem"].ToString()); this.InsertRows(codeitem, qty, orderno, prdno); } }
      
     String strQuery = "select Orderno,Order_Ref_No From tbl_BalPacM where" +
                                               " PID = @PID";
                // SqlConnection con = new SqlConnection();
                SqlCommand cmde = new SqlCommand();
                cmde.Parameters.AddWithValue("@PID", Session["PID"]);
                cmde.CommandType = CommandType.Text;
                cmde.CommandText = strQuery;
                cmde.Connection = con;
                try
                {
                    con.Open();
                    SqlDataReader sdr = cmde.ExecuteReader();
                    while (sdr.Read())
                    {
    
    
                        txtOrder.Text = sdr["Orderno"].ToString();
                        txtRefno.Text = sdr["Order_Ref_No"].ToString();
    
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                SqlCommand cmdbno = new SqlCommand("[Sp_PackDetailGV_CRUD]", con);
                cmdbno.Parameters.AddWithValue("@Action", "SELECT");
                cmdbno.CommandType = CommandType.StoredProcedure;
                cmdbno.Parameters.AddWithValue("@PID", Session["PID"]);
                SqlDataAdapter adptbno = new SqlDataAdapter(cmdbno);
                DataTable dtbno = new DataTable();
                adptbno.Fill(dtbno);
                GVPACKLISted.DataSource = dtbno;
                GVPACKLISted.DataBind();
                cmdbno.Dispose();
                con.Close();
    

    Best Regards,

    YongQing.

    Friday, August 16, 2019 5:57 AM
  • User-367318540 posted

    HI YongQing.

    i will insert New PID  into db, Not previous retrieve PID ..

    i used session but giving error below

    The parameterized query '(@PID nvarchar(4000))select Orderno,Order_Ref_No From tbl_BalPac' expects the parameter '@PID', which was not supplied.

    Note: with my previous code in Loop( foreach (DataRow row in dt.Rows) ) i debug it,it just read(in dt.Rows) 

    Friday, August 16, 2019 7:19 AM
  • User665608656 posted

    Hi akhterr,

    The parameterized query '(@PID nvarchar(4000))select Orderno,Order_Ref_No From tbl_BalPac' expects the parameter '@PID', which was not supplied.

    This error message indicates that you used an unassigned session, where the session is empty.

    Note: with my previous code in Loop( foreach (DataRow row in dt.Rows) ) i debug it,it just read(in dt.Rows) 

    What do you mean by "it just read(in dt.Rows)"? Did the code not enter the loop?

    If it do not enter the loop, the code will not perform the second stored behavior, and the second table will not be inserted into the data.

    I hope you can describe your needs in detail and provide us with known results, which will help us understand and solve your issue faster.

    And I hope you can provide complete code and data.

    Best Regards,

    YongQing.

    Friday, August 16, 2019 7:58 AM
  • User-367318540 posted

    Hi YongQing,

    here is my data 

    CREATE TABLE #tbl_BalPacM (PID INT,OrderNo INT,Order_Ref_No VARCHAR(50));
    CREATE TABLE #tbl_PckDetail (PDID INT,Prdno INT,Codeitem INT,OrderNo INT, QTY INT,PID INT);
    CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50));
    
    GO
    INSERT INTO #ItemMasterFile VALUES(1,'A')
    , (2,'B')
    , (3,'C')
    , (4,'D')
    , (5,'e');
    INSERT INTO #tbl_BalPacM VALUES(1,4,'AM_01')
    INSERT INTO #tbl_PckDetail VALUES(1,10001,1,4,1,1),(2,10002,2,4,1,1),(3,10003,2,4,1,1),(4,10004,2,4,1,1);

    here is my html

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PackListED.aspx.cs" Inherits="WebApplication1.PackListED" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    
        <style>
            .grdContent
            {
                width: 80%;
                border: solid 2px black;
                min-width: 80%;
            }
            .header
            {
                background-color: #646464;
                font-family: Arial;
                color: White;
                border: none 0px transparent;
                height: 25px;
                text-align: center;
                font-size: 16px;
            }
            .rows
            {
                background-color: #fff;
                font-family: Arial;
                font-size: 14px;
                color: #000;
                min-height: 25px;
                text-align: left;
                border: none 0px transparent;
            }
            .rows:hover
            {
                background-color: #ff8000;
                font-family: Arial;
                color: #fff;
                text-align: left;
            }
            .selectedrow
            {
                background-color: #ff8000;
                font-family: Arial;
                color: #fff;
                font-weight: bold;
                text-align: left;
            }
            .mydatagrid a /** FOR THE PAGING ICONS  **/
             {
                background-color: Transparent;
                padding: 5px 5px 5px 5px;
                color: #fff;
                text-decoration: none;
                font-weight: bold;
            }
            /*.mygrdContent a:hover /** FOR THE PAGING ICONS  HOVER STYLES**/
            {
                background-color: #000;
                color: #fff;
            }*/
            .mygrdContent span /** FOR THE PAGING ICONS CURRENT PAGE INDICATOR **/
             {
                background-color: #c9c9c9;
                color: #000;
                padding: 5px 5px 5px 5px;
            }
            .pager
            {
                background-color: #646464;
                font-family: Arial;
                color: White;
                height: 30px;
                text-align: left;
            }
            .mygrdContent td
            {
                padding: 5px;
            }
            .mygrdContent th
            {
                padding: 5px;
            }
            .inputbtn
    {
        -moz-border-radius: 5px;
        border-radius: 5px;
        border: 1px solid gray;
        padding:0 3px 0 3px;
        display:inline-block;
        text-decoration:none;
        background:#595651;
        color:#FFFFFF;
        cursor:pointer;
        font:11px sans-serif;
    }
         .bordereffect
    {
    	color: #1abc9c;
    	background-color: Transparent;
    	padding: 10px;
    	border: solid 2px #1abc9c;
    	font-size: 20px;
    	font-weight: lighter;
    	font-family: Consolas;
    
    	-moz-border-radius: 0px 0px 0px 0px;
    	border-radius: 0px 0px 0px 0px;
               margin-top: 12px;
           }
    
        </style> 
    
    
        <title></title>
        <style type="text/css">
            .auto-style1 {
                width: 100%;
            }
            .input-lg {}
        </style>
        </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <br />
            Ref No.
             <asp:TextBox ID="txtRefno" CssClass="form-control input-lg"   Placeholder="Ref No"  runat="server" ></asp:TextBox>
            &nbsp; Order No.
                     <asp:TextBox ID="txtOrder" CssClass="form-control input-lg"  Placeholder="Order No"  runat="server" Height="16px" Width="68px"></asp:TextBox>
    
            <asp:Label ID="lbmsg" runat="server" Text="." Font-Bold="True" Font-Size="Large" ForeColor="Red"></asp:Label>
    
            &nbsp; Search By PID No :
                     <asp:TextBox ID="txtpackno" CssClass="form-control input-lg"  Placeholder="Order No" AutoPostBack="true"  runat="server" Height="16px" Width="68px" OnTextChanged="txtpackno_TextChanged"></asp:TextBox>
    
            <br/>
            <br />
    
    
    
               &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <asp:TextBox ID="txtprdno"  runat="server" AutoPostBack="true" Placeholder="Input Bale No" Height="16px" Width="100px" style="margin-left: 0px" OnTextChanged="txtprdno_TextChanged"></asp:TextBox>
            <asp:DropDownList ID="DropDownList1" runat="server" Enabled="false" Height="20px" Width="130px">
            </asp:DropDownList>
        
            <asp:TextBox ID="txtorderno" Placeholder="Order No"  readonly="true" runat="server" Height="16px" Width="65px"></asp:TextBox>
        
            <asp:TextBox ID="txtqty" Placeholder="QTY"  readonly="true" runat="server" Height="16px" Width="56px"></asp:TextBox>
        
        &nbsp;
            <asp:Button ID="bt_add" runat="server" Text="Add"  Height="26px" Width="35px" OnClick="bt_add_Click" />
        
        </div>
            <table class="auto-style1">
                <tr>
                    <td>&nbsp;</td>
                    <td>
                        <asp:GridView ID="GVPACKLISted" DataKeyNames="PDID"  runat="server" HeaderStyle-BackColor="#66ccff" HeaderStyle-ForeColor="White"
        CssClass="mygrdContent" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" RowStyle-CssClass="rows" AutoGenerateColumns="false" OnRowDeleting="GVPACKLISted_RowDeleting" OnRowEditing="GVPACKLISted_RowEditing"  >
        <Columns>
            
             <asp:TemplateField HeaderText="Bale_No">
                                    <ItemTemplate>
                                        <asp:Label ID="Prdno" runat="server" Text='<%#Bind("Prdno")%>'></asp:Label>
                                    </ItemTemplate>
                 </asp:TemplateField>
    
    
    
    
               <asp:TemplateField HeaderText="Pack_No" >
                                    <ItemTemplate>
                                        <asp:Label ID="PID" runat="server" Text='<%#Bind("PID")%>'></asp:Label>
                                    </ItemTemplate>
                 </asp:TemplateField>
    
             <asp:TemplateField HeaderText="Description" Visible="false">
                                    <ItemTemplate>
                                        <asp:Label ID="Codeitem" runat="server" Text='<%#Bind("Codeitem")%>'></asp:Label>
                                    </ItemTemplate>
                 </asp:TemplateField>
    
             <asp:TemplateField HeaderText="Description">
                                    <ItemTemplate>
                                        <asp:Label ID="Descriptionitem" runat="server" Text='<%#Bind("Descriptionitem")%>'></asp:Label>
                                    </ItemTemplate>
                 </asp:TemplateField>
    
    
    
             <asp:TemplateField HeaderText="Orderno" >
                                    <ItemTemplate>
                                        <asp:Label ID="Orderno" runat="server" Text='<%#Bind("Orderno")%>'></asp:Label>
                                    </ItemTemplate>
                 </asp:TemplateField>
    
    
    
                 <asp:TemplateField HeaderText="QTY" >
                                    <ItemTemplate>
                                        <asp:Label ID="QTY" runat="server" Text='<%#Bind("QTY")%>'></asp:Label>
                                    </ItemTemplate>
                 </asp:TemplateField>
         
            <asp:TemplateField>
                <ItemTemplate>
                    <%--<asp:LinkButton ID="lnkEdit" Text="Edit" runat="server" CommandName="Edit" />--%>
    
                </ItemTemplate>
    
                <EditItemTemplate>
                  <%--  <asp:LinkButton ID="lnkUpdate" Text="Update" runat="server" OnClick="OnUpdate" />
                    <asp:LinkButton ID="lnkCancel" Text="Cancel" runat="server" OnClick="OnCancel" />--%>
                </EditItemTemplate>
    
            </asp:TemplateField>
            <%--<asp:CommandField ShowDeleteButton="True" ButtonType="Button" />--%>
             <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="20"/>
        </Columns>
    
    <HeaderStyle BackColor="#66CCFF" ForeColor="White"></HeaderStyle>
                            <SelectedRowStyle BackColor="Yellow" />
    </asp:GridView>
                    </td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                <tr>
                    <td>&nbsp;</td>
                    <td>
                        <asp:Button ID="Save" runat="server" Height="25px" Text="Save" Width="73px" BackColor="#66FFFF" CssClass="inputbtn" Font-Names="Aharoni" ForeColor="#000066" OnClick="Save_Click" />
                        </td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                <tr>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                <tr>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
            </table>
        </form>
    </body>
    </html>

    here is my c# code

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace WebApplication1
    {
        public partial class PackListED : System.Web.UI.Page
        {
            SqlConnection con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SPS;MultipleActiveResultSets=True;");
            DataTable dt = new DataTable();
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    loadID();
                    itemload();
                    dt.Columns.Add("Prdno");
                    dt.Columns.Add("Descriptionitem");
                    dt.Columns.Add("Codeitem");
                    dt.Columns.Add("PID");
                    dt.Columns.Add("orderno");
                    dt.Columns.Add("QTY");
                    ViewState["dt"] = dt;
                    itemload();
                }
    
            }
    
            private void itemload()
            {
                con.Open();
                SqlDataAdapter adpbp = new SqlDataAdapter("select * from ItemMasterFile ", con);
                DataSet dsbp = new DataSet();
                adpbp.Fill(dsbp);
                DropDownList1.DataSource = dsbp.Tables[0];
                DropDownList1.DataTextField = "Descriptionitem";
                DropDownList1.DataValueField = "Codeitem";
                DropDownList1.DataBind();
                DropDownList1.Items.Insert(0, new ListItem("Select Item Name", ""));
    
                con.Close();
            }
    
            private void loadID()
            {
                if (Request.QueryString["PID"] != null)
                    txtpackno.Text = Request.QueryString["PID"];
                BindGrid(txtpackno.Text.Trim());
    
            }
    
            private void BindGrid(string PID)
            {
                String strQuery = "select Orderno,Order_Ref_No From tbl_BalPacM where" +
                                               " PID = @PID";
                // SqlConnection con = new SqlConnection();
                SqlCommand cmde = new SqlCommand();
                cmde.Parameters.AddWithValue("@PID", PID);
                cmde.CommandType = CommandType.Text;
                cmde.CommandText = strQuery;
                cmde.Connection = con;
                try
                {
                    con.Open();
                    SqlDataReader sdr = cmde.ExecuteReader();
                    while (sdr.Read())
                    {
    
    
                        txtOrder.Text = sdr["Orderno"].ToString();
                        txtRefno.Text = sdr["Order_Ref_No"].ToString();
    
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                SqlCommand cmdbno = new SqlCommand("[Sp_PackDetailGV_CRUD]", con);
                cmdbno.Parameters.AddWithValue("@Action", "SELECT");
                cmdbno.CommandType = CommandType.StoredProcedure;
                cmdbno.Parameters.AddWithValue("@PID", PID);
                //  SqlDataAdapter adptbno = new SqlDataAdapter(cmdbno);
    
                using (SqlDataAdapter da = new SqlDataAdapter(cmdbno))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        this.GVPACKLISted.DataSource = dt;
                        this.GVPACKLISted.DataBind();
                        ViewState["CurrentTable"] = dt;
                    }
                    //DataTable dtbno = new DataTable();
                    //adptbno.Fill(dtbno);
                    //GVPACKLISted.DataSource = dtbno;
                    //GVPACKLISted.DataBind();
                    cmdbno.Dispose();
                    con.Close();
                }
            }
    
            protected void txtpackno_TextChanged(object sender, EventArgs e)
            {
                BindGrid(txtpackno.Text.Trim());
            }
    
    
    
            protected void GVPACKLISted_RowDeleting(object sender, GridViewDeleteEventArgs e)
            {
                int PDID = Convert.ToInt32(GVPACKLISted.DataKeys[e.RowIndex].Values[0]);
    
    
    
                using (SqlCommand cmd = new SqlCommand("Sp_PackDetailGV_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "DELETE");
                    cmd.Parameters.AddWithValue("@PDID", PDID);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    BindGrid(txtpackno.Text.Trim());
                }
            }
    
            protected void bt_add_Click(object sender, EventArgs e)
            {
                if
                    (txtOrder.Text == "")
                {
                    Response.Write("<script language='javascript'>alert('Please Input Orderno');</script>");
                }
                else
                {
                    if
                         (txtorderno.Text == "")
                    {
                        Response.Write("<script language='javascript'>alert('Please Input Bale No');</script>");
                    }
                    else if
                    (txtqty.Text == "")
                    {
                        Response.Write("<script language='javascript'>alert('Please Input QTY');</script>");
                    }
                    else if
    
                        ((int.Parse(txtOrder.Text) != (int.Parse(txtorderno.Text))))
                        lbmsg.Text = "Bale is Not in order";
                    else if
                            (txtorderno.Text == "")
                    {
                        Response.Write("<script language='javascript'>alert('Please Input Orderno');</script>");
                    }
    
                    else if (DropDownList1.SelectedValue == "")
                    {
                        Response.Write("<script language='javascript'>alert('Please Input Item');</script>");
                    }
                    else
                    {
                        int prdno = int.Parse(txtprdno.Text);
                        int codeitem = Convert.ToInt32(DropDownList1.SelectedItem.Value.ToString());
                        int orderno = int.Parse(txtorderno.Text);
                        int qty = int.Parse(txtqty.Text);
                        int PID = int.Parse(txtpackno.Text);
    
    
                        using (SqlCommand cmd = new SqlCommand("Sp_PackDetailGV_CRUD"))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@Action", "INSERT");
                            cmd.Parameters.AddWithValue("@PID", PID);
                            cmd.Parameters.AddWithValue("@CodeItem", codeitem);
                            cmd.Parameters.AddWithValue("@QTY", qty);
                            cmd.Parameters.AddWithValue("@Orderno", orderno);
                            cmd.Parameters.AddWithValue("@Prdno", prdno);
                            cmd.Connection = con;
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                            BindGrid(txtpackno.Text.Trim());
                            Clear();
                        }
                    }
                }
            }
            private void Clear()
            {
                txtprdno.Text = "";
                txtqty.Text = "";
                txtorderno.Text = "";
                DropDownList1.SelectedValue = "";
            }
    
            protected void txtprdno_TextChanged(object sender, EventArgs e)
            {
                loadbaleno();
            }
    
            private void loadbaleno()
            {
                String strQuery = "select Codeitem,Orderno,Prdqty From Probale where" +
                                                " Prdno = @Prdno";
                // SqlConnection con = new SqlConnection();
                SqlCommand cmde = new SqlCommand();
                cmde.Parameters.AddWithValue("@Prdno", txtprdno.Text);
                cmde.CommandType = CommandType.Text;
                cmde.CommandText = strQuery;
                cmde.Connection = con;
                try
                {
                    con.Open();
                    SqlDataReader sdr = cmde.ExecuteReader();
                    while (sdr.Read())
                    {
                        //DropDownList1.SelectedValue = sdr[0].ToString();
                        DropDownList1.SelectedValue = sdr["Codeitem"].ToString();
    
                        txtorderno.Text = sdr["Orderno"].ToString();
                        txtqty.Text = sdr["Prdqty"].ToString();
                        lbmsg.Text = "";
    
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            protected void GVPACKLISted_RowEditing(object sender, GridViewEditEventArgs e)
            {
                GVPACKLISted.EditIndex = e.NewEditIndex;
                BindGrid(txtpackno.Text.Trim());
            }
            public object PID { get; set; }
            protected void Save_Click(object sender, EventArgs e)
            {
                if
                    (txtRefno.Text == "")
                {
                    Response.Write("<script language='javascript'>alert('Please Input Orderno Ref.');</script>");
                }
                else
                {
    
                    SqlCommand cmd = new SqlCommand("[Sp_BalPakinsert]", con);
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        cmd.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
    
                        cmd.Parameters.AddWithValue("@OrderNo", txtOrder.Text);
    
                        con.Open();
                        PID = Convert.ToInt32(cmd.ExecuteScalar());
                        DataTable dt = (DataTable)ViewState["dt"];
                        int pid, codeitem, qty, orderno, prdno;
    
                        foreach (DataRow row in dt.Rows)
                        {
                            cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con);
    
    
                            cmd.CommandType = CommandType.StoredProcedure;
    
                            pid = int.Parse(row["PID"].ToString());
                            prdno = int.Parse(row["Prdno"].ToString());
                            orderno = int.Parse(row["Orderno"].ToString());
                            qty = int.Parse(row["QTY"].ToString());
                            codeitem = int.Parse(row["Codeitem"].ToString());
    
                            //this.InsertRows(codeitem, qty, orderno, prdno);
                            //            }
                            //        }
                            //    }
                            //}
    
                            //   private void InsertRows(int codeitem, int qty, int orderno, int prdno)
                            //  {
                            //     con.Open();
    
    
                            // cmd.Parameters.AddWithValue("@PDID", _PID);
                            cmd.Parameters.AddWithValue("@PID", PID);
                            cmd.Parameters.AddWithValue("@CodeItem", codeitem);
                            cmd.Parameters.AddWithValue("@QTY", qty);
                            cmd.Parameters.AddWithValue("@Orderno", orderno);
                            cmd.Parameters.AddWithValue("@Prdno", prdno);
    
                            cmd.ExecuteNonQuery();
    
                            con.Close();
                        }
                    }
    
    
    
                }
    
            }
        }
    }
        
    

    Store Procedure

    Create PROCEDURE [dbo].[Sp_BalPakinsert]
    
    
    
    
     @OrderNo int,
    @Order_Ref_No varchar(50)
    
    as 
     BEGIN
        INSERT INTO tbl_BalPacM (OrderNo,Order_Ref_No) VALUES (@OrderNo,@Order_Ref_No)
       SELECT SCOPE_IDENTITY()
    
        end
    
    
    
    
    Create PROCEDURE [dbo].[SP_BalPakDetail_Insert]
    
    
    --@PDID int,
    @Orderno int,
    @Prdno int,
    @PID int Output,
    @Codeitem int,
    @QTY int
    as begin
    
    --Insert
    
       INSERT  INTO tbl_PckDetail (PID,Codeitem,QTY,Orderno,Prdno) Values(@PID,@Codeitem,@QTY,@Orderno,@Prdno)
      --  set @PID=SCOPE_IDENTITY()
      --  --Update
      --  UPDATE tbl_PckDetail
    		--SET
    		--	OrderNO=@OrderNO,
    		--    Prdno=@Prdno,
    		--	CodeItem=@CodeItem,
    		--	qty=@QTY,
    		-- 	PID=@PID
    		--WHERE PDID = @PDID
        
        end

    Now i want to insert PID (2)

    Friday, August 16, 2019 9:01 AM
  • User665608656 posted

    Hi akhterr,

    According to your code , I wonder what your question is. Can you elaborate on it?

    Are there any errors or abnormal reminders on the page? If so, please provide it to us.

    I want to run your code, but you didn't provide me with the stored procedure "Sp_PackDetailGV_CRUD".

    If you can't save the second table data, you need to make sure that you can enter the foreach loop.

    Best Regards,

    YongQing.

    Monday, August 19, 2019 9:39 AM
  • User-367318540 posted
    USE [SPS]
    GO
    /****** Object:  StoredProcedure [dbo].[Sp_PackDetailGV_CRUD]    Script Date: 08/19/2019 15:48:34 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Sp_PackDetailGV_CRUD]
          @Action VARCHAR(10)
          ,@PID INT = NULL
          ,@PDID INT = NULL
          ,@Prdno INT = NULL
          ,@Codeitem INT = NULL
          ,@OrderNO INT = NULL
          ,@QTY INT = Null
          
    AS
    BEGIN
          SET NOCOUNT ON;
     
          --SELECT
        IF @Action = 'SELECT'
          BEGIN
                select tbl_PckDetail.PDID,tbl_PckDetail.Prdno as Prdno,itemMasterFile.Codeitem as Codeitem ,tbl_PckDetail.PID as PID,ItemMasterFile.Descriptionitem ,tbl_PckDetail.qty,tbl_PckDetail.Orderno from tbl_PckDetail
    
    inner join tbl_BalPacM on tbl_BalPacM.PID=tbl_PckDetail.PID
    inner join ItemMasterFile on tbl_PckDetail.Codeitem=itemMasterFile.Codeitem
    
    where tbl_PckDetail.PID=@PID
          END
     
          --INSERT
        IF @Action = 'INSERT'
          BEGIN
             INSERT  INTO tbl_PckDetail (PID,Codeitem,QTY,Orderno,Prdno) Values(@PID,@Codeitem,@QTY,@Orderno,@Prdno)
          END
     
          --UPDATE
        IF @Action = 'UPDATE'
          BEGIN
                UPDATE tbl_PckDetail
                SET PID = @PID, Codeitem = @Codeitem,QTY=@QTY,Orderno=@Orderno,Prdno=@Prdno
                WHERE PDID = @PDID
          END
     
          --DELETE
        IF @Action = 'DELETE'
          BEGIN
                DELETE FROM tbl_PckDetail
                WHERE PDID = @PDID
          END
    END

    Monday, August 19, 2019 10:48 AM
  • User665608656 posted

    Hi akhterr,

    I found that you didn't save data in ViewState["dt"], so after you save the first table ,then you cannot enter the foreach loop to save the second table.

    And i found you save the viewstate in the BindGrid method which called ViewState["CurrentTable"], the name is different from the ViewState["dt"].

    I don't know the relationship between your ViewState["dt"] and ViewState["CurrentTable"], but if you are foreach cycling ViewState["dt"], your ViewState["dt"] does not have any rows of data, you need to bind the ViewState["dt"] data when you click ‘Add’ button and then store it cyclically.

     DataTable dt = (DataTable)ViewState["dt"];
                        int pid, codeitem, qty, orderno, prdno;
    
                        foreach (DataRow row in dt.Rows)
                        {
                            cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con);
    
    
                            cmd.CommandType = CommandType.StoredProcedure;
    
                            pid = int.Parse(row["PID"].ToString());
                            prdno = int.Parse(row["Prdno"].ToString());
                            orderno = int.Parse(row["Orderno"].ToString());
                            qty = int.Parse(row["QTY"].ToString());
                            codeitem = int.Parse(row["Codeitem"].ToString());
    
                            //this.InsertRows(codeitem, qty, orderno, prdno);
                            //            }
                            //        }
                            //    }
                            //}
    
                            //   private void InsertRows(int codeitem, int qty, int orderno, int prdno)
                            //  {
                            //     con.Open();
    
    
                            // cmd.Parameters.AddWithValue("@PDID", _PID);
                            cmd.Parameters.AddWithValue("@PID", PID);
                            cmd.Parameters.AddWithValue("@CodeItem", codeitem);
                            cmd.Parameters.AddWithValue("@QTY", qty);
                            cmd.Parameters.AddWithValue("@Orderno", orderno);
                            cmd.Parameters.AddWithValue("@Prdno", prdno);
    
                            cmd.ExecuteNonQuery();
    
                            con.Close();
                        }

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 20, 2019 2:18 AM
  • User-367318540 posted

    Thank ALot Yongqing Yu........innocent

    Tuesday, August 20, 2019 5:20 AM