Format DateTime column (field) in ASP.Net GridView with AutoGenerateColumns True

Database

Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.

Download and install Northwind Database

HTML Markup

The following HTML Markup consists of an ASP.Net GridView with AutoGenerateColumns property with set to True and specified with OnRowDataBound event.

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="OnRowDataBound">

</asp:GridView>

Namespaces

You will need to import the 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 Repeater control using records form database

Inside the Page load event handler, the Repeater is populated with the records from the Customers table of the Northwind database.

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!this.IsPostBack)

    {

        this.BindGrid();

    }

}

private void BindGrid()

{

    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr))

    {

        string query = "SELECT EmployeeId, (FirstName + '' + LastName) Name, BirthDate FROM Employees";

        using (SqlCommand cmd = new SqlCommand(query))

        {

            using (SqlDataAdapter sda = new SqlDataAdapter())

            {

                cmd.Connection = con;

                sda.SelectCommand = cmd;

                using (DataTable dt = new DataTable())

                {

                    sda.Fill(dt);

                    GridView1.DataSource = dt;

                    GridView1.DataBind();

                }

            }

        }

    }

}

VB.Net

Protected Sub Page_Load(sender As Object, 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

    Using con As New SqlConnection(constr)

        Dim query As String = "SELECT EmployeeId, (FirstName + '' + LastName) Name, BirthDate FROM Employees"

        Using cmd As New SqlCommand(query)

            Using sda As New SqlDataAdapter()

                cmd.Connection = con

                sda.SelectCommand = cmd

                Using dt As New DataTable()

                    sda.Fill(dt)

                    GridView1.DataSource = dt

                    GridView1.DataBind()

                End Using

            End Using

        End Using

    End Using

End Sub

Formatting DateTime column (field) in ASP.Net GridView with AutoGenerateColumns True

Inside the OnRowDataBound event handler, the value of the DateTime field is formatted to the desired DateTime format.

C#

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)

{

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

        e.Row.Cells[2].Text = Convert.ToDateTime(e.Row.Cells[2].Text).ToString("dd, MMM yyyy");

    }

}

VB.Net

Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)

    If e.Row.RowType = DataControlRowType.DataRow Then

        e.Row.Cells(2).Text = Convert.ToDateTime(e.Row.Cells(2).Text).ToString("dd, MMM yyyy")

    End If

End Sub

Implement jQuery AutoComplete TextBox from database using AJAX PageMethods in ASP.Net

<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js" type="text/javascript"></script>

<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>

<link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css"

    rel="Stylesheet" type="text/css" />

<script type="text/javascript">

    $(function () {

        $("[id$=txtSearch]").autocomplete({

            source: function (request, response) {

                $.ajax({

                    url: '<%=ResolveUrl("~/Default.aspx/GetCustomers") %>',

                    data: "{ 'prefix': '" + request.term + "'}",

                    dataType: "json",

                    type: "POST",

                    contentType: "application/json; charset=utf-8",

                    success: function (data) {

                        response($.map(data.d, function (item) {

                            return {

                                label: item.split('-')[0],

                                val: item.split('-')[1]

                            }

                        }))

                    },

                    error: function (response) {

                        alert(response.responseText);

                    },

                    failure: function (response) {

                        alert(response.responseText);

                    }

                });

            },

            select: function (e, i) {

                $("[id$=hfCustomerId]").val(i.item.val);

            },

            minLength: 1

        });

    });  

</script>

Enter search term:

<asp:TextBox ID="txtSearch" runat="server" />

<asp:HiddenField ID="hfCustomerId" runat="server" />

<asp:Button ID="Button1" Text="Submit" runat="server" OnClick="Submit" />

 

 

Namespaces

You will need to import the following namespaces.

C#

using System.Web.Services;

using System.Configuration;

using System.Data.SqlClient;

 

VB.Net

Imports System.Web.Services

Imports System.Configuration

Imports System.Data.SqlClient

 

 

 The ASP.Net PageMethod

The following AJAX PageMethod accepts a parameter prefix and its value is used to find matching records from the Customers Table of the Northwind database.

The select query gets the Name and the ID of the customer that matches the prefix text.

The fetched records are processed and a Key Value Pair is created by appending the Id to the Name field in the following format {0}-{1} where is the Name {0} and {1} is the ID of the Customer.

 C#

[WebMethod]

public static string[] GetCustomers(string prefix)

{

    List<string> customers = new List<string>();

    using (SqlConnection conn = new SqlConnection())

    {

        conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        using (SqlCommand cmd = new SqlCommand())

        {

            cmd.CommandText = "select ContactName, CustomerId from Customers where ContactName like @SearchText + '%'";

            cmd.Parameters.AddWithValue("@SearchText", prefix);

            cmd.Connection = conn;

            conn.Open();

            using (SqlDataReader sdr = cmd.ExecuteReader())

            {

                while (sdr.Read())

                {

                    customers.Add(string.Format("{0}-{1}", sdr["ContactName"], sdr["CustomerId"]));

                }

            }

            conn.Close();

        }

    }

    return customers.ToArray();

}

 

VB.Net

<WebMethod()>

Public Shared Function GetCustomers(prefix As String) As String()

    Dim customers As New List(Of String)()

    Using conn As New SqlConnection()

        conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString

        Using cmd As New SqlCommand()

            cmd.CommandText = "select ContactName, CustomerId from Customers where ContactName like @SearchText + '%'"

            cmd.Parameters.AddWithValue("@SearchText", prefix)

            cmd.Connection = conn

            conn.Open()

            Using sdr As SqlDataReader = cmd.ExecuteReader()

                While sdr.Read()

                    customers.Add(String.Format("{0}-{1}", sdr("ContactName"), sdr("CustomerId")))

                End While

            End Using

            conn.Close()

        End Using

    End Using

    Return customers.ToArray()

End Function

 

 

Fetching the selected item on Server Side

The Key (Customer Name) and Value (Customer ID) can be fetched on server side inside the click event handler of the Button from the Request.Form collection as shown below.

 C#

protected void Submit(object sender, EventArgs e)

{

    string customerName = Request.Form[txtSearch.UniqueID];

    string customerId = Request.Form[hfCustomerId.UniqueID];

    ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Name: " + customerName + "\\nID: " + customerId +"');", true);

}

 

VB.Net

Protected Sub Submit(sender As Object, e As EventArgs)

    Dim customerName As String = Request.Form(txtSearch.UniqueID)

    Dim customerId As String = Request.Form(hfCustomerId.UniqueID)

    ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Name: " & customerName & "\nID: " & customerId &"');", True)

End Sub