Insert record into Database using Gridview in Asp.net

Description:-

In this tutorial I am going to explain how to insert a new record into database using Gridview FooterTemplate in Asp.net.

In this article I am going to use Gridview to insert new record into database. To implement this use the FooterTemplate inside the Template Field. Put the textbox control inside FooterTemplate and set require field validation on textboxes.

Create a table:-

CREATE TABLE [dbo].[Employeee](
                [Number] [int] IDENTITY(1,1) NOT NULL,
                [Name] [varchar](50) NOT NULL,
                [Gender] [varchar](50) NOT NULL,
                [Email] [varchar](50) NOT NULL,
                [MobileNumber] [bigint] NOT NULL,
                [Bdate] [date] NULL,
 CONSTRAINT [PK_Employeee] PRIMARY KEY CLUSTERED
(
                [Number] 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

Default.aspx:-

    <div>
        <asp:GridView ID="GridView1" runat="server" Width="550px" AutoGenerateColumns="False"
            ShowFooter="True" AllowPaging="True" CellPadding="4" ForeColor="#333333"
            GridLines="None" onpageindexchanging="GridView1_PageIndexChanging"
            onrowcommand="GridView1_RowCommand">
            <Columns>
                <asp:TemplateField HeaderText="Name">
                    <ItemTemplate>
                        <asp:Label ID="lblname" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtname" runat="server" />
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Enter Name"
                            ControlToValidate="txtname"></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Gender">
                    <ItemTemplate>
                        <asp:Label ID="lblGender" runat="server" Text='<%# Eval("Gender") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtGender" runat="server" />
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Gender"
                            ControlToValidate="txtGender"></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Email">
                    <ItemTemplate>
                        <asp:Label ID="lblEmail" runat="server" Text='<%# Eval("Email") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Enter Email"
                            ControlToValidate="txtEmail"></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Mobile Number">
                    <ItemTemplate>
                        <asp:Label ID="lblMobileNumber" runat="server" Text='<%# Eval("MobileNumber") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtMobileNumber" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Enter Mobile Number"
                            ControlToValidate="txtMobileNumber"></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
                <asp:TemplateField>
                    <ItemTemplate>
                    </ItemTemplate>
                    <FooterTemplate>
                     <asp:Button ID="btninsert" runat="server" Text="Insert Record" CommandName="Insert" />
                    </FooterTemplate>
                    <ItemStyle VerticalAlign="Top" />
                </asp:TemplateField>
            </Columns>
            <EditRowStyle BackColor="#999999" />
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#E9E7E2" />
            <SortedAscendingHeaderStyle BackColor="#506C8C" />
            <SortedDescendingCellStyle BackColor="#FFFDF8" />
            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>
    </div>

Web.Config:-

<connectionStrings>
    <add connectionString="ConnectionString" name="DBCS" providerName="System.Data.SqlClient"/>   
</connectionStrings>

Default.aspx.cs:-

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString());
protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    BindGridview();
  }
}

public void BindGridview()
{
  try
  {
    SqlDataAdapter adp = new SqlDataAdapter("Select * from Employeee", con);
    DataTable dt = new DataTable();
    adp.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
  }
  catch (Exception ex)
  {
  }
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
  GridView1.PageIndex = e.NewPageIndex;
  BindGridview();
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
  if (e.CommandName == "Insert")
  {
    SqlCommand cmd = new SqlCommand("Insert into Employeee(Name,Gender,Email,MobileNumber) values(@Name,@Gender,@Email,@MobileNumber)", con);
    TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtname");
    TextBox txtGender = (TextBox)GridView1.FooterRow.FindControl("txtGender");
    TextBox txtEmail = (TextBox)GridView1.FooterRow.FindControl("txtEmail");
    TextBox txtMobileNumber = (TextBox)GridView1.FooterRow.FindControl("txtMobileNumber");
    con.Open();
    cmd.Parameters.AddWithValue("@Name", txtname.Text);
    cmd.Parameters.AddWithValue("@Gender", txtGender.Text);
    cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
    cmd.Parameters.AddWithValue("@MobileNumber", txtMobileNumber.Text);
    cmd.ExecuteNonQuery();
    con.Close();
    Response.Write("<script type=\"text/javascript\">alert('Record Insert Successfully!!!');</script>");
    BindGridview();
    txtname.Text = string.Empty;
    txtGender.Text = string.Empty;
    txtEmail.Text = string.Empty;
    txtMobileNumber.Text = string.Empty;
  }
}

Author : Umesh Patel

Related Posts