Answered by:
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
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>
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).TextEnd 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 & NUTRIENTS" Type="2" MS="S" EjGr=" "/> <Budget Account="2400-00" Sequence="3030" Dept="00" Description="FREIGHT & 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 & 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 SubPrivate 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 IfDataGridView.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 SubSaturday, 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
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