In this article I will explain with an example, how to insert records to SQL Server database using GridView EmptyDataTemplate and FooterTemplate in ASP.Net using C# and VB.Net.
 
 

Database

I have made use of the following table Customers with the schema as follows.
Add record to Database using ASP.Net GridView EmptyDataTemplate and FooterTemplate
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

HTML Markup

The following HTML Markup consists of:
GridView – For displaying data.

Properties

ShowFooter – It defined whether the footer will be displayed or not.
 

Columns

GridView consists of three TemplateField columns.
TemplateField - It consists of ItemTemplate and FooterTemplate.
 

ItemTemplate :

The ItemTemplate is used to display the Name and Country columns.
 

FooterTemplate :

The FooterTemplate consists of two TextBoxes and a Button.
The Button has been assigned with an OnClick event handler and the CommandName property set to Footer.
 

EmptyDataTemplate

The EmptyDataTemplate consists of two TextBoxes and a Button.
The Button has been assigned with an OnClick event handler and the CommandName property set to EmptyDataTemplate.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" ShowFooter="true">
    <Columns>
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <%# Eval("Name") %>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <%# Eval("Country") %>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtCountry" 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>
    <EmptyDataTemplate>
        <tr>
            <th scope="col">Name</th>
            <th scope="col">Country</th>
            <th scope="col"></th>
        </tr>
        <tr>
            <td><asp:TextBox ID="txtName" runat="server" /></td>
            <td><asp:TextBox ID="txtCountry" runat="server" /></td>
            <td><asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Add" CommandName="EmptyDataTemplate" /></td>
        </tr>
    </EmptyDataTemplate>
</asp:GridView>
 
 

Namespaces

You will need to import following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
 
 

Binding the GridView

Inside the Page_Load event handler, the BindGrid method is called.
BindGrid
Inside the BindGrid method, the records are fetched from the SQL Server database and the GridView is populated.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT Name, Country FROM Customers";
 
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    con.Open();
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                    con.Close();
                }
            }
        }
    }
}
 
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub
 
Private Sub BindGrid()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT Name, Country FROM Customers"
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    con.Open()
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                    con.Close()
                End Using
            End Using
        End Using
    End Using
End Sub
 
 

Add new record to database using GridView

When the Add Button is clicked, an object of type Control is created.
Then, the check is performed if the FooterRow is not NULL, then the FooterRow is referenced else the EmptyDataTemplate row will be referenced.
Finally, the Name and Country TextBoxes are referenced and the record will be inserted into the SQL Server database using ExecuteNonQuery function.
Note: For more details on ExecuteNonQuery function, please refer my article Understanding SqlCommand ExecuteNonQuery in C# and VB.Net.
 
C#
protected void Add(object sender, EventArgs e)
{
    Control control = null;
    if (gvCustomers.FooterRow != null)
    {
        control = gvCustomers.FooterRow;
    }
    else
    {
        control = gvCustomers.Controls[0].Controls[0];
    }
 
    string name = (control.FindControl("txtName") as TextBox).Text;
    string country = (control.FindControl("txtCountry") as TextBox).Text;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "INSERT INTO Customers VALUES(@Name, @Country)";
 
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Country", country);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    Response.Redirect(Request.Url.AbsoluteUri);
} 
 
VB.Net
Protected Sub Add(ByVal sender As Object, ByVal e As EventArgs)
    Dim control As Control = Nothing
 
    If gvCustomers.FooterRow IsNot Nothing Then
        control = gvCustomers.FooterRow
    Else
        control = gvCustomers.Controls(0).Controls(0)
    End If
 
    Dim name As String = (TryCast(control.FindControl("txtName"), TextBox)).Text
    Dim country As String = (TryCast(control.FindControl("txtCountry"), TextBox)).Text
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "INSERT INTO Customers VALUES(@Name, @Country)"
 
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@Name", name)
            cmd.Parameters.AddWithValue("@Country", country)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub
 
 

Screenshot

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

Downloads

Download Code