Insert Data into Parent and Child Tables from GridView using C# in ASP.Net

Last Reply one month ago By pandeyism

Posted one month ago

How to Inserting Data into Table from gridview after retrieving from database

I am retrieving data from database into gridview on Master Detail Form

Master Detail data is inserting into table, but Child data from gridview with multiple row are not getting inserting into table.

Here is my inserting query into table

protected void Save_Click(object sender, EventArgs e)
{
    if (txtRefno.Text == "")
    {
        Response.Write("<script language='javascript'>alert('Please Input Orderno Ref.');</script>");
    }
    else
    {
        using (SqlCommand cmd = new SqlCommand("[Sp_BalPakinsert]", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
            cmd.Parameters.AddWithValue("@OrderNo", txtOrder.Text);
            con.Open();
            PID = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            DataTable dt = (DataTable)ViewState["dt"];
            int codeitem, qty, orderno, prdno;
            foreach (DataRow row in dt.Rows)
            {
                //pid = int.Parse(row["_PID"].ToString());
                prdno = int.Parse(row["Prdno"].ToString());
                orderno = int.Parse(row["Orderno"].ToString());
                qty = int.Parse(row["QTY"].ToString());
                codeitem = int.Parse(row["Codeitem"].ToString());
                this.InsertRows(codeitem, qty, orderno, prdno);
            }
        }
    }
}

private void InsertRows(int codeitem, int qty, int orderno, int prdno)
{
    con.Open();
    using (SqlCommand cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        // cmd.Parameters.AddWithValue("@PDID", _PID);
        cmd.Parameters.AddWithValue("@PID", PID);
        cmd.Parameters.AddWithValue("@CodeItem", codeitem);
        cmd.Parameters.AddWithValue("@QTY", qty);
        cmd.Parameters.AddWithValue("@Orderno", orderno);
        cmd.Parameters.AddWithValue("@Prdno", prdno);
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

public object PID { get; set; }    

Table structure 

 Master Table

CREATE TABLE [dbo].[tbl_BalPacM](
	[PID] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [int] NULL,
	[Order_Ref_No] [varchar](50) NULL,
	[Date] [varchar](12) NULL,
 CONSTRAINT [PK_tbl_BalPacM] PRIMARY KEY CLUSTERED 
(
	[PID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Child Table

CREATE TABLE [dbo].[tbl_PckDetail](
	[PDID] [int] IDENTITY(1,1) NOT NULL,
	[Prdno] [int] NULL,
	[Codeitem] [int] NULL,
	[OrderNo] [int] NULL,
	[QTY] [int] NULL,
	[PID] [int] NULL,
 CONSTRAINT [PK_tbl_PckDetail] PRIMARY KEY CLUSTERED 
(
	[PDID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
You are viewing reply posted by: pandeyism one month ago.
Posted one month ago

Hi akhter,

Refer below code.

Namespaces

using System.Data.SqlClient;
using System.Data;

Code

public object PID { get; set; }
protected void Save_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection();
    if (txtRefno.Text == "")
    {
        Response.Write("<script language='javascript'>alert('Please Input Orderno Ref.');</script>");
    }
    else
    {
        SqlCommand cmd = new SqlCommand("[Sp_BalPakinsert]", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
        cmd.Parameters.AddWithValue("@OrderNo", txtOrder.Text);
        con.Open();
        PID = Convert.ToInt32(cmd.ExecuteScalar());
        DataTable dt = (DataTable)ViewState["dt"];
        int pid, codeitem, qty, orderno, prdno;
        foreach (DataRow row in dt.Rows)
        {
            cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con);
            cmd.CommandType = CommandType.StoredProcedure;
            pid = int.Parse(row["_PID"].ToString());
            prdno = int.Parse(row["Prdno"].ToString());
            orderno = int.Parse(row["Orderno"].ToString());
            qty = int.Parse(row["QTY"].ToString());
            codeitem = int.Parse(row["Codeitem"].ToString());

            cmd.Parameters.AddWithValue("@pid", pid);
            cmd.Parameters.AddWithValue("@CodeItem", codeitem);
            cmd.Parameters.AddWithValue("@QTY", qty);
            cmd.Parameters.AddWithValue("@Orderno", orderno);
            cmd.Parameters.AddWithValue("@Prdno", prdno);
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}