5
Answers

How to insert Master Table ID into Child Detail table ?

i am trying to insert master table id into child table but getting error that is (
here is my html
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MasterDetails.aspx.cs" Inherits="WebApplication1.MasterDetails" %>  
  2. <!DOCTYPE html>  
  3. <html xmlns="http://www.w3.org/1999/xhtml">  
  4. <head runat="server">  
  5. <title>Master Detail Form</title>  
  6. </head>  
  7. <body>  
  8. <form id="form1" runat="server">  
  9. <div>  
  10. Master Detail  
  11. <br />  
  12. <br />  
  13. Customer Name : <asp:TextBox runat="server" ID="txtName" Height="16px" Width="124px" />  
  14. <br />  
  15. <br />  
  16. Child Detail  
  17. <br />  
  18. <br />  
  19. CodeItems : <asp:DropDownList ID="DropDownList1" runat="server">  
  20. </asp:DropDownList>  
  21. Qty :  
  22. <asp:TextBox runat="server" ID="txtqty" Height="16px" Width="53px" />  
  23. <asp:Button Text="Insert" runat="server" OnClick="GVadd_Click" />  
  24. <br />  
  25. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">  
  26. <Columns>  
  27. <asp:BoundField DataField="Codeitem" HeaderText="Codeitem" Visible="False"/>  
  28. <asp:BoundField DataField="Descriptionitem" HeaderText="Descriptionitem" />  
  29. <asp:BoundField DataField="QTY" HeaderText="QTY" />  
  30. </Columns>  
  31. </asp:GridView>  
  32. <asp:Button ID="Save" runat="server" Text="SaveDB" OnClick="Save_Click" />  
  33. <br />  
  34. </div>  
  35. </form>  
  36. </body>  
  37. </html>  
and c# code..
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Configuration;  
  8. using System.Data;  
  9. using System.Data.SqlClient;  
  10. namespace WebApplication1  
  11. {  
  12. public partial class MasterDetails : System.Web.UI.Page  
  13. {  
  14. int IPID = 0;  
  15. SqlConnection con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SPS;MultipleActiveResultSets=True;");  
  16. DataTable dt = new DataTable();  
  17. DataRow dr;  
  18. protected void Page_Load(object sender, EventArgs e)  
  19. {  
  20. if (!this.IsPostBack)  
  21. {  
  22. dt.Columns.Add("Descriptionitem");  
  23. dt.Columns.Add("Codeitem");  
  24. dt.Columns.Add("QTY");  
  25. ViewState["dt"] = dt;  
  26. itemload();  
  27. }  
  28. }  
  29. private void itemload()  
  30. {  
  31. con.Open();  
  32. SqlDataAdapter adpr1 = new SqlDataAdapter("select * from ItemMasterFile ", con);  
  33. DataSet dspr1 = new DataSet();  
  34. adpr1.Fill(dspr1);  
  35. DropDownList1.DataSource = dspr1.Tables[0];  
  36. DropDownList1.DataTextField = "Descriptionitem";  
  37. DropDownList1.DataValueField = "Codeitem";  
  38. DropDownList1.DataBind();  
  39. }  
  40. //Save Data Into Gridview//  
  41. protected void GVadd_Click(object sender, EventArgs e)  
  42. {  
  43. dt = ViewState["dt"] as DataTable;  
  44. dr = dt.NewRow();  
  45. // dr["Codeitem"] = DropDownList1.SelectedValue;  
  46. dr["Descriptionitem"] = DropDownList1.SelectedItem.Text.Trim();  
  47. dr["Codeitem"] = DropDownList1.SelectedItem.Value;  
  48. dr["QTY"] = txtqty.Text;  
  49. dt.Rows.Add(dr);  
  50. GridView1.DataSource = dt;  
  51. GridView1.DataBind();  
  52. clear();  
  53. }  
  54. private void clear()  
  55. {  
  56. // Codeitem.Text = "";  
  57. txtqty.Text = "";  
  58. }  
  59. //Save Data into Database//  
  60. protected void Save_Click(object sender, EventArgs e)  
  61. {  
  62. int _PID = 0;  
  63. using (SqlCommand cmd = new SqlCommand("Packinsert", con))  
  64. {  
  65. cmd.CommandType = CommandType.StoredProcedure;  
  66. cmd.Parameters.AddWithValue("@PID", IPID);  
  67. cmd.Parameters.AddWithValue("@PName", txtName.Text);  
  68. con.Open();  
  69. _PID = Convert.ToInt32(cmd.ExecuteScalar());  
  70. DataTable dt = (DataTable)ViewState["dt"];  
  71. int codeitem, qty;  
  72. foreach (DataRow row in dt.Rows)  
  73. {  
  74. qty = int.Parse(row["QTY"].ToString());  
  75. codeitem = int.Parse(row["Codeitem"].ToString());  
  76. this.InsertRows(codeitem, qty);  
  77. }  
  78. }  
  79. }  
  80. private void InsertRows(int codeitem, int qty)  
  81. {  
  82. using (SqlCommand cmd = new SqlCommand("Insert_PackDetail", con))  
  83. {  
  84. con.Open();  
  85. cmd.CommandType = CommandType.StoredProcedure;  
  86. cmd.Parameters.AddWithValue("@PID", _PID );  
  87. cmd.Parameters.AddWithValue("@CodeItem", codeitem);  
  88. cmd.Parameters.AddWithValue("@QTY", qty);  
  89. cmd.ExecuteNonQuery();  
  90. con.Close();  
  91. }  
  92. }  
  93. public object _PID { get; set; }  
  94. }  
  95. }  
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
  1. Insert into Pack table  
  2. ALTER PROCEDURE [dbo].[Packinsert]  
  3. @PName varchar(50)  
  4. as  
  5. BEGIN  
  6. INSERT INTO Pack (PName) VALUES (@PName)  
  7. SELECT SCOPE_IDENTITY()  
  8. end  
Child table insert
  1. Create PROCEDURE [dbo].[Insert_PackDetail]  
  2. @PID int ,  
  3. @Codeitem int,  
  4. @QTY int  
  5. as begin  
  6. INSERT INTO PackDetails (PID,Codeitem,QTY) Values(@PID,@Codeitem,@QTY)  
  7. end  
  8. 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

Answers (5)