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”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<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>
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
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.
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!
Thanks Tom
I wish many get benefited with this change.