Display Serial Number Automatically on GridView

How to display Serial/Row Number automatically in GridView in asp.net

Introduction: In this article I am going to explain with example How to bind Grid view and automatically show row/record number or we can say counting with each row of the GridView data control in asp.net using both C#.Net and VB.Net languages.

 

Description: In previous related articles i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView andBind,upload,download,delete image files from the GridView and Highlight gridview row on mouse over using CSS and WCF Service to bind,insert,edit,update,delete from sql server database in asp.net and Send email to multiple users based on CheckBox selection inside GridView.
Here in this example i have demonstrated two ways to display serial number or we can say record/row numbering in the gridview along with the rows. You can use any of the two that are listed below.

1. First Method: Using  ROW_NUMBER()  e.g. in this article example I used the query  "Select ROW_NUMBER() over (order by Emp_Id) as IndexNo,* from Emp_Tb" and in the source code of gridview adding a Bound filed as <asp:BoundField HeaderText="IndexNo" DataField="IndexNo" />
2. Second Method: This is the simplest way. Just add <asp:TemplateField HeaderText="IndexNo.">

 <ItemTemplate>

    <%# Container.DataItemIndex + 1 %>

  </ItemTemplate>

</asp:TemplateField> in the gridview

Implementation: Let's understand by creating an asp.net application

• First of all create a database in Sql server and name it “Emp_DB” and create a table in this database with the column and data type as shown below and name it “Emp_Tb” 

Column Name	Data Type
Emp_Id	Int(primary key. So set Is Identity=true)
EmpName	varchar(100)
City	varchar(100)
Salary	int

• In the web.config file create the connection string in the <configuration> tag to connect our asp.net web application with the Sql server database as:

<connectionStrings>

    <add name="conStr" connectionString="Data Source=lalit;Initial Catalog=Emp_DB;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and the Initial Catalog (i.e. Database name) as per your application.

Source Code:

• In the design page (.aspx) place a GridView control from the Data category of the visual studio’s toolbox and set as:

<div>

    <fieldset style="Width:335px;">

    <legend>Row number example in gridview </legend>

    <asp:GridView ID="grdEmp" runat="server" AutoGenerateColumns="False"

            AllowPaging="True" onpageindexchanging="grdEmp_PageIndexChanging"

            PageSize="5" >

        <Columns>

        <%--<asp:TemplateField HeaderText="IndexNo.">

 <ItemTemplate>

    <%# Container.DataItemIndex + 1 %>

  </ItemTemplate>

</asp:TemplateField>--%>

      <asp:BoundField HeaderText="IndexNo" DataField="IndexNo" />

        <asp:BoundField HeaderText="Emp_Id" DataField="Emp_Id" />

        <asp:BoundField HeaderText="EmpName" DataField="EmpName" />

        <asp:BoundField HeaderText="City" DataField="City" />

        <asp:BoundField HeaderText="Salary" DataField="Salary" />

        </Columns>

        </asp:GridView>

    </fieldset>       

    </div>

C#.Net Code:

• In the code behind file (.aspx.cs) write the code as:

First include following namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

Then write the code as:

   protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            BindEmpGrid();

        }

    }

    private void BindEmpGrid()

    {

        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

        DataTable dt = new DataTable();

        SqlDataAdapter adp = new SqlDataAdapter();

        SqlCommand cmd = new SqlCommand();

        try

        {

            cmd = new SqlCommand("Select ROW_NUMBER() over (order by Emp_Id) as IndexNo,* from Emp_Tb", con);

           // cmd = new SqlCommand("Select * from Emp_Tb", con);

            adp.SelectCommand = cmd;

            adp.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                grdEmp.DataSource = dt;

                grdEmp.DataBind();

            }       

        }

        catch (Exception ex)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);

        }

        finally

        {

            cmd.Dispose();

            dt.Clear();

            dt.Dispose();

            adp.Dispose();

            con.Close();

        }

    }

//GridView paging

    protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        grdEmp.PageIndex = e.NewPageIndex;

        BindEmpGrid();

    }

Note: The example above demonstrates the first way. You can also use the second method. To do so, In the source code of gridview you just need to comment or replace the bound field line <asp:BoundField HeaderText="IndexNo" DataField="IndexNo" />  and uncomment the following template field line

  <asp:TemplateField HeaderText="IndexNo.">

 <ItemTemplate>

    <%# Container.DataItemIndex + 1 %>

  </ItemTemplate>

</asp:TemplateField>

And also in the code behind file comment or replace the query "Select ROW_NUMBER() over (order by Emp_Id) as IndexNo,* from Emp_Tb" and uncomment the query  "Select * from Emp_Tb"

VB.Net Code

• In the code behind file (.aspx.vb) write the following code:

First of all import the following namespaces.

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Then write the code as:

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            BindEmpGrid()

        End If

    End Sub

    Private Sub BindEmpGrid()

        Dim con As NewSqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

        Dim dt As New DataTable()

        Dim adp As New SqlDataAdapter()

        Dim cmd As New SqlCommand()

        Try

            cmd = New SqlCommand("Select ROW_NUMBER() over (order by Emp_Id) as IndexNo,* from Emp_Tb", con)

            ' cmd = new SqlCommand("Select * from Emp_Tb", con);

            adp.SelectCommand = cmd

            adp.Fill(dt)

            If dt.Rows.Count > 0 Then

                grdEmp.DataSource = dt

                grdEmp.DataBind()

            End If

        Catch ex As Exception

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)

        Finally

            cmd.Dispose()

            dt.Clear()

            dt.Dispose()

            adp.Dispose()

            con.Close()

        End Try

    End Sub

'gridView paging

    Protected Sub grdEmp_PageIndexChanging(sender As Object, e AsSystem.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging

        grdEmp.PageIndex = e.NewPageIndex

        BindEmpGrid()

    End Sub