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>
- 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();
- }
-
- protected void GVadd_Click(object sender, EventArgs e)
- {
- dt = ViewState["dt"] as DataTable;
- dr = dt.NewRow();
-
- 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()
- {
-
- txtqty.Text = "";
- }
-
- 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