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.

show serial number in gridview in asp.net
 
Description: In previous related articles i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and Bind,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:TemplateFieldin 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 = new SqlConnection(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 New SqlConnection(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 As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging
        grdEmp.PageIndex = e.NewPageIndex
        BindEmpGrid()
    End Sub

Now over to you: "If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. Out Of Topic Show Konversi KodeHide Konversi Kode Show EmoticonHide Emoticon

Thanks for your comment