what I learn is what u c

GridView Sorting/Paging w/o a DataSourceControl DataSource (VB.NET)

with 20 comments

This page is reproduced from this URL. Copied here for easy reference. All credits to the original author.

If you set AllowPaging=”true” or AllowSorting=”true” on a GridView control without using a DataSourceControl DataSource (i.e. SqlDataSource, ObjectDataSource), you will run into the following errors:

When changing the page on the GridView control:

The GridView ‘GridViewID’ fired event PageIndexChanging which wasn’t handled.

When clicking a column name to sort the column on the GridView control:

The GridView ‘GridViewID’ fired event Sorting which wasn’t handled.

As a result of not setting the DataSourceID property of the GridView to a DataSourceControl DataSource, you have to add event handlers for sorting and paging.

<%@ Page Language=”VB” %>


<%@ Import Namespace=”System.Data” %>

<%@ Import Namespace=”System.Data.OleDb” %>


<script runat=”server”>   

    Private Sub PopulatePublishersGridView()

        Dim connectionString As String = AccessConnectionString()

        Dim accessConnection As OleDbConnection = New OleDbConnection(connectionString)


        Dim sqlQuery As String = “SELECT [PubID], [Name], [Company Name], [Address], [City], [State], [Zip], [Telephone], [Fax], [Comments] FROM Publishers ORDER BY [Name] ASC;”


        Dim accessCommand As New OleDbCommand(sqlQuery, accessConnection)


        Dim publishersDataAdapter As New OleDbDataAdapter(accessCommand)

        Dim publishersDataTable As New DataTable(“Publishers”)



        Dim dataTableRowCount As Integer = publishersDataTable.Rows.Count


        If dataTableRowCount > 0 Then

            gridViewPublishers.DataSource = publishersDataTable


        End If

    End Sub


    Private Function AccessConnectionString() As String

        Dim accessDatabasePath As String = Server.MapPath(“~/App_Data/biblio.mdb”)

        Return String.Format(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};”, accessDatabasePath)

    End Function


    Private Property GridViewSortDirection() As String


            Return IIf(ViewState(“SortDirection”) = Nothing, “ASC”, ViewState(“SortDirection”))

        End Get

        Set(ByVal value As String)

            ViewState(“SortDirection”) = value

        End Set

    End Property


    Private Property GridViewSortExpression() As String


            Return IIf(ViewState(“SortExpression”) = Nothing, String.Empty, ViewState(“SortExpression”))

        End Get

        Set(ByVal value As String)

            ViewState(“SortExpression”) = value

        End Set

    End Property


    Private Function GetSortDirection() As String

        Select Case GridViewSortDirection

            Case “ASC”

                GridViewSortDirection = “DESC”


            Case “DESC”

                GridViewSortDirection = “ASC”

        End Select


        Return GridViewSortDirection

    End Function


    Protected Sub gridViewPublishers_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

        gridViewPublishers.DataSource = SortDataTable(CType(gridViewPublishers.DataSource,DataTable), True)  gridViewPublishers.PageIndex = e.NewPageIndex


    End Sub


    Protected Function SortDataTable(ByVal pdataTable As DataTable, ByVal isPageIndexChanging As Boolean) As DataView

        If Not pdataTable Is Nothing Then

            Dim pdataView As New DataView(pdataTable)

            If GridViewSortExpression <> String.Empty Then

                If isPageIndexChanging Then

                    pdataView.Sort = String.Format(“{0} {1}”, GridViewSortExpression, GridViewSortDirection)


                    pdataView.Sort = String.Format(“{0} {1}”, GridViewSortExpression, GetSortDirection())

                End If

            End If

            Return pdataView


            Return New DataView()

        End If

    End Function


    Protected Sub gridViewPublishers_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)

        GridViewSortExpression = e.SortExpression

        Dim pageIndex As Integer = gridViewPublishers.PageIndex

        gridViewPublishers.DataSource =  SortDataTable(CType(gridViewPublishers.DataSource,DataTable), False)


        gridViewPublishers.PageIndex = pageIndex

    End Sub


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)


    End Sub



<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN””&gt;

<html xmlns=””&gt;

