Insert (save) data in Master and detail Table using C# and VB.Net in ASP.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

How to insert data into two table MasterDetail using web Form

I have data which is given below....

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))

INSERT INTO #ItemMasterFile VALUES(1,'A')
INSERT INTO #ItemMasterFile VALUES(2,'B')
INSERT INTO #ItemMasterFile VALUES(3,'C')

CREATE TABLE #Pack (PID INT,PName VARCHAR(50))
INSERT INTO #Pack VALUES(1,'INDIA')

CREATE TABLE #PackDetails (PDID INT,PID int,CodeItem int,QTY int)


INSERT INTO #PackDetails VALUES(1,1,1,1)
INSERT INTO #PackDetails VALUES(2,1,2,1)
INSERT INTO #PackDetails VALUES(3,1,3,1)


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">
        Master Detail Form
        <table class="auto-style1">
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td class="auto-style6">&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style4">Pack No<asp:TextBox ID="TextBox1" ReadOnly="true" runat="server"></asp:TextBox></td>
                <td class="auto-style5">Pack Name:<asp:TextBox ID="txtpaclname" runat="server"></asp:TextBox>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td class="auto-style6">&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td class="auto-style6">
                   ItemName<asp:DropDownList ID="ItemCode" runat="server">
                    </asp:DropDownList>
                    QTY : <asp:TextBox ID="txtpaclname0" runat="server" Height="16px" Width="43px"></asp:TextBox>
                    <asp:Button ID="GVadd" runat="server" style="text-align: right" Text="AddItemGV" Height="26px" Width="77px" />
                </td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td class="auto-style6">
                    <asp:GridView ID="GridView1" runat="server" Width="235px">
                    </asp:GridView>
                </td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td class="auto-style6">
                    <asp:Button ID="Submit" runat="server" style="text-align: justify" Text="Save" />
                </td>
                <td>&nbsp;</td>
            </tr>
        </table>
    </form>
</body>
</html>

I have two table Pack and PackDetails table.

I want when Save button is click then data insert into table Master Data will insert into Pack table and Detail data will get insert into PackDetail table.

 

Posted 4 months ago Modified on 4 months ago

Hi akhter,

Refer below sample.

SQL

CREATE TABLE ItemMasterFile(CodeItem INT IDENTITY,Descriptionitem VARCHAR(50))
CREATE TABLE Pack (PID INT IDENTITY,PName VARCHAR(50)) 
CREATE TABLE #PackDetails (PDID INT IDENTITY,PID INT,CodeItem INT,QTY INT)

 HTML

Descriptionitem:
<asp:TextBox runat="server" ID="txtDescriptionitem" />
<br />
PName :
<asp:TextBox ID="TextBox1" runat="server" ID="txtPname" />
<br />
QTY :
<asp:TextBox ID="txtQty" runat="server"></asp:TextBox>

Namespaces

C#

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

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void Save(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = null;
    cmd = new SqlCommand("INSERT INTO ItemMasterFile(Descriptionitem) VALUES(@Descriptionitem)", con);
    cmd.Parameters.AddWithValue("@Descriptionitem", txtDescriptionitem.Text);
    con.Open();
    cmd.ExecuteNonQuery();

    cmd = new SqlCommand("INSERT INTO Pack(PName) VALUES(@PName)", con);
    cmd.Parameters.AddWithValue("@PName", txtPname.Text);
    cmd.ExecuteNonQuery();

    cmd = new SqlCommand("SELECT CodeItem FROM  ItemMasterFile WHERE Descriptionitem = @Descriptionitem", con);
    cmd.Parameters.AddWithValue("@Descriptionitem", txtDescriptionitem.Text);
    int codeItem = Convert.ToInt16(cmd.ExecuteScalar());

    cmd = new SqlCommand("SELECT PID FROM  Pack WHERE PName = @PName", con);
    cmd.Parameters.AddWithValue("@PName", txtPname.Text);
    int pid = Convert.ToInt16(cmd.ExecuteScalar());

    cmd = new SqlCommand("INSERT INTO PackDetails(PID,CodeItem,QTY) VALUES(@PID,@CodeItem,@QTY)", con);
    cmd.Parameters.AddWithValue("@PID", pid);
    cmd.Parameters.AddWithValue("@CodeItem", codeItem);
    cmd.Parameters.AddWithValue("@QTY", txtQty.Text);
    cmd.ExecuteNonQuery();
    con.Close();
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = Nothing
    cmd = New SqlCommand("INSERT INTO ItemMasterFile(Descriptionitem) VALUES(@Descriptionitem)", con)
    cmd.Parameters.AddWithValue("@Descriptionitem", txtDescriptionitem.Text)
    con.Open()
    cmd.ExecuteNonQuery()
    cmd = New SqlCommand("INSERT INTO Pack(PName) VALUES(@PName)", con)
    cmd.Parameters.AddWithValue("@PName", txtPname.Text)
    cmd.ExecuteNonQuery()
    cmd = New SqlCommand("SELECT CodeItem FROM  ItemMasterFile WHERE Descriptionitem = @Descriptionitem", con)
    cmd.Parameters.AddWithValue("@Descriptionitem", txtDescriptionitem.Text)
    Dim codeItem As Integer = Convert.ToInt16(cmd.ExecuteScalar())
    cmd = New SqlCommand("SELECT PID FROM  Pack WHERE PName = @PName", con)
    cmd.Parameters.AddWithValue("@PName", txtPname.Text)
    Dim pid As Integer = Convert.ToInt16(cmd.ExecuteScalar())
    cmd = New SqlCommand("INSERT INTO PackDetails(PID,CodeItem,QTY) VALUES(@PID,@CodeItem,@QTY)", con)
    cmd.Parameters.AddWithValue("@PID", pid)
    cmd.Parameters.AddWithValue("@CodeItem", codeItem)
    cmd.Parameters.AddWithValue("@QTY", txtQty.Text)
    cmd.ExecuteNonQuery()
    con.Close()
End Sub