Sorting & Paging Gridview Bound to Dataset

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

 

Answer 6

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

Answer 7

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.

 

Answer 8

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