Purvi's Blog

Add record to Database Using ASP.Net GridView EmptyDataTemplate and FooterTemplate

http://www.shibashishdotnetocean.com/2012/02/add-record-to-database-using-aspnet.html#.UZSUMaKmhPI

In this article I will explain how to display ASP.Net GridView’s Empty Data Template having textbox as well as giving chance to user for entering data into sql server database .

Database

The below screenshot displays the structure of the database table that will store the Your records as well as the .mdf file inside the app.data folder.

HTML Markup

Below is the HTML Markup of the ASP.Net GridView control

<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="true" CodeFile="EmptydataTemplate.aspx.cs" Inherits="EmptydataTemplate" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="Green" AutoGenerateColumns="false" ShowFooter="true">

<Columns>

<asp:TemplateField HeaderText="ID">

<ItemTemplate>

<%#Eval("ID") %>

</ItemTemplate>

<FooterTemplate>

<asp:TextBox ID="TxtID" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Shiba1">

<ItemTemplate>

<%#Eval("Shiba1") %>

</ItemTemplate>

<FooterTemplate>

<asp:TextBox ID="TxtShiba1" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Shiba2">

<ItemTemplate>

<%#Eval("shiba2") %>

</ItemTemplate>

<FooterTemplate>

<asp:TextBox ID="Txtshiba2" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Shiba3">

<ItemTemplate>

<%#Eval("shiba3") %>

</ItemTemplate>

<FooterTemplate>

<asp:TextBox ID="Txtshiba3" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField >

<FooterTemplate>

<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Add" CommandName="Footer" />

</FooterTemplate>

</asp:TemplateField>

</Columns>

<AlternatingRowStyle BackColor="#C2D69B" />

<EmptyDataTemplate>

<tr style="background-color: Green;">

<th scope="col">

</th>

<th scope="col">

Shiba1

</th>

<th scope="col">

Shiba2

</th>

<th scope="col">

Shiba3

</th>

<th scope="col">

</th>

</tr>

<tr>

<td>

<asp:TextBox ID="TxtID" runat="server"></asp:TextBox>

</td>

<td>

<asp:TextBox ID="TxtShiba1" runat="server"></asp:TextBox>

</td>

<td>

<asp:TextBox ID="Txtshiba2" runat="server"></asp:TextBox>

</td>

<td>

<asp:TextBox ID="Txtshiba3" runat="server"></asp:TextBox>

</td>

<td>

<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Add" CommandName="EmptyDataTemplate" />

</td>

</tr>

</EmptyDataTemplate>

</asp:GridView>

</div>

</form>

</body>

</html>

Binding the GridView

Below is the code to bind the data from the SQL Server database to the ASP.Net GridView control and insert data into sql server database.

C#(My code behind):

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;

public partial class EmptydataTemplate : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindData();

}

SqlConnection con = null;

SqlDataAdapter da = null;

DataSet ds = null;

string constr, SqlQuery;

public void BindData()

{

constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlQuery = "select * from SampleTable";

con = new SqlConnection(constr);

da = new SqlDataAdapter(SqlQuery,constr);

ds = new DataSet();

da.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

}

protected void Add(object sender, EventArgs e)

{

Control control = null;

if (GridView1.FooterRow != null)

{

control = GridView1.FooterRow;

}

else

{

control = GridView1.Controls[0].Controls[0];

}

int ID =int.Parse( (control.FindControl("TxtID") as TextBox).Text);

string Shiba1 = (control.FindControl("TxtShiba1") as TextBox).Text;

string shiba2 = (control.FindControl("Txtshiba2") as TextBox).Text;

string shiba3 = (control.FindControl("Txtshiba3") as TextBox).Text;

constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand())

{

cmd.Connection = con;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "INSERT INTO SampleTable VALUES(@ID,@Shiba1,@shiba2,@shiba3)";

cmd.Parameters.AddWithValue("@ID", ID);

cmd.Parameters.AddWithValue("@Shiba1", Shiba1);

cmd.Parameters.AddWithValue("@shiba2", shiba2);

cmd.Parameters.AddWithValue("@shiba3", shiba3);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

BindData();

}

The below screenshot displays GridView when there is no data in the database, you will notice that it is displaying the<EmptyDataTemplate> with Four textboxes and a button.