In addition to this, I am going to show you how to add a jQuery Datepicker control in each GridView row, in edit mode. The below images says it all. The image clearly shows the Datepicker control (widget) before the “Insert Record” button.
Insert new records using GridView
Here is a scenario. XYZ company issues books to its employees on weekends and maintains a database on issued books with the names of the Employee, the issuing date and the category of these books. It’s a simple senario and this will help us understand how Asp.Net GridView can be used to insert records, row by row.
First create a table dbo.Books in the database
“I recommend using this SQL Server dummy database with tables and few dummy data in it. It will save some time.”
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>
<%--GRIDVIEW STYLE.--%>
<style>
body { font-size:62.5% }
#divBooks {
font:11px Verdana;
width:750px;
overflow-y:scroll;
display:block;
padding:10px 0 0 0;
}
.textbox {
width:70px;
padding:2px 4px;
font:inherit;
text-align:left;
border:solid 1px #BFBFBF;
border-radius:2px; -moz-border-radius:2px; -webkit-border-radius:2px;
text-transform:uppercase;
}
.Grid {
width:100%;
font:inherit;
margin:5px 0 10px 0;
background-color:#FFF;
border:solid 1px #525252;
}
.Grid td {
font:inherit;
padding:2px;
border:solid 1px #C1C1C1;
color:#333;
text-align:center;
}
.Grid th {
padding:3px;
color:#FFF;
background:#424242
border-left:solid 1px #525252;
font:inherit;
}
.button {
cursor:pointer;
text-align:center;
color:#FFF;
font:inherit;
background-color:#4D90FE;
border:solid 1px #3079ED;
border-radius:5px;
-moz-border-radius:5px;
-webkit-border-radius:5px;
line-height:15px;
}
</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 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