Insert (Save) data in multiple table using primary and foreign key in ASP.Net

Last Reply 4 days ago By dharmendr

Posted 4 days ago

how to insert data in multiple table using primary & foreign key in asp.net

I have 2 table 

1st table having st_ID, Title

2nd table having ID,Paragraph.

how should I save the Paragraph with ID first 2-3 times as per need but every time ID should be same .

after that I have to save finally Title with st_ID.

I have created 1st Table with st_ID autoincrement

but ID of 2nd table is not auto increment.

St_ID Title Isactive
1 Buy 1
2 Sell 1
     

 

ID Paragraph Image Date Isactive
1 I have to buy a book.   22/05/2020 1
1 I have to buy a copy.   22/05/2020 1
1 I have to buy a pen.   22/05/2020 1
2 Who are they?   22/05/2020 1
2 Kids are playing    22/05/2020 1

Like this I need

Posted 4 days ago Modified on 3 days ago

Hi vrindavani,

Check the below example. Change column and table name as per your database structure.

Database

CREATE TABLE Parent(St_ID INT IDENTITY PRIMARY KEY,Title VARCHAR(50),Isactive BIT)
CREATE TABLE Child(ID INT,Paragraph NVARCHAR(MAX),Image VARCHAR(500),Date DATE,Isactive BIT)

HTML

<div class="container">
    <div class="row" style="margin-top: 1.5%">
        <asp:Label ID="lbl_id" runat="server" Text="st_ID" Visible="true"></asp:Label>
        <div class="col-md-6">
            <label>Title</label>
            <asp:TextBox ID="txt_title" runat="server" CssClass="form-control" placeholder="Title"></asp:TextBox>
        </div>
        <div class="col-md-6">
            <asp:Button ID="btnsubmit" runat="server" Text="Submit" CssClass="btn btn-danger" Style="color: white; background-color: #1b4971;" OnClick="btnsubmit_Click" Width="112px" />
        </div>
    </div>
    <div class="row" style="margin-top: 1.5%">
        <asp:Label ID="lblid" runat="server" Text="ID" Visible="false"></asp:Label>
        <div class="col-md-12 mg">
            <label>Paragraph1</label>
            <asp:TextBox ID="txt_paragraph1" runat="server" CssClass="form-control" placeholder="Please Enter Your Paragraph " TextMode="MultiLine" Height="90"></asp:TextBox>
        </div>
        <div class="col-md-6">
            <label>Upload Image</label>
            <asp:FileUpload ID="Fu" CssClass="form-control" runat="server" />
        </div>
        <div class="col-md-4">
        </div>
    </div>
    <div class="row" style="margin-top: 1.5%">
        <div class="col-md-8">
            <asp:Button ID="btnsave" runat="server" Text="Save" CssClass="btn btn-danger" Style="height: 35px; width: 20%; color: white; background-color: #1b4971;" OnClick="btnsave_Click" />
        </div>
        <div class="col-md-4">
        </div>
    </div>
</div>
<asp:GridView ID="Grid_study" runat="server" Width="100%" DataKeyNames="ID" class="table table-bordered table-hover" HeaderStyle-BackColor="" AutoGenerateColumns="False"
    BorderStyle="None" GridLines="Horizontal" EmptyDataText="No Record Found" AllowPaging="True" PageSize="35">
    <Columns>
        <asp:TemplateField HeaderText="Select">
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="S.No." ItemStyle-HorizontalAlign="Left">
            <ItemTemplate>
                <asp:Label Visible="false" ID="lblid" runat="server" Text='<%#Eval("ID")%>'>
                </asp:Label>
                <%#Container.DataItemIndex+1%>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Description">
            <ItemTemplate>
                <asp:Label ID="lblparagraph1" runat="server" Text='<%#Eval("Paragraph")%>'> 
                </asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Image">
            <ItemTemplate>
                <asp:Image CssClass="img-thumbnail" ID="imgp" Style="height: 50px; width: 100px; object-fit: cover;" runat="server" ImageUrl='<%#string.Concat("../Img/",Eval("Image"))%>'></asp:Image>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <PagerSettings PageButtonCount="15" />
    <PagerStyle CssClass="GridPager" HorizontalAlign="Left" />
</asp:GridView>

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

