Thursday, February 23

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


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">
       
       ID
       </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.




Here is my Design view of the gridview:-

After saving data it will display as:-


Thanks Shibashish Mohanty

6 comments:

  1. http://webgeek.elletis.com/how-to-enter-html-code-into-database-from-a-textbox/

    ReplyDelete

  2. Great thoughts you got there, believe I may possibly try just some of it throughout my daily life.

    Elletis Web Geek

    ReplyDelete
    Replies
    1. Hello Shibashish,
      This is really a Nice code, Thanks alot.
      But how can i retrieve data from SQL DB to Gridview Textboxes ?
      Please help me out...Thanks in Advanced..
      sumit080177@gmail.com

      Delete
    2. Dear sumit,
      To binding in textbox within gridview is very simple one.you just set autogenerate column property false and bind data to gridview and in textbox text property write Text='<%#Eval("YourColumnName")%>'

      Delete

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty