locked
How to insert Master Table ID into Child Detail table using asp.net webform? RRS feed

  • Question

  • User-367318540 posted

    i am trying to insert master table id into child table but getting error that is (

    here is my html 

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MasterDetails.aspx.cs" Inherits="WebApplication1.MasterDetails" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Master Detail Form</title>
        </head>
    <body>
      <form id="form1" runat="server">
        <div>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            Master Detail
            <br />
            <br />
            Customer Name : <asp:TextBox runat="server" ID="txtName" Height="16px" Width="124px" />
    
            <br />
            <br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    
            Child Detail
            <br />
            <br />
            CodeItems : <asp:DropDownList ID="DropDownList1" runat="server">
            </asp:DropDownList>
            Qty :
    <asp:TextBox runat="server" ID="txtqty" Height="16px" Width="53px" />
            
    
            <asp:Button Text="Insert" runat="server" OnClick="GVadd_Click" />
    
        
    <br />
    
    
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
        <Columns>
              <asp:BoundField DataField="Codeitem" HeaderText="Codeitem" Visible="False"/>
            <asp:BoundField DataField="Descriptionitem" HeaderText="Descriptionitem" />
    
            <asp:BoundField DataField="QTY" HeaderText="QTY" />
        </Columns>
    </asp:GridView>
            <asp:Button ID="Save" runat="server" Text="SaveDB" OnClick="Save_Click" />
            
    <br />
    
        </div>
        </form>
    </body>
    </html>
    

    and 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 MasterDetails : System.Web.UI.Page
        {
            int IPID = 0;
            SqlConnection con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SPS;MultipleActiveResultSets=True;");
    
            DataTable dt = new DataTable();
            DataRow dr;
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    dt.Columns.Add("Descriptionitem");
                    dt.Columns.Add("Codeitem");
    
                    dt.Columns.Add("QTY");
                    ViewState["dt"] = dt;
                    itemload();
                }
            }
    
            private void itemload()
            {
                con.Open();
                SqlDataAdapter adpr1 = new SqlDataAdapter("select * from ItemMasterFile ", con);
                DataSet dspr1 = new DataSet();
                adpr1.Fill(dspr1);
                DropDownList1.DataSource = dspr1.Tables[0];
                DropDownList1.DataTextField = "Descriptionitem";
                DropDownList1.DataValueField = "Codeitem";
                DropDownList1.DataBind();
            }
    
    
            //Save Data Into Gridview//
    
            protected void GVadd_Click(object sender, EventArgs e)
            {
                dt = ViewState["dt"] as DataTable;
                dr = dt.NewRow();
                //  dr["Codeitem"] = DropDownList1.SelectedValue;
                dr["Descriptionitem"] = DropDownList1.SelectedItem.Text.Trim();
                dr["Codeitem"] = DropDownList1.SelectedItem.Value;
                dr["QTY"] = txtqty.Text;
                dt.Rows.Add(dr);
                GridView1.DataSource = dt;
                GridView1.DataBind();
                clear();
            }
    
            private void clear()
            {
                // Codeitem.Text = "";
                txtqty.Text = "";
            }
    
    
            //Save Data into Database//
    
            protected void Save_Click(object sender, EventArgs e)
            {
    
    
                int _PID = 0;
                using (SqlCommand cmd = new SqlCommand("Packinsert", con))
                {
    
    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PID", IPID);
                    cmd.Parameters.AddWithValue("@PName", txtName.Text);
    
                    con.Open();
                    _PID = Convert.ToInt32(cmd.ExecuteScalar());
    
    
                    DataTable dt = (DataTable)ViewState["dt"];
                    int codeitem, qty;
    
                    foreach (DataRow row in dt.Rows)
                    {
    
    
                        qty = int.Parse(row["QTY"].ToString());
                        codeitem = int.Parse(row["Codeitem"].ToString());
                 
                        this.InsertRows(codeitem, qty);
                    }
                }
            }        
            private void InsertRows(int codeitem, int qty)
            {
                using (SqlCommand cmd = new SqlCommand("Insert_PackDetail", con))
                {
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PID", _PID );
                    cmd.Parameters.AddWithValue("@CodeItem", codeitem);
                    cmd.Parameters.AddWithValue("@QTY", qty);
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
    
            public object _PID { get; set; }
        }
    
    
    }
    
    
    
    

    here is my store procedure 

    Master table insert 

    PID is primary key of Pack table and will use as a FK in PackDetail table  

    ALTER PROCEDURE [dbo].[Packinsert]
    
    
    
    
     @PName varchar(50)
    
    
    as 
     BEGIN
        INSERT INTO Pack (PName) VALUES (@PName)
       SELECT SCOPE_IDENTITY()
    
        end

    Child table insert

    Create PROCEDURE [dbo].[Insert_PackDetail]
    
    
    
    
    @PID int ,
    @Codeitem int,
    @QTY int
    as begin
    
       INSERT  INTO PackDetails (PID,Codeitem,QTY) Values(@PID,@Codeitem,@QTY)
        end

    Please help me out.....

    PID will get insert into child table at the time of insert

    Wednesday, August 7, 2019 11:35 AM

Answers

  • User288213138 posted

    Hi akhterr,

    akhterr

    etting error that is (

    What's error with you code?

    akhterr

    using (SqlCommand cmd = new SqlCommand("Packinsert", con))
                {
    
    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PID", IPID);
                    cmd.Parameters.AddWithValue("@PName", txtName.Text);
    
                    con.Open();
                    _PID = Convert.ToInt32(cmd.ExecuteScalar());

    You add the PID to your stored procedure, but the stored procedure you give does not have a PID.

    And the cmd.executescalar () method returns the number of affected rows.What's the point of doing this?

    akhterr

    PID is primary key of Pack table and will use as a FK in PackDetail table  

    The foreign key of the Detail table is the primary key value of the Main table, not the primary key.

    And do you want to Inserts the value of the primary key into the detail table?

    If so, you can try to use LAST_INSERT_ID() function. 

    More details about how to use  LAST_INSERT_ID(), you could refer to this link:https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

    If I misunderstand your requirement, please post more details information about your requirement.

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 8, 2019 8:54 AM

All replies

  • User288213138 posted

    Hi akhterr,

    akhterr

    etting error that is (

    What's error with you code?

    akhterr

    using (SqlCommand cmd = new SqlCommand("Packinsert", con))
                {
    
    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PID", IPID);
                    cmd.Parameters.AddWithValue("@PName", txtName.Text);
    
                    con.Open();
                    _PID = Convert.ToInt32(cmd.ExecuteScalar());

    You add the PID to your stored procedure, but the stored procedure you give does not have a PID.

    And the cmd.executescalar () method returns the number of affected rows.What's the point of doing this?

    akhterr

    PID is primary key of Pack table and will use as a FK in PackDetail table  

    The foreign key of the Detail table is the primary key value of the Main table, not the primary key.

    And do you want to Inserts the value of the primary key into the detail table?

    If so, you can try to use LAST_INSERT_ID() function. 

    More details about how to use  LAST_INSERT_ID(), you could refer to this link:https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

    If I misunderstand your requirement, please post more details information about your requirement.

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 8, 2019 8:54 AM
  • User-367318540 posted

    thanks

    Thursday, August 8, 2019 10:37 AM