protected void btnsave_Click(object sender, EventArgs e)
{
    int id = GetMaxid();
    string strname = "";
    if (Fu.HasFile)
    {
        strname = Fu.FileName.ToString();
        Fu.PostedFile.SaveAs(Server.MapPath("~/Img/") + strname);
    }

    string MyCmdText = ("insert into Child (ID,Paragraph,Image,Isactive) values('" + id + "',N'" + txt_paragraph1.Text.Replace(Environment.NewLine, "<br />") + "','" + strname + "','" + 1 + "')");
    bool ResultF = ExecuteInsertUpdateOrDelete(MyCmdText);
    if (ResultF == true)
    {
        ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert(' Record Inserted Successfully !!! ');", true);
    }
    else
    {
        ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Record Not Save Successfully .')", true);
    }
    BindGrid();
}

protected void btnsubmit_Click(object sender, EventArgs e)
{
    string MyCmdText = ("insert into Parent values (N'" + txt_title.Text + "','" + 1 + "')");
    bool Result = ExecuteInsertUpdateOrDelete(MyCmdText);
    if (Result == true)
    {
        ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record Inserted Successfully !!! ');", true);
    }
    else
    {
        ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Record Not Save Successfully .')", true);
    }
}

private bool ExecuteInsertUpdateOrDelete(string query)
{
    string conStr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(conStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = query;
    cmd.CommandType = CommandType.Text;
    con.Open();
    bool isInsert = cmd.ExecuteNonQuery() > 0 ? true : false;
    con.Close();
    return isInsert;
}
private void BindGrid()
{
    string conStr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(conStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "SELECT * FROM Child";
    cmd.CommandType = CommandType.Text;
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    Grid_study.DataSource = dt;
    Grid_study.DataBind();
}
public int GetMaxid()
{
    string conStr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(conStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "select Max(St_ID) as ID from Parent";
    cmd.CommandType = CommandType.Text;
    con.Open();
    int id = cmd.ExecuteScalar() != DBNull.Value ? Convert.ToInt32(cmd.ExecuteScalar()) : 0;
    con.Close();

    return id;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindGrid()
    End If
End Sub

Protected Sub btnsave_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim id As Integer = GetMaxid()
    Dim strname As String = ""

    If Fu.HasFile Then
        strname = Fu.FileName.ToString()
        Fu.PostedFile.SaveAs(Server.MapPath("~/Img/") & strname)
    End If

    Dim MyCmdText As String = ("insert into Child (ID,Paragraph,Image,Isactive) values('" & id & "',N'" & txt_paragraph1.Text.Replace(Environment.NewLine, "<br />") & "','" & strname & "','" + 1 & "')")
    Dim ResultF As Boolean = ExecuteInsertUpdateOrDelete(MyCmdText)

    If ResultF = True Then
        ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert(' Record Inserted Successfully !!! ');", True)
    Else
        ScriptManager.RegisterStartupScript(Me, [GetType](), "showalert", "alert('Record Not Save Successfully .')", True)
    End If

    BindGrid()
End Sub

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim MyCmdText As String = ("insert into Parent values (N'" & txt_title.Text & "','" + 1 & "')")
    Dim Result As Boolean = ExecuteInsertUpdateOrDelete(MyCmdText)

    If Result = True Then
        ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('Record Inserted Successfully !!! ');", True)
    Else
        ScriptManager.RegisterStartupScript(Me, [GetType](), "showalert", "alert('Record Not Save Successfully .')", True)
    End If
End Sub

Private Function ExecuteInsertUpdateOrDelete(ByVal query As String) As Boolean
    Dim conStr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(conStr)
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.Connection = con
    cmd.CommandText = query
    cmd.CommandType = CommandType.Text
    con.Open()
    Dim isInsert As Boolean = If(cmd.ExecuteNonQuery() > 0, True, False)
    con.Close()
    Return isInsert
End Function

Private Sub BindGrid()
    Dim conStr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(conStr)
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.Connection = con
    cmd.CommandText = "SELECT * FROM Child"
    cmd.CommandType = CommandType.Text
    Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    sda.Fill(dt)
    Grid_study.DataSource = dt
    Grid_study.DataBind()
End Sub

Public Function GetMaxid() As Integer
    Dim conStr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(conStr)
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.Connection = con
    cmd.CommandText = "select Max(St_ID) as ID from Parent"
    cmd.CommandType = CommandType.Text
    con.Open()
    Dim id As Integer = If(Not IsDBNull(cmd.ExecuteScalar()), Convert.ToInt32(cmd.ExecuteScalar()), 0)
    con.Close()
    Return id
End Function