Insert Data in Database Using GridView Control

Hello friends, we know many ways to insert data into a database.

In this article we will see how to insert data into the SQL Database and GridView using a GridView TextBox control. GridView is an important control for any database record displayed in any web and desktop application.

In this example use an update panel control and CSS to design the GridView.

Now here we will see step-by-step how to insert data into the database and also display the inserted record in the GridView.

Step 1

Create a table.

Example

create table EmployeeDB
(
EmpID int primary key identity(1,1),
EmpName varchar(20),
EmpGender varchar(10),
EmpDOB date null,
EmpDepartment varchar(20)
)

Step 2

Create a SQL procedure to insert data into the database.

Create procedure sp_EmpDataInsert
@EmpName varchar(20),
@EmpGender varchar(10),
@EmpDOB date,
@EmpDepartment varchar(20)
as
begin
set nocount on;
insert into EmployeeDB
(EmpName,EmpGender,EmpDOB,EmpDepartment)
values
(@EmpName,@EmpGender,@EmpDOB,@EmpDepartment)
End

Step 3

Now go to your project design (.aspx) page.



Add a page using Master page.



Step 4

Add a Script Manager as in the following:

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" EnablePageMethods="true" >
</asp:ScriptManager>

Step 5

Add an Update panel for the currently inserted record as in the following:

<asp:UpdatePanel runat="server" UpdateMode="Always" >
</asp:UpdatePanel>

Step 6

Add a GridView control inside the Update panel as in the following:

<asp:UpdatePanel runat="server" UpdateMode="Always"  >
<ContentTemplate>
<asp:GridView runat="server" ID="GridEmpData"
                 CssClass="Grid"
                 AutoGenerateColumns="false"
                 ShowFooter="true" >
</asp:GridView>
</ContentTemplate>

</asp:UpdatePanel>

Step 7

Add a CSS Style for the GridView Design as in the following:

<style>
        body { font-size:65% }

        #Employee {
            font:11px Times New Roman;
            width:auto;
            display:block;
            padding:10px 0 0 0;
        }

        .text {
            width:auto;
            padding:2px 4px;
            font:inherit;
            font-weight:bold;
            text-align:left;
            border:solid 2px #BFBFBF;
            background:yellow;
            text-transform:uppercase;
        }

        .Grid {
            width:100%;
            font:inherit;
            margin:5px 0 10px 0;
            background-color:#FFF;
            border:solid 2px #525252;
            text-transform:uppercase;
        }
        .Grid td {
            font:inherit;
            padding:2px;
            border:solid 1px #C1C1C1;
            color:#333;
            text-align:center;
        }
        .Grid th {
            padding:5px;
            color:blue;
            background:#424234 border-left:solid 3px #525233;
            font:inherit;
            font-weight:bold;
        }

        .Gridbutton {
            cursor:pointer;
            text-align:center;
            color: white;
            font:inherit;
            background-color: blue;
            border:solid 1px #3079ED;
            -moz-border-radius:5px;
            -webkit-border-radius:5px;
            line-height:20px;
        }
    </style>

Step 8

Finally, create your .aspx design page for the GridView as in the following:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.Master" AutoEventWireup="true" CodeBehind="GridDemo.aspx.cs" Inherits="Test_WebApplication.Form.WebForm1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">

<div>

    <%--GRIDVIEW CSS--%>
      <style>
        body { font-size:62.5% }

        #Employee {
            font:11px Times New Roman;
            width:auto;
            display:block;
            padding:10px 0 0 0;
        }

        .text {
            width:auto;
            padding:2px 4px;
            font:inherit;
            font-weight:bold;
            text-align:left;
            border:solid 2px #BFBFBF;
            background:yellow;
            text-transform:uppercase;
        }

        .Grid {
            width:100%;
            font:inherit;
            margin:5px 0 10px 0;
            background-color:#FFF;
            border:solid 2px #525252;
            text-transform:uppercase;
        }
        .Grid td {
            font:inherit;
            padding:2px;
            border:solid 1px #C1C1C1;
            color:#333;
            text-align:center;
        }
        .Grid th {
            padding:5px;
            color:blue;
            background:#424234 border-left:solid 3px #525233;
            font:inherit;
            font-weight:bold;
        }

        .Gridbutton {
            cursor:pointer;
            text-align:center;
            color: white;
            font:inherit;
            background-color: blue;
            border:solid 1px #3079ED;
            -moz-border-radius:5px;
            -webkit-border-radius:5px;
            line-height:20px;
        }
    </style>

</div>

    <%--- Add Script Manager ----%>
    <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" EnablePageMethods="true" >
    </asp:ScriptManager>

    <div id="Employee">
        <asp:UpdatePanel runat="server" UpdateMode="Always"  >
            <ContentTemplate>
                <asp:GridView runat="server" ID="GridEmpData"
                 CssClass="Grid"
                 AutoGenerateColumns="false"
                 ShowFooter="true" >
                 <Columns>
                    <asp:TemplateField HeaderText="Employee No.">
                        <ItemTemplate><%#Eval("EmpID")%></ItemTemplate>
                        <FooterTemplate><h4><i> INSERT NEW RECORD </i></h4></FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Employee Name">
                        <ItemTemplate><%#Eval("EmpName")%></ItemTemplate>
                        <FooterTemplate><asp:TextBox runat="server" ID="txtEmpName" CssClass="text"></asp:TextBox></FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Gender">
                        <ItemTemplate><%#Eval("EmpGender")%></ItemTemplate>
                        <FooterTemplate><asp:TextBox runat="server" ID="txtEmpGender" CssClass="text"></asp:TextBox></FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Date of Birth">
                        <ItemTemplate><%#Eval("EmpDOB")%></ItemTemplate>
                        <FooterTemplate><asp:TextBox runat="server" ID="txtEmpDOB" CssClass="text"></asp:TextBox></FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Department">
                        <ItemTemplate><%#Eval("EmpDepartment")%></ItemTemplate>
                        <FooterTemplate><asp:TextBox runat="server" ID="txtEmpDpt" CssClass="text" ></asp:TextBox></FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField>
                        <ItemTemplate></ItemTemplate>
                        <FooterTemplate><asp:Button runat="server" ID="btnSave" Text="SAVE NEW RECORD" CssClass="Gridbutton" CommandName="Footer" onclick="btnSave_Click" /></FooterTemplate>
                    </asp:TemplateField>
                 </Columns>

                 <EmptyDataTemplate>
                    <tr>
                        <th>Employee Name</th>
                        <th>Gender</th>
                        <th>Date of Birth</th>
                        <th>Department</th>
                        <th></th>
                    </tr

                    <tr>
                        <td><asp:TextBox runat="server" ID="txtEmpName" CssClass="text"></asp:TextBox></td>
                        <td><asp:TextBox runat="server" ID="txtEmpGender" CssClass="text"></asp:TextBox></td>
                        <td><asp:TextBox runat="server" ID="txtEmpDOB" CssClass="text"></asp:TextBox></td>
                        <td><asp:TextBox runat="server" ID="txtEmpDpt" CssClass="text" ></asp:TextBox></td>
                        <td><asp:Button runat="server" ID="btnSave" Text="SAVE NEW RECORD" CssClass="Gridbutton" onclick="btnSave_Click" CommandName="EmptyDataTemplate" /></td>
                    </tr>

                 </EmptyDataTemplate>

                </asp:GridView>
            </ContentTemplate>

        </asp:UpdatePanel>

    </div>

    <asp:Label runat="server" ID="lblmsg"></asp:Label>
</asp:Content>

In this Design page add a GridView control and all database fields added in the GridView Template field and also in the last add a TextBox control for the record insert into the database and Button control inside the GridView Footer Template.

Also define an EmptyDataTemplate in the GridView design for the blank TextBox display and button control.

Step 9

Configure your database connection string in your project web.config file.

<connectionStrings>
    <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=your SQL DB Password" providerName="System.Data.SqlClient"/>

    <add name="Pratical_testConnectionString" connectionString="Data Source=RAKESH-PC;Initial Catalog=Pratical_test;User ID=sa" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Step 10

In the last step write the back-end code for the GridView data insert into the database and display on the grid view.

In the Page Design back-end write the following code.

First define the connection string as in the following:

string conString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

After writing the code for the Grid View Data Bind in EmployeeDataGrid().

The following is the back-end 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 Test_WebApplication.Form
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        string conString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                EmployeeDataGrid();
            }
        }
        private void EmployeeDataGrid()
        {
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlCommand cmd = new SqlCommand("select * from EmployeeDB"))
                {
                    SqlDataAdapter dt = new SqlDataAdapter();
                    try
                    {
                        cmd.Connection = con;
                        con.Open();
                        dt.SelectCommand = cmd;

                        DataTable dTable = new DataTable();
                        dt.Fill(dTable);

                        GridEmpData.DataSource = dTable;
                        GridEmpData.DataBind();
                    }
                    catch(Exception)
                    {
                        lblmsg.Text = "record not found";
                    }
                }
            }
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            Button btn = (Button)sender;
            GridViewRow GrdRow = (GridViewRow)btn.Parent.Parent;

            TextBox txtEmpName = (TextBox)GrdRow.Cells[0].FindControl("txtEmpName");
            TextBox txtEmpGender = (TextBox)GrdRow.Cells[0].FindControl("txtEmpGender");
            TextBox txtEmpDOB = (TextBox)GrdRow.Cells[0].FindControl("txtEmpDOB");
            TextBox txtEmpDpt = (TextBox)GrdRow.Cells[0].FindControl("txtEmpDpt");


            using (SqlConnection con = new SqlConnection(conString))
            {
                 using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_EmpDataInsert";

                    cmd.Connection = con;
                    con.Open();

                    cmd.Parameters.AddWithValue("@EmpName",SqlDbType.VarChar).Value=txtEmpName.Text.Trim();
                    cmd.Parameters.AddWithValue("@EmpGender",SqlDbType.VarChar).Value=txtEmpGender.Text.Trim();
                    cmd.Parameters.AddWithValue("@EmpDOB",SqlDbType.Date).Value=txtEmpDOB.Text.Trim();
                    cmd.Parameters.AddWithValue("@EmpDepartment",SqlDbType.VarChar).Value=txtEmpDpt.Text;

                    cmd.ExecuteNonQuery();
                }
            }
            EmployeeDataGrid();
        }

    }
}

Finally to see your GridView control on your web form press F5 and run your aspx page.



And also, first check the database EmployeeDB table all the record fields are null using the SQL Select Query command.

select * from EmployeeDB
--No Record inside table

Now insert a record into the TextBox and press the button to insert a TextBox record in the database and display a Grid View.

First fill in the TextBox and then press the button SAVE NEW RECORD.



Next see insert a record into the Grid view.





And check also your inserted record in the database using the select Query command.



Finally check your inserted grid view record and that the database inserted records are the same. So the GridView example is complete. I hope you understand this example.

We are many more operation doing on TextBox data using GridView control as like data insert, update, delete. All types of these operations will be seen in a future article.