Add jQuery Datepicker Control to a GridView Row and Save the Date to an SQL Server Table

Add jQuery Datepicker Control to a GridView Row and Save the Date to an SQL Server Table

If you are using a GridView control in your Asp.Net application for doing CRUD operations and you want to add a Datepicker to the GridView, then this article has the solution. I am sharing an example here, which shows how to add a jQuery Datepicker control to a GridView row and save the date, with other data, to a database table using C# and Vb.Net code.

You can easily bind a jQuery Datepicker control to an Asp.Net textbox control or any form input element. We use a textbox control to make our GridView row editable. Therefore, we can bind the Datepicker to the textbox in the GridView to select a date.

The Markup

First, add the plug-in’s CDN inside the <head> tag of your web page. The .js and .css files provide the necessary methods and themes to the Datepicker control.

<link href="https://code.jquery.com/ui/1.12.0/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js"></script>
Add the GridView

Now, add a GridView control to your web page, with few columns and a button at the last column. I want to save the date and other data to a database table. The button's click event will call a code behind procedure to save the data. This feature is optional.

<asp:GridView ID="GridView1" 
    runat="server" 
    AutoGenerateColumns="False" 
    GridLines="None" ShowFooter="True">
                    
    <Columns>
        <asp:TemplateField HeaderText="ID">
            <ItemTemplate>
                <asp:Label ID="lblEmpID" runat="server" Text='<%#Eval("EmpID")%>'>
            </asp:Label></ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Employee">
            <ItemTemplate> <%#Eval("EmployeeName")%> </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbEmpName" Width="200px" runat="server" />
            </FooterTemplate>
        </asp:TemplateField>

        <%-- I WANT TO SHOW THE Datepicker HERE. --%>
        <asp:TemplateField HeaderText="Date of Joining">
            <ItemTemplate><asp:Label ID="lblDOJ" width="140px" runat="server" 
                Text='<%#Eval("DOJ", "{0:dd/MM/yyyy}")%>'></asp:Label></ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbDOJ" 
                    DataFormatString="{dd/MM/yyyy}"
                    runat="server">
                </asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        
        <%-- BUTTON TO ADD GRID'S DATA TO THE DATABASE TABLE. --%>            
        <asp:TemplateField>
            <ItemTemplate></ItemTemplate>
            <FooterTemplate>
                <asp:Button ID="btInsert" runat="server" Text="Add"
                    OnClick="addRow" CommandName="Footer" />
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>

    <%-- SHOWS BLANK FILEDS AT THE BOTTOM OF THE GRIDVIEW. --%>
    <EmptyDataTemplate>  
    <tr>
        <th>Employee Name</th>
        <th>Date of Joining</th>
        <th></th>
    </tr>
    <tr>
        <td><asp:TextBox ID="tbEmpName" Width="200px" runat="server" /></td>
        <td>
            <%-- SHOW THE Datepicker. --%>
            <asp:TextBox ID="tbDOJ" DataFormatString="{dd/MM/yyyy}" runat="server" />
        </td>
        <td>
            <asp:Button ID="btAdd" Text="Add" 
                OnClick="addRow" CommandName="EmptyDataTemplate" 
                runat="server" />
        </td>
    </tr>
    </EmptyDataTemplate>

</asp:GridView>

The second column (header) is Date of Joining and I want a Datepicker to popup when the user sets focus in the textbox (in edit mode). The textbox, inside the <FooterTemplate> (of the second column) has an id tbDOJ, which I’ll need to bind with the jQuery Datepicker widget.

I am also using the <EmptyDataTemplate> tag in the GridView. The empty data row is displayed when the data source that is bound to the GridView does not contain any record. Therefore, I have a textbox in the second column (for date) with same id as tbDOJ.

The Script to Bind Datepicker

The script to bind the textbox with the Datepicker is very simple.

<script>
    $(document).ready(function () {
        $('input[id*=tbDOJ]').datepicker({
            dateFormat: 'dd/mm/yy'
        });
    });
</script>

I have tied the textbox control (tbDOJ) to the jQuery Daterpicker widget and used a predefined option dateFormat to show the date to a specified format. The options are case sensitive.

Binding jQuery DatePicker Control to an Asp.Net GridView Control

That’s it. We have added a Datepicker to our GridView control. This only requires few lines of code.

Now, let’s finish it with a small code behind procedure to save all the GridView data.

Create a Table in SQL Server

CREATE TABLE dbo.Employees
(
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DOJ DATETIME NULL
)
Code Behind Procedure (C#)

This code behind procedure shows you how to populate the GridView with data extracted from a database table and save new data (from the GridView) to the database table.

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class SiteMaster : System.Web.UI.MasterPage
{
    // CONNECTION STRING.
    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)
        {
            PopulateGridView();
        }
    }
    private void PopulateGridView()
    {
        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Employees"))
            {
                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)
                { 
                    //
                }
            }
        }
    }

    // PROCEDURE TO SAVE DATA IN SQL SERVER.
    protected void addRow(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 tbEmpName = (TextBox)grdRow.Cells[0].FindControl("tbEmpName");
        TextBox tbDOJ = (TextBox)grdRow.Cells[0].FindControl("tbDOJ");

        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Employees"))
            {
                // FINALLY INSERT ROW VALUES IN THE TABLE.
                cmd.Connection = con;
                con.Open();

                cmd.CommandText = "INSERT INTO dbo.Employees (EmployeeName, DOJ) " +
                    "VALUES(@EmployeeName, @DOJ)";
                cmd.Parameters.AddWithValue("@EmployeeName", tbEmpName.Text.Trim());
                cmd.Parameters.AddWithValue("@DOJ", DateTime.Parse(tbDOJ.Text.Trim() + " " +
                    DateTime.Now.TimeOfDay));

                cmd.ExecuteNonQuery();
            }
        }

        // REFRESH THE GRIDVIEW CONTROL TO SHOW THE NEWLY INSERTED ROW.
        PopulateGridView();
    }
}
Code Behind (Vb.Net)
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Dim sCon As String = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" & _
        "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            PopulateGridView()
        End If
    End Sub

    Private Sub PopulateGridView()
        Using con As SqlConnection = New SqlConnection(sCon)
            Dim sSQL = "SELECT *FROM dbo.Employees"
            Using cmd As SqlCommand = New SqlCommand(sSQL)

                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

    ' PROCEDURE TO SAVE DATA IN SQL SERVER.
    Protected Sub addRow(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 tbEmpName As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbEmpName"), TextBox)
        Dim tbDOJ As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbDOJ"), TextBox)

        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Employees")

                ' FINALLY INSERT ROW VALUES IN THE TABLE.
                With cmd
                    .Connection = con : con.Open()

                    .CommandText = "INSERT INTO dbo.Employees (EmployeeName, DOJ) " & _
                        "VALUES(@EmployeeName, @DOJ)"
                    .Parameters.AddWithValue("@EmployeeName", UCase(Trim(tbEmpName.Text)))
                    .Parameters.AddWithValue("@DOJ", Trim(tbDOJ.Text) & " " &
                        TimeOfDay)

                    .ExecuteNonQuery()
                End With
            End Using
        End Using

        PopulateGridView()   ' REFRESH THE GRIDVIEW CONTROL TO SHOW THE NEWLY INSERTED ROW.
    End Sub
End Class

Well, this is how it works. You now know how to add a jQuery Datepicker control to a GridView row. The code behind procedure shows how you can save the data, along with the selected date, into an SQL Server table and later retrieve the data from table and bind it to the GridView.

Thanks for reading.

Previous - Example Showing How to bind jQuery DatePicker Control to Asp.Net Textbox Control



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!