Answered by:
How to insert Master Table ID into Child Detail table using asp.net webform?

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> 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 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 tableThe 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 tableThe 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