Script(s)

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”)

        publishersDataAdapter.Fill(publishersDataTable)

 

        Dim dataTableRowCount As Integer = publishersDataTable.Rows.Count

 

        If dataTableRowCount > 0 Then

            gridViewPublishers.DataSource = publishersDataTable

            gridViewPublishers.DataBind()

        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

        Get

            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

        Get

            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

        gridViewPublishers.DataBind()

    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)

                Else

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

                End If

            End If

            Return pdataView

        Else

            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.DataBind()

        gridViewPublishers.PageIndex = pageIndex

    End Sub

 

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

        PopulatePublishersGridView()

    End Sub

</script>

 

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;

<html xmlns=”http://www.w3.org/1999/xhtml”&gt;

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

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

</head>

<body>

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

        <div>

            <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”

                runat=”server”>

                <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” />

                <Columns>

                    <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” />

                </Columns>

            </asp:GridView>

        </div>

    </form>

</body>

</html>

Advertisements

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

    kyukee

    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.

    gchandra

    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.

    gchandra

    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

    mark

    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.

    Maria

    May 22, 2008 at 4:55 pm

  7. thanks for the cool article..

    fakhre

    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?
    Thanks.

    Gonzalo

    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?
    Thanks

    Gonzalo

    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

      John

      November 10, 2010 at 9:04 am

  10. you, are, the man.

    cerb55

    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)
    {
    UltraWebGrid1.Clear();
    }
    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);

    }
    else
    {
    dtFrom = DateFrom.SelectedDate.Value;
    TimeSpan ts = DateTill.SelectedDate.Value.Subtract(DateFrom.SelectedDate.Value);
    DaysinM=ts.Days+1;
    }
    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;
    UltraWebGrid1.DataBind();
    ValueList valueList;
    valueList = new ValueList();
    foreach (DC_Shift s in shift)
    {
    valueList.DataSource = shift;
    valueList.ValueMember = "ShiftId";
    valueList.DisplayMember = "ShiftCode";
    valueList.DataBind();

    }
    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;
    DataBind();
    }
    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;
    objDT.Rows.Add(objDR);
    objDR = objDT.NewRow();
    }
    Session["DT"] = objDT;
    UltraWebGrid1.DataSource = objDT;
    UltraWebGrid1.DataBind();
    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.

    Prem

    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.
    tks

    ching

    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

    Todd

    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

    sree

    November 14, 2011 at 4:07 pm


Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: