How to insert Master Table ID into Child Detail table ?

Akhter HUssain

7y1.1kLearn iOS Programming

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" %>
>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Master Detail Formtitle>
head>
<body>
<form id="form1" runat="server">
<div>
Master Detail
<br />
<br />
Customer Name : <asp:TextBox runat="server" ID="txtName" Height="16px" Width="124px" />
<br />
<br />
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 i have no idea that how Master Table (Pack) primary ID ,will get insert into child table(PackDetail) after insert

here is my store procedure

Insert into Pack 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
PID is primary key of Pack table and will use as a FK in PackDetail table

Please help me out.....

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
Check below code it is working fine
private DataTable dt = new DataTable();
       private 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", _PID);
               cmd.Parameters.AddWithValue("@PName", txtName.Text);

               con.Open();
               _PID = Convert.ToInt32(cmd.ExecuteScalar());
               con.Close();

               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 int _PID { get; set; }
Akhter HUssain7y ago
Hi Priyanka Jain..

PID in child table is inserting 0 ..please have a look
private void InsertRows(int codeitem, int qty)
       {
           using (SqlCommand cmd = new SqlCommand("Insert_PackDetail", con))
           {

               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.AddWithValue("@PID", _PID );
               cmd.Parameters.AddWithValue("@CodeItem", codeitem);
               cmd.Parameters.AddWithValue("@QTY", qty);
               con.Open();
               cmd.ExecuteNonQuery();
               con.Close();
           }
       }

       public int _PID { get; set; }
   }
Priyanka Jain7y ago
Your PID will be same for all child table record so define your PID as int property in last
public int _PID { get; set; }
So once you insert it will store in _PID property from there it will automatically insert in your InsertRows method
Akhter HUssain7y ago
Hi Priyanka Jain,

How PID will get insert into packdetail table with each row in gridview
Priyanka Jain7y ago
Your code require some changes ,

First of all make sure that your,
In Database, PID should be Primary Key and it should be set as IsIdentity property to true

and in your code should be,
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", _PID);
              cmd.Parameters.AddWithValue("@PName", txtName.Text);

              con.Open();
              _PID = Convert.ToInt32(cmd.ExecuteScalar());
              con.Close();

              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);
              }
          }
      }
// Define PID as int
public int _PID { get; set; }