<head id=”Head1″ runat=”server”>

    <title>GridView Sorting/Paging without a DataSourceControl DataSource</title>



    <form id=”form” runat=”server”>


            <asp:GridView ID=”gridViewPublishers” AllowPaging=”true” AllowSorting=”true” AutoGenerateColumns=”false”

                EmptyDataText=”No records found” PagerSettings-Mode=”NumericFirstLast” PageSize=”25″

                OnPageIndexChanging=”gridViewPublishers_PageIndexChanging” OnSorting=”gridViewPublishers_Sorting”


                <AlternatingRowStyle BackColor=”LightGray” />

                <HeaderStyle BackColor=”Gray” Font-Bold=”true” Font-Names=”Verdana” Font-Size=”Small” />

                <PagerStyle BackColor=”DarkGray” Font-Names=”Verdana” Font-Size=”Small” />

                <RowStyle Font-Names=”Verdana” Font-Size=”Small” />


                    <asp:BoundField DataField=”PubID” HeaderText=”Publisher ID” SortExpression=”PubID” />

                    <asp:BoundField DataField=”Name” HeaderText=”Name” SortExpression=”Name” />

                    <asp:BoundField DataField=”Company Name” HeaderText=”Company Name” SortExpression=”Company Name” />

                    <asp:BoundField DataField=”Address” HeaderText=”Address” SortExpression=”Address” />

                    <asp:BoundField DataField=”City” HeaderText=”City” SortExpression=”City” />

                    <asp:BoundField DataField=”State” HeaderText=”State” SortExpression=”State” />

                    <asp:BoundField DataField=”Zip” HeaderText=”Zip” SortExpression=”Zip” />

                    <asp:BoundField DataField=”Telephone” HeaderText=”Telephone” SortExpression=”Telephone” />

                    <asp:BoundField DataField=”Fax” HeaderText=”Fax” SortExpression=”Fax” />

                    <asp:BoundField DataField=”Comments” HeaderText=”Comments” SortExpression=”Comments” />








Written by gchandra

September 25, 2007 at 2:02 pm

Posted in Web Coding

20 Responses

Subscribe to comments with RSS.

  1. hi,

    i tried the sort algo, but i get an error.

    Unable to cast object of type ‘System.Data.DataView’ to type ‘System.Data.DataTable’.

    when i call the sortDataTable(…)

    i noticed that this method is called twice and the error occurs during the second pass.

    pls. help. tnx


    October 3, 2007 at 8:03 am

  2. Try calling SortDataTable this way

    Its called twice, once during Sorting and second time during page index change.

    SortDataTable(CType(gridViewPublishers.DataSource,DataTable), True)

    SortDataTable(CType(gridViewPublishers.DataSource,DataTable), False)

    I have also modified the parameters for SortDataTable declaration. Let me know if you still have problem.


    October 3, 2007 at 8:57 am

  3. I was having the same problem Kyukee was having.

    I used the ToTable method to ensure that the DataSource was always a table.

    The line of code I modified was:
    grdContacts.DataSource = SortDataTable(CType(grdContacts.DataSource, DataTable), False).ToTable

    Hope that helps you. Thanks for converting this code to VB!

    Tom Oakes

    December 16, 2007 at 9:07 pm

  4. Thanks Tom

    I wish many get benefited with this change.


    December 16, 2007 at 11:30 pm

  5. hi,body. This is the best code i met before. but I met a question:
    after I copy the code, modify and running it, no error occur. But when I click the sorting button, the gridview sort once and remain the same when i click sorting button more than one time. Can you help


    March 26, 2008 at 3:15 am

  6. This code runs in my application but after I click on any sortable header, no change is affected. I’ve toyed around with the call to SortData (after getting all of the same errors as already noted) and incorporated suggested changes, all to no avail. What am i missing? Please advise.


    May 22, 2008 at 4:55 pm

  7. thanks for the cool article..


    June 16, 2008 at 9:07 am

  8. The first thanks for your articule, I believe that I am near to solve my problem but even doesn’t work.
    I have copy your sample but when I make click to headertext the gridview return me a gridview “empty”.
    Can you help me please?


    October 6, 2008 at 3:02 am

  9. Ok now I get have data and works! but only if I have one page, if I have 2 pages doesn’t work corretly.
    The order lost and I need to click again and again for get the correct order.
    For sample if i need a order to z…a, I open page and I see k…l…a..z(not order or oder by other field) then I need make click and I get a….z then click again and z…a ok perfect, but I now change the page and I need make again all, click and click because gridview lost the original order.
    Is possible to save the order for second pages?


    October 6, 2008 at 4:59 am

    • Sir,
      Great, I got the sorting working and thank you but I can’t get the paging numbers or anything to do with paging working. I am using a SQL 2008 db with VS2010 and Framework 4.0.0
      Now for the hard work, what I really want to do is use a stored procedure to select certain records and be able to page through them. I am not fussed about the sorting, that is just a bit of icing for me.
      Many thanks


      November 10, 2010 at 9:04 am

  10. you, are, the man.


    July 7, 2009 at 6:35 pm

  11. please help me its urgent
    How to Add freez pan in the following infragistic Code

    private void PopulateGrid(string Cadre, string Loc)
    using (TAPServiceClient proxy = new TAPServiceClient())
    DC_Shift[] shift = proxy.GetShiftBySeg(true);
    DC_Emp[] emp = proxy.GetEmps(false, false, string.Empty, string.Empty, string.Empty, string.Empty, Cadre, false, string.Empty, string.Empty, 0, string.Empty, string.Empty, “FirstName”, Loc, false, false, string.Empty, string.Empty, true, string.Empty);
    DataTable objDT = new DataTable(”Emp”);
    objDT.Columns.Add(”EmpId”, typeof(int));
    objDT.Columns.Add(”EmpCode”, typeof(string));
    objDT.Columns.Add(”EmpName”, typeof(string));
    objDT.Columns.Add(”DeptName”, typeof(string));
    if (UltraWebGrid1 != null)
    int i = 0;
    int j = 0;
    DateTime dtFrom;
    int DaysinM;
    if (DateFrom.IsEmpty == true && DateTill.IsEmpty == true)
    dtFrom = DateTime.Now.FirstDate();
    DaysinM = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);

    dtFrom = DateFrom.SelectedDate.Value;
    TimeSpan ts = DateTill.SelectedDate.Value.Subtract(DateFrom.SelectedDate.Value);
    if (DaysinM >0)
    string day;
    for (j = 0; j < DaysinM; j++)
    DateTime d = dtFrom.AddDays(j);
    day = d.FormatDay();
    objDT.Columns.Add(d.Day + "-" + day);

    UltraWebGrid1.DataSource = objDT;
    ValueList valueList;
    valueList = new ValueList();
    foreach (DC_Shift s in shift)
    valueList.DataSource = shift;
    valueList.ValueMember = "ShiftId";
    valueList.DisplayMember = "ShiftCode";

    for (i = 4; i < DaysinM + 4; i++)
    valueList.DisplayStyle = ValueListDisplayStyle.DisplayText;
    UltraWebGrid1.Columns[i].Type = ColumnType.DropDownList;
    UltraWebGrid1.DisplayLayout.CellClickActionDefault = CellClickAction.Edit;
    UltraWebGrid1.Columns[i].ValueList = valueList;
    Session["DT"] = objDT;


    public void PopulateDataTable(string Cadre, string Loc)
    DataTable objDT = new DataTable();
    objDT = (DataTable)Session["DT"];
    DataRow objDR;
    using (TAPServiceClient proxy = new TAPServiceClient())
    DC_Shift[] shift = proxy.GetShiftBySeg(true);
    DC_Emp[] emp = proxy.GetEmps(true, false, string.Empty, string.Empty, string.Empty, string.Empty, Cadre, false, string.Empty, string.Empty, 0, string.Empty, string.Empty, "FirstName", Loc, false, false, string.Empty, string.Empty, true, string.Empty);

    foreach (DC_Emp a in emp)
    DC_Dept[] Dept = proxy.Get(string.Empty, a.EmpOfficial.DeptId.ToString());
    objDR = objDT.NewRow();
    objDR["EmpId"] = a.EmpId;
    objDR["EmpCode"] = a.EmpCode;
    objDR["EmpName"] = a.FirstName + " " + a.LastName;
    objDR["Deptname"] = Dept[0].DeptName;
    objDR = objDT.NewRow();
    Session["DT"] = objDT;
    UltraWebGrid1.DataSource = objDT;
    UltraWebGrid1.DisplayLayout.Bands[0].Columns[0].Hidden = true;
    UltraWebGrid1.DisplayLayout.Bands[0].Columns[2].Width = 190;
    UltraWebGrid1.DisplayLayout.Bands[0].Columns[1].Width = 80;
    UltraWebGrid1.DisplayLayout.Bands[0].Columns[3].Width = 190;


    Sapna Perchani

    July 14, 2009 at 12:47 am

  12. Thanks a lot. Excellent solution.


    January 20, 2010 at 4:12 pm

  13. Thanks

    It Will Help Me

    Muhammad Danish

    July 9, 2010 at 2:56 am

  14. Excellent solution.

    Muhammad Danish

    July 9, 2010 at 2:57 am

  15. Thanks…
    I use this example and it helps me to resolve the manual data source sorting and paging issue.


    January 19, 2011 at 3:35 pm

  16. Great work. Thanks for sharing!

    Ryan Reif

    January 24, 2011 at 1:15 pm

  17. Well, I’m getting the same error as some have even with the .ToTable setting being used.

    Unable to cast object of type ‘System.Data.DataSet’ to type ‘System.Data.DataTable’.

    GridView1.DataSource = SortDataTable(CType(GridView1.DataSource, DataTable), True).ToTable

    GridView1.DataSource = SortDataTable(CType(GridView1.DataSource, DataTable), False).ToTable


    March 24, 2011 at 2:23 pm

  18. This works perfectly. Thanks a lot!!

    Syreeta Dendy

    June 14, 2011 at 8:36 am

  19. This should work for “Cannot convert an object from dataset to datatable”

    gridObservations.DataSource = SortDataTable(CType(CType(gridObservations.DataSource, DataSet).Tables(0), DataTable), False).ToTable


    November 14, 2011 at 4:07 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: