locked
Sorting & Paging Gridview Bound to Dataset RRS feed

  • Question

  • User-72198832 posted

    I have XML Database that I want to display in Gridview and allow sorting and paging.

    If I use XMLDataSource I can do paging but not sorting,

    I would like to try using DataSet bound to Gridview but don't know how to do Sorting or Paging.

    Can someone show me how to accomplish this?

    Here's my code to create dataset from xml and bind dataset to DataGridView.

                Dim xmlfile As String = Server.MapPath("~\data\budchart.xml")
                Dim Dataset As New DataSet
                Dataset.ReadXml(xmlfile, XmlReadMode.InferSchema)
                DataGridView.DataSource = Dataset
                DataGridView.DataBind()
    

    Hope you can help.

    Lee

    Thursday, June 21, 2018 6:27 PM

Answers

  • User-330142929 posted

    Hi Trims30,

    Really envy your lifestyle, according to your description,

    Had to add a few more lines of code to force Sort Ascending on Column Header Click when changing sort Column.  Also had to force Page Zero to sort new column from beginning.

    Because of my poor English, I may misunderstand what you mean. I have modified the code.

    Code Behind.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                ViewState("sortdirection") = "ASC"
                Session("LastSort") = "Description ASC"
                Session("CurrentIndex") = "Description"
                ReSort()
            End If
    End Sub
        Protected Sub DataGridView_Sorting(sender As Object, e As GridViewSortEventArgs) Handles DataGridView.Sorting
            If GetDataSet() IsNot Nothing Then
                Dim dv As DataView = New DataView(GetDataSet().Tables(0))
                If Session("CurrentIndex") <> e.SortExpression Then
                    DataGridView.PageIndex = 0
                    ViewState("sortdirection") = "ASC"
                    Session("CurrentIndex") = e.SortExpression
                Else
                    If ViewState("sortdirection").ToString() = "ASC" Then
                        ViewState("sortdirection") = "DESC"
                    Else
                        ViewState("sortdirection") = "ASC"
                    End If
                End If
                Session("LastSort") = e.SortExpression + " " + ViewState("sortdirection")
                ReSort()
            End If
        End Sub

    Feel free to let me know if you have any question.

    Best Regards,

    Abraham.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 28, 2018 3:33 AM

All replies

  • User-330142929 posted

    Hi Trims30,

    According to your description,

    I would like to try using DataSet bound to Gridview but don't know how to do Sorting or Paging.

    Can someone show me how to accomplish this?

    You want to bind the dataset to the gridview and implement sorting and paging.

    Here I have made a demo, and wish it is useful to you.

    Aspx.

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="True" AllowSorting="True" PageSize="5" OnSorting="GridView1_Sorting" OnPageIndexChanging="GridView1_PageIndexChanging">
                    <Columns>
                        <asp:TemplateField HeaderText="Id" SortExpression="Id">
                            <ItemTemplate>
                                <h4><%#  Eval("Id") %></h4>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Name" SortExpression="Name">
                            <ItemTemplate>
                                <h4><%# Eval("Name") %></h4>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Price" SortExpression="Price">
                            <ItemTemplate>
                                <h4><%# Eval("Price") %></h4>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

    Code behind.

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    GridView1.DataSource = GetDataSet();
                    GridView1.DataBind();
                    ViewState["sortdirection"] = "ASC";
                }
            }
            protected DataSet GetDataSet()
            {
                string path = Server.MapPath("~/App_Data/1.xml");
                DataSet ds = new DataSet();
                ds.ReadXml(path, XmlReadMode.InferSchema);
                return ds;
            }
            protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
            {
                if (GetDataSet()!=null)
                {
                    DataView dv = new DataView(GetDataSet().Tables[0]);
                    if (ViewState["sortdirection"].ToString()=="ASC")
                    {
                        dv.Sort = e.SortExpression + " DESC";
                        ViewState["sortdirection"] = "DESC";
                    }
                    else
                    {
                        dv.Sort = e.SortExpression + " ASC";
                        ViewState["sortdirection"] = "ASC";
     
                    }
                    GridView1.DataSource = dv;
                    GridView1.DataBind();
                }
            }
            protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
            {
                this.GridView1.PageIndex = e.NewPageIndex;
                this.GridView1.DataSource = GetDataSet();
                this.GridView1.DataBind();
            }

    My XML structure.

    <?xml version="1.0" standalone="yes"?>
    <NewDataSet>
      <Table>
        <Id>1002</Id>
        <Name>Peach</Name>
        <Price>18</Price>
      </Table>
      <Table>
        <Id>1003</Id>
        <Name>Banana</Name>
        <Price>16</Price>
      </Table>
      <Table>
        <Id>1005</Id>
        <Name>Cherry</Name>
        <Price>21</Price>
      </Table>
    </NewDataSet>

    How it works.

    Please feel free to let me know, if you have any question.

    Best Regards

    Abraham.

    Friday, June 22, 2018 10:14 AM
  • User-72198832 posted

    Abraham:

    Thank you - got that working after deleting the following: VB.Net said those were not part of Gridview1

    OnSorting="GridView1_Sorting" OnPageIndexChanging="GridView1_PageIndexChanging"

    Now, I've added Edit button that fires SelectedIndexChanged event.

    Am unable to get cell values using the following:

    Protected Sub DataGridView_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DataGridView.SelectedIndexChanged

    txtAccount.Text = DataGridView.SelectedRow.Cells(1).Text
    txtSequence.Text = DataGridView.SelectedRow.Cells(2).Text

    End Sub

    What do I need to do to get cell values into a text box?

    Lee

    Friday, June 22, 2018 3:20 PM
  • User-330142929 posted

    Hi Trims30,

    According to your description, I copied your code and found that the edit button could not fire the GridView1_SelectedIndexChanged event.

    When I set AutoGenerateSelectButton="True" and Click the selection button, It could get the value sucessfully by your codes.

    Please Post your error code and make me understand your needs, It is easily to give you a solution if you could post more details about your code.

    Please feel free to let me know if you have any question.

    Best Regards,

    Abraham

    Friday, June 22, 2018 4:10 PM
  • User-72198832 posted

    Abraham:

    All is working - am able to SORT and PAGE.

    I do have another issue - I added some records to my XML file (See last 3 records) .

    With 15 row page size and initial sort (Page Load) by Description, all appears OK - the three records are in proper sequence.

    See Initial Display

    Clicking on Next Page button causes remaining records to be displayed in alpha sequence by Description but also includes the last 3 physical records from XML.

    See Next Page

    Clicking Previous Page button displays 15 records in Description Sequence but those three records are not shown.

    See Previous Page

    Why is Paging putting these records at end of list?  Appears that Paging is not Sorting.

    Lee

    Here's my XML

    <?xml version="1.0"?>
    <Bud_List>
    <Budget Account="3030-00" Sequence="3030" Dept="00" Description="BUILDING MAINTENANCE" Type="2" MS=" " EjGr="G"/>
    <Budget Account="2200-00" Sequence="3030" Dept="00" Description="COMMUNICATIONS" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1140-00" Sequence="3030" Dept="00" Description="DOUBLETIME PAY" Type="2" MS=" " EjGr=" "/>
    <Budget Account="3010-00" Sequence="3030" Dept="00" Description="EQUIPMENT REPAIRS" Type="2" MS="M" EjGr=" "/>
    <Budget Account="4040-00" Sequence="3030" Dept="00" Description="FERTILIZERS &amp; NUTRIENTS" Type="2" MS="S" EjGr=" "/>
    <Budget Account="2400-00" Sequence="3030" Dept="00"  Description="FREIGHT &amp; POSTAGE" Type="2" MS=" " EjGr=" "/>
    <Budget Account="4010-00" Sequence="3030" Dept="00"  Description="FUNGICIDES" Type="2" MS=" " EjGr=" "/>
    <Budget Account="3020-00" Sequence="3030" Dept="00"  Description="GROUNDS MAINTENANCE" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1300-00" Sequence="3030" Dept="00"  Description="GROUP HEALTH INSURANCE" Type="2" MS=" " EjGr=" "/>
    <Budget Account="4020-00" Sequence="3030" Dept="00"  Description="HERBICIDES" Type="2" MS=" " EjGr=" "/>
    <Budget Account="5060-00" Sequence="3030" Dept="00"  Description="IRRIGATION WATER" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1150-00" Sequence="3030" Dept="00"  Description="OTHER PAY" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1130-00" Sequence="3030" Dept="00"  Description="OVERTIME PAY" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1200-00" Sequence="3030" Dept="00"  Description="PAYROLL TAXES" Type="2" MS=" " EjGr=" "/>
    <Budget Account="4030-00" Sequence="3030" Dept="00"  Description="PESTICIDES" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1120-00" Sequence="3030" Dept="00"  Description="REGULAR PAY" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1110-00" Sequence="3030" Dept="00"  Description="SALARIES" Type="2" MS=" " EjGr=" "/>
    <Budget Account="2100-00" Sequence="3030" Dept="00"  Description="SECURITY SERVICE" Type="2" MS=" " EjGr=" "/>
    <Budget Account="2500-00" Sequence="3030" Dept="00"  Description="STATE SALES TAX" Type="2" MS=" " EjGr=" "/>
    <Budget Account="5010-00" Sequence="3030" Dept="00"  Description="SYSTEM PARTS" Type="2" MS=" " EjGr=" "/>
    <Budget Account="2300-00" Sequence="3030" Dept="00"  Description="UTILITIES" Type="2" MS=" " EjGr=" "/>
    <Budget Account="5050-00" Sequence="3030" Dept="00"  Description="WATER TREATMENT" Type="2" MS=" " EjGr=" "/>
    <Budget Account="5030-00" Sequence="3030" Dept="00"  Description="WELL &amp; PUMP REPAIR" Type="2" MS=" " EjGr=" "/>
    <Budget Account="1400-00" Sequence="3030" Dept="00"  Description="WORKMANS COMP INSURANCE" Type="2" MS=" " EjGr=" "/>
    <Budget Account="4122A" Sequence="224122" Dept="20" Description="New 3122a" Type="2" MS="M" EjGr=" "/>
    <Budget Account="1470-0A" Sequence="3100" Dept="30" Description="new 3A470a" Type="2" MS="M" EjGr=" "/>
    <Budget Account="1470-01" Sequence="3201" Dept="40" Description="nw 1470-01" Type="2" MS=" " EjGr=" "/>
    </Bud_List>
    
    

    Here's the HTML

              <asp:GridView ID="DataGridView"  runat="server" 
                    AutoGenerateColumns="false" 
                    AllowPaging="True" 
                    AllowSorting="True" PageSize="15" 
                     Width="100%" Font-Names="Arial" Font-Size="Large">
                    <AlternatingRowStyle BackColor="#FFCC99" />
                    <Columns>
                        <asp:CommandField  ButtonType="Button" SelectText="Edit"  ShowSelectButton="True" 
                        ItemStyle-HorizontalAlign="Center" ItemStyle-Width="65px"  />
    
                        <asp:BoundField DataField="Account" HeaderText="Account" SortExpression="Account" />
                        <asp:BoundField DataField="Sequence" HeaderText="Sequence" SortExpression="Sequence" />
                        <asp:BoundField DataField="Dept" HeaderText="Dept" SortExpression="Dept" />
                        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
                        <asp:BoundField DataField="Type" HeaderText="Type" />
                        <asp:BoundField DataField="MS" HeaderText="MS" />
                        <asp:BoundField DataField="EjGr" HeaderText="EjGr" />
                    </Columns>
    
                    <HeaderStyle BackColor="Silver" Font-Names="Arial" Font-Size="Smaller" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#0099FF" Font-Names="Arial" />
                    <SortedAscendingHeaderStyle BackColor="Aqua" />
                    <SortedDescendingHeaderStyle BackColor="#FFFF66" />
                    <PagerSettings Mode="NextPreviousFirstLast" Position="top" 
                        FirstPageImageUrl="images\btnfirst.jpg" LastPageImageUrl="images\btnlast.jpg" 
                        NextPageImageUrl="images\btnnext.jpg" PreviousPageImageUrl="images\btnprevious.jpg" />
                    <PagerStyle HorizontalAlign="Right" />
                </asp:GridView>
    

    And the VB.Net Code


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
    '----- Set initial sort BY Description Column ----
    Session("LastSort") = "Description ASC"
    DataGridView.DataSource = GetDataSet()
    ReSort()
    ViewState("sortdirection") = "ASC"
    End If
    End Sub

    Private Function GetDataSet() As DataSet
    Dim path As String = Server.MapPath("~\data\budchart.xml")
    Dim ds As DataSet = New DataSet()
    ds.ReadXml(path, XmlReadMode.InferSchema)
    Return ds
    End Function

    Private Sub DataGridView_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles DataGridView.PageIndexChanging
    DataGridView.PageIndex = e.NewPageIndex
    DataGridView.DataSource = GetDataSet()
    DataGridView.DataBind()
    End Sub


    Private Sub DataGridView_Sorting(sender As Object, e As GridViewSortEventArgs) Handles DataGridView.Sorting
    If GetDataSet() IsNot Nothing Then
    Dim dv As DataView = New DataView(GetDataSet().Tables(0))

    If (ViewState("sortdirection").ToString()) = "ASC" Then
    dv.Sort = e.SortExpression + " DESC"
    ViewState("sortdirection") = "DESC"
    Else
    dv.Sort = e.SortExpression + " ASC"
    ViewState("sortdirection") = "ASC"
    End If

    DataGridView.DataSource = dv
    DataGridView.DataBind()
    Session("LastSort") = e.SortExpression & " " & ViewState("sortdirection")

    End If

    End Sub

    Private Sub ReSort()   'Performs initial Sort on form load
    If GetDataSet() IsNot Nothing Then
    Dim dv As DataView = New DataView(GetDataSet().Tables(0))
    dv.Sort = Session("LastSort")
    DataGridView.DataSource = dv
    DataGridView.DataBind()
    End If
    End Sub

    Saturday, June 23, 2018 10:20 PM
  • User-330142929 posted

    Hi Trims30,

    I am sorry to tell you that the previously submitted code is incorrect. I did not reorder the data binding in the PageIndexChanging event. This is the reason for this problem.

    I have modified the code based on your code snippets, please check.

    Aspx.

    <asp:GridView ID="DataGridView"  runat="server"
                    AutoGenerateColumns="false"
                    AllowPaging="True"
                    AllowSorting="True" PageSize="15"
                     Width="100%" Font-Names="Arial" Font-Size="Large">
                    <AlternatingRowStyle BackColor="#FFCC99" />
                    <Columns>
                        <asp:CommandField  ButtonType="Button" SelectText="Edit"  ShowSelectButton="True"
                        ItemStyle-HorizontalAlign="Center" ItemStyle-Width="65px"  />
     
                        <asp:BoundField DataField="Account" HeaderText="Account" SortExpression="Account" />
                        <asp:BoundField DataField="Sequence" HeaderText="Sequence" SortExpression="Sequence" />
                        <asp:BoundField DataField="Dept" HeaderText="Dept" SortExpression="Dept" />
                        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
                        <asp:BoundField DataField="Type" HeaderText="Type" />
                        <asp:BoundField DataField="MS" HeaderText="MS" />
                        <asp:BoundField DataField="EjGr" HeaderText="EjGr" />
                    </Columns>
     
                    <HeaderStyle BackColor="Silver" Font-Names="Arial" Font-Size="Smaller" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#0099FF" Font-Names="Arial" />
                    <SortedAscendingHeaderStyle BackColor="Aqua" />
                    <SortedDescendingHeaderStyle BackColor="#FFFF66" />
                    <PagerSettings Mode="NextPreviousFirstLast" Position="top"
                        FirstPageImageUrl="images\btnfirst.jpg" LastPageImageUrl="images\btnlast.jpg"
                        NextPageImageUrl="images\btnnext.jpg" PreviousPageImageUrl="images\btnprevious.jpg" />
                    <PagerStyle HorizontalAlign="Right" />
                </asp:GridView>


    Code behind.

      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                ViewState("sortdirection") = "ASC"
                Session("LastSort") = "Description ASC"
                ReSort()
            End If
        End Sub
        Private Function GetDataSet() As DataSet
            Dim path As String = Server.MapPath("~/App_Data/2.xml")
            Dim ds As DataSet = New DataSet()
            ds.ReadXml(path, XmlReadMode.InferSchema)
            Return ds
        End Function
     
        Protected Sub DataGridView_Sorting(sender As Object, e As GridViewSortEventArgs) Handles DataGridView.Sorting
            If GetDataSet() IsNot Nothing Then
                Dim dv As DataView = New DataView(GetDataSet().Tables(0))
                If ViewState("sortdirection").ToString() = "ASC" Then
                    ViewState("sortdirection") = "DESC"
                    Session("LastSort") = e.SortExpression + " DESC"
                Else
                    ViewState("sortdirection") = "ASC"
                    Session("LastSort") = e.SortExpression + " ASC"
                End If
                ReSort()
            End If
        End Sub
     
        Protected Sub DataGridView_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles DataGridView.PageIndexChanging
            DataGridView.PageIndex = e.NewPageIndex
            ReSort()
        End Sub
        Private Sub ReSort()
            If GetDataSet() IsNot Nothing Then
                Dim dv As DataView = New DataView(GetDataSet().Tables(0))
                dv.Sort = Session("LastSort")
                DataGridView.DataSource = dv
                DataGridView.DataBind()
            End If
    End Sub

    How it works.

    If the problem still exists, please feel free to let me know.

    Best Regards,

    Abraham

     

    Monday, June 25, 2018 2:50 AM
  • User-72198832 posted

    Abraham:

    Sorry I didn't get back to you sooner - am traveling and had no internet for a few days.

    Code changes you sent work well - Thanks.

    Had to add a few more lines of code to force Sort Ascending on Column Header Click when changing sort Column.  Also had to force Page Zero to sort new column from beginning.

    Thanks again for your help.

    Lee

    Here's code changes....

    In CodeBehind Page_Load

    ViewState("sortdirection") = "ASC"
    Session("LastSort") = "Description ASC"
    Session("CurrentIndex") = "Description"
    ReSort()


    In Gridview Sorting

    Private Sub DataGridView_Sorting(sender As Object, e As GridViewSortEventArgs) Handles DataGridView.Sorting 
    If GetDataSet() IsNot Nothing Then
        Dim dv As DataView = New DataView(GetDataSet().Tables(0))
        If Session("CurrentIndex") <> e.SortExpression Then
           DataGridView.PageIndex = 0
           ViewState("sortdirection") = "DESC"
        End If
        Session("CurrentIndex") = e.SortExpression
    
        If ViewState("sortdirection").ToString() = "ASC" Then
           ViewState("sortdirection") = "DESC"
           Session("LastSort") = e.SortExpression + " DESC"
        Else
           ViewState("sortdirection") = "ASC"
           Session("LastSort") = e.SortExpression + " ASC"
        End If
        ReSort()
    End If
    End Sub

    Wednesday, June 27, 2018 4:16 PM
  • User-330142929 posted

    Hi Trims30,

    Really envy your lifestyle, according to your description,

    Had to add a few more lines of code to force Sort Ascending on Column Header Click when changing sort Column.  Also had to force Page Zero to sort new column from beginning.

    Because of my poor English, I may misunderstand what you mean. I have modified the code.

    Code Behind.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                ViewState("sortdirection") = "ASC"
                Session("LastSort") = "Description ASC"
                Session("CurrentIndex") = "Description"
                ReSort()
            End If
    End Sub
        Protected Sub DataGridView_Sorting(sender As Object, e As GridViewSortEventArgs) Handles DataGridView.Sorting
            If GetDataSet() IsNot Nothing Then
                Dim dv As DataView = New DataView(GetDataSet().Tables(0))
                If Session("CurrentIndex") <> e.SortExpression Then
                    DataGridView.PageIndex = 0
                    ViewState("sortdirection") = "ASC"
                    Session("CurrentIndex") = e.SortExpression
                Else
                    If ViewState("sortdirection").ToString() = "ASC" Then
                        ViewState("sortdirection") = "DESC"
                    Else
                        ViewState("sortdirection") = "ASC"
                    End If
                End If
                Session("LastSort") = e.SortExpression + " " + ViewState("sortdirection")
                ReSort()
            End If
        End Sub

    Feel free to let me know if you have any question.

    Best Regards,

    Abraham.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 28, 2018 3:33 AM
  • User-72198832 posted

    Abraham:

    Test project is complete - thanks for your assistance;

    No problem with language - where are you located?

    I'm currently travelling in MotorHome in Washington State, USA - on working vacation till September.

    Lee

    Thursday, June 28, 2018 3:49 AM