Last updated: 10th March 2025
The Asp.Net GridView control is a powerful and versatile tool commonly used by web developers in their websites and web applications. Known for its user-friendly interface and rich features, it simplifies data handling and display.In a previous article, I shared insights on how to edit, update, and delete records within a GridView control in Asp.Net. However, one important aspect I missed is explaining how to insert new records into a database table using a GridView. This guide will walk you through the process of adding new rows to a GridView control and inserting corresponding records into your database.
Additionally, I will demonstrate how to integrate a jQuery Datepicker control into each row of a GridView while in edit mode. The images below provide a clear visualization, showing the Datepicker widget in action, positioned just before the "Insert Record" button.
Insert new records using GridView
Imagine this scenario: XYZ company lends books to its employees on weekends and keeps a detailed database of the issued books. The records include the employee's name, the issuing date, and the book category. This straightforward example serves as an excellent way to demonstrate how the Asp.Net GridView can be utilized to insert records into a database, row by row.
First create a table dbo.Books in the database
"I recommend using this SQL Server dummy database with tables and add few records in it."
CREATE TABLE dbo.Books ( BookID INT IDENTITY(1,1) PRIMARY KEY, BookName VARCHAR(50) NULL, Category VARCHAR(50) NULL, EmployeeName VARCHAR(100), DateIssued DATETIME NULL )
I am using jQuery for two reasons.
01) To show a dropdown list of "Books" using jQuery AutoComplete feature. An array provides the name of the Books.
02) jQuery .datepicker() function is used to select the "issuing date" in one of the cells in the GridView control.
$(function() { $('input[id*=tbIssueDate]').datepicker(); }); // jQuery DATE PICKER.
Learn more about jQuery .datepicker().
The Markup with CSS
<head>
<title>Insert New Records in Database Table Using GridView</title>
<%--jQuery CDN.--%>
<link href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>
<style>
#divBooks {
width: 750px;
overflow-y: auto;
display: block;
padding-top: 10px;
}
.textbox {
width: 100px;
padding: 4px;
font: inherit;
text-align: left;
border: 1px solid #BFBFBF;
border-radius: 4px;
text-transform: uppercase;
}
.Grid {
width: 100%;
margin: 10px 0;
background-color: #FFF;
border: 1px solid #525252;
border-collapse: collapse;
}
.Grid td {
padding: 5px;
border: 1px solid #C1C1C1;
color: #333;
text-align: center;
}
.Grid th {
padding: 8px;
color: #FFF;
background-color: #424242;
border-left: 1px solid #525252;
font: inherit;
}
.button {
cursor: pointer;
text-align: center;
color: #FFF;
background-color: #4D90FE;
border: 1px solid #3079ED;
border-radius: 6px;
line-height: 20px;
padding: 4px 10px;
transition: background-color 0.3s ease;
}
.button:hover {
background-color: #2C70D7;
}
</style>
</head>Add the GridView control
<body>
<form id="frmBooks" runat="server">
<asp:ScriptManager ID="scriptManager" runat="server" EnablePartialRendering="true" EnablePageMethods="true" />
<div id="divBooks">
<asp:UpdatePanel runat="server" UpdateMode="Conditional">
<ContentTemplate>
<asp:GridView ID="GridView1"
AutoGenerateColumns="false"
CssClass="Grid"
GridLines="None"
ShowFooter="true"
AllowPaging="true"
PageSize="5"
runat="server">
<Columns>
<asp:TemplateField HeaderText="ID">
<ItemTemplate><%#Eval("BookID")%> </ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Book">
<ItemTemplate> <%#Eval("BookName")%> </ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="tbBook" Width="200px" CssClass="textbox" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Category">
<ItemTemplate> <%#Eval("Category")%> </ItemTemplate>
<FooterTemplate><asp:TextBox ID="tbCategory" CssClass="textbox" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate> <%#Eval("EmployeeName")%></ItemTemplate>
<FooterTemplate><asp:TextBox ID="tbEmpName" Width="140px" CssClass="textbox"
runat="server"></asp:TextBox></FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Issue Date">
<ItemTemplate><asp:Label ID="lblIsseDate" width="140px" runat="server" Text='
<%#Eval("DateIssued")%>'></asp:Label></ItemTemplate>
<FooterTemplate><asp:TextBox ID="tbIssueDate" DataFormatString="{dd/MM/yyyy}"
CssClass="textbox" runat="server"></asp:TextBox></FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate></ItemTemplate>
<FooterTemplate>
<asp:Button ID="btInsert"
Text="Insert Record" CssClass="button"
OnClick="InsertRecord"
CommandName="Footer" runat="server" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate> <%--SHOWS BLANK FILEDS AT THE BOTTOM OF THE GRIDVIEW.--%>
<tr>
<th>Book</th>
<th>Category</th>
<th>Employee Name</th>
<th>Issue Date</th>
<th></th>
</tr>
<tr>
<td><asp:TextBox ID="tbBookName" Width="200px" CssClass="textbox" runat="server" /></td>
<td><asp:TextBox ID="tbCategory" CssClass="textbox" runat="server" /></td>
<td><asp:TextBox ID="tbEmpName" Width="200px" CssClass="textbox" runat="server" /></td>
<td>
<asp:TextBox ID="tbIssueDate"
DataFormatString="{dd/MM/yyyy}"
CssClass="textbox"
runat="server" />
</td>
<td>
<asp:Button ID="btInsert" Text="Insert Record"
CssClass="button" OnClick="InsertRecord"
CommandName="EmptyDataTemplate" runat="server" />
</td>
</tr>
</EmptyDataTemplate>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>The jQuery Script
We will create a script to attach an AutoComplete drop down list to the first column if the GridView. The drop down list shows a list of books.
<script>
$(document).ready(function() {
var prm = Sys.WebForms.PageRequestManager.getInstance();
prm.add_initializeRequest(InitializeRequest);
prm.add_endRequest(EndRequest);
BindControls();
});
function InitializeRequest(sender, args) { }
function EndRequest(sender, args) { BindControls(); }
function BindControls() {
// BOOKS IN AN ARRAY.
var books = ['The DNA of Unix Programming', 'The Hacker Crackdown',
'History of Economic Meltdown', 'Business of Basic Economics',
'Hospitality Accounts', 'The Alejandra Variations', 'Ancient Echoes'];
$('input[id*=tbBookName]').autocomplete({ source: books });
// jQuery DATE PICKER.
$(function() { $('input[id*=tbIssueDate]').datepicker(); });
}
</script>Code Behind (C#)
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
partial class _Default : System.Web.UI.Page
{
string sCon = "Data Source=dna;Persist Security Info=False;Integrated Security=SSPI;" +
"Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;";
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid_With_Data();
}
}
private void BindGrid_With_Data()
{
using (SqlConnection con = new SqlConnection(sCon))
{
using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
{
SqlDataAdapter sda = new SqlDataAdapter();
try
{
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
// BIND DATABASE WITH THE GRIDVIEW.
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{ // }
}
}
}
protected void GridView1_PageIndexChanging(object sender,
System.Web.UI.WebControls.GridViewPageEventArgs e)
{
// GRIDVIEW PAGING.
GridView1.PageIndex = e.NewPageIndex;
BindGrid_With_Data();
}
protected void InsertRecord(object sender, EventArgs e)
{
// GET THE ACTIVE GRIDVIEW ROW.
Button bt = (Button)sender;
GridViewRow grdRow = (GridViewRow)bt.Parent.Parent;
// NOW GET VALUES FROM FIELDS FROM THE ACTIVE ROW.
TextBox tbBookName = (TextBox)grdRow.Cells[0].FindControl("tbBookName");
TextBox tbCategory = (TextBox)grdRow.Cells[0].FindControl("tbCategory");
TextBox tbEmpName = (TextBox)grdRow.Cells[0].FindControl("tbEmpName");
TextBox tbDate = (TextBox)grdRow.Cells[0].FindControl("tbIssueDate");
using (SqlConnection con = new SqlConnection(sCon))
{
using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
{
// FINALLY INSERT ROW VALUES IN THE TABLE.
cmd.Connection = con;
con.Open();
cmd.CommandText = "INSERT INTO dbo.Books (BookName, Category,
EmployeeName, DateIssued) " +
"VALUES(@BookName, @Category, @EmployeeName, @DateIssued)";
cmd.Parameters.AddWithValue("@BookName", tbBookName.Text.Trim());
cmd.Parameters.AddWithValue("@Category", tbCategory.Text.Trim());
cmd.Parameters.AddWithValue("@EmployeeName", tbEmpName.Text.Trim());
cmd.Parameters.AddWithValue("@DateIssued", tbDate.Text.Trim() + " " +
DateTime.Now.TimeOfDay);
cmd.ExecuteNonQuery();
}
}
// REFRESH THE GRIDVIEW CONTROL TO SHOW THE NEWLY INSERTED ROW.
BindGrid_With_Data();
}
}VB.NET
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim SqlCom As SqlCommand
Dim sCon As String = "Data Source=DNA;Persist Security Info=False;" & _
"Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;"
Protected Sub frmBooks_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles frmBooks.Load
If Not Page.IsPostBack Then
BindGrid_With_Data()
End If
End Sub
Private Sub BindGrid_With_Data()
Using con As SqlConnection = New SqlConnection(sCon)
Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")
Dim sda As SqlDataAdapter = New SqlDataAdapter
Try
cmd.Connection = con : con.Open()
sda.SelectCommand = cmd
Dim dt As DataTable = New DataTable
sda.Fill(dt)
' BIND DATABASE WITH THE GRIDVIEW.
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As Exception
'
End Try
End Using
End Using
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object,
ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
Handles GridView1.PageIndexChanging
' GRIDVIEW PAGING.
GridView1.PageIndex = e.NewPageIndex
BindGrid_With_Data()
End Sub
' INSERT THE RECORDS.
Protected Sub InsertRecord(ByVal sender As Object, ByVal e As EventArgs)
' GET THE ACTIVE GRIDVIEW ROW.
Dim bt As Button = DirectCast(sender, Button)
Dim grdRow As GridViewRow = DirectCast(bt.Parent.Parent, GridViewRow)
' NOW GET VALUES FROM FIELDS FROM THE ACTIVE ROW.
Dim tbBookName As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbBookName"), TextBox)
Dim tbCategory As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbCategory"), TextBox)
Dim tbEmpName As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbEmpName"), TextBox)
Dim tbDate As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbIssueDate"), TextBox)
Using con As SqlConnection = New SqlConnection(sCon)
Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")
' FINALLY INSERT ROW VALUES IN THE TABLE.
With cmd
.Connection = con : con.Open()
.CommandText = "INSERT INTO dbo.Books ( " & _
"BookName, Category, EmployeeName, DateIssued) " & _
"VALUES(@BookName, @Category, @EmployeeName, @DateIssued)"
.Parameters.AddWithValue("@BookName", Trim(tbBookName.Text))
.Parameters.AddWithValue("@Category", Trim(tbCategory.Text))
.Parameters.AddWithValue("@EmployeeName", Trim(tbEmpName.Text))
.Parameters.AddWithValue("@DateIssued", Trim(tbDate.Text) & " " &
TimeOfDay)
.ExecuteNonQuery()
End With
End Using
End Using
BindGrid_With_Data() ' REFRESH THE GRIDVIEW CONTROL TO SHOW THE NEWLY INSERTED ROW.
End Sub
End Class