VB.NET GridView Sorting/Paging w/o a DataSourceControl DataSource
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.
Click here to download the below code
Click here to test script below live
<%@ 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(gridViewPublishers.DataSource, True)
gridViewPublishers.PageIndex = e.NewPageIndex
gridViewPublishers.DataBind()
End Sub
Protected Function SortDataTable(ByVal dataTable As DataTable, ByVal isPageIndexChanging As Boolean) As DataView
If Not dataTable Is Nothing Then
Dim dataView As New DataView(dataTable)
If GridViewSortExpression <> String.Empty Then
If isPageIndexChanging Then
dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection)
Else
dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GetSortDirection())
End If
End If
Return dataView
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(gridViewPublishers.DataSource, 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>
Similar Posts
- C# GridView Sorting/Paging w/o a DataSourceControl DataSource
- GridView without DataSourceControl DataSource
- GridView Sorting
Comments
steve Schoonover on on 6.05.2008 at 10:36 AM
I cannot get the code to work. I do not have a data table.
Here is my code:
Dim ConStr As String = ConfigurationManager.ConnectionStrings("NUCO2ConnectionString").ConnectionString
Dim cn As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConStr)
Dim cmd As New SqlCommand("sp_PhoneRoster", cn)
Dim da As SqlDataAdapter = New SqlDataAdapter()
Dim ds As New Data.DataSet
Try
' Set Parms for Call to Stored Procedure
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@DeptValue", DeptValue)
cmd.Parameters.AddWithValue("@SearchName", SearchName)
cmd.Parameters.AddWithValue("@NameType", NameType)
' Open Connection
cmd.Connection.Open()
da.SelectCommand = cmd
' Fill Data Set
da.Fill(ds)
' Dislay in GridView
GridView1.Controls.Clear()
GridView1.DataSource = ds
GridView1.DataBind()
SqlConnection.ClearPool(cn)
' Close Connection
cmd.Connection.Close()
cmd.Connection.Dispose()
Catch ex As Exception
lblText.Text = ex.Message
End Try
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub
Michael Rybicki on on 7.01.2008 at 11:21 AM
I grabbed your code and while it does sort/page correctly, I an issue. I have a select button so when the user clicks it, they are redirected to a new page with the data fields loaded.
If, for example, there are 3 IDs/Items:
ID: 1 Item: "AAA"
ID: 2 Item: "BBB"
ID: 3 Item: "CCC"
Click on the first row and the new page shows "AAA"s data.
Click the header so it is sorted:
ID: 3 Item: "CCC"
ID: 2 Item: "BBB"
ID: 1 Item: "AAA"
Click on the first row and the new page shows "AAA"s data again. The page shows the new sorted items but the IDs remain as when the gridview is first loaded.
pavan on on 8.12.2008 at 7:17 AM
thank's
very good usefull code