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
