Gridview ADO BOUND sorting...

[VisualGuy]

Maybe this isn't possible, because I can't find any compatible solutions. Here's my grid:

<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Here's my onload code...

Protected Sub GridView1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Load
If Not Page.IsPostBack Then
Dim MyReader As SqlDataReader
Dim MyConnection As SqlConnection = New SqlConnection

MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("TDRCS_Intranet_Conn").ConnectionString

Dim Mycommand As SqlCommand = New SqlCommand
Mycommand.CommandText = "Select TOP 3 * FROM WebViews"
Mycommand.CommandType = CommandType.Text
Mycommand.Connection = MyConnection

Mycommand.Connection.Open()
MyReader = Mycommand.ExecuteReader(CommandBehavior.CloseConnection)

GridView1.DataSource = MyReader
GridView1.DataBind()

Mycommand.Dispose()
MyConnection.Dispose()

End If
End Sub

______

Without me going through what I've already tried...Can anyone please help me figure out how this is done?

 

[VisualGuy]

I've made some changes, and I think that I might be getting close, but still no cigar...

<B>gridview:</B>

<asp:GridView ID="GridView1"
OnPageIndexChanging="gridView_PageIndexChanging" OnSorting="gridView_Sorting"
runat="server" AllowSorting="True" AllowPaging="True">
</asp:GridView>

<B>...with the following code behind (VB.NET 3.5)</B>

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration


Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub GridView1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Load
If Not Page.IsPostBack Then
Dim MyReader As SqlDataReader
Dim MyConnection As SqlConnection = New SqlConnection

MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("TDRCS_Intranet_Conn").ConnectionString

Dim Mycommand As SqlCommand = New SqlCommand
Mycommand.CommandText = "Select TOP 3 * FROM WebViews"
Mycommand.CommandType = CommandType.Text
Mycommand.Connection = MyConnection

Mycommand.Connection.Open()
MyReader = Mycommand.ExecuteReader(CommandBehavior.CloseConnection)

GridView1.DataSource = MyReader
GridView1.DataBind()

Mycommand.Dispose()
MyConnection.Dispose()

End If
End Sub

Private Function ConvertSortDirectionToSql(ByVal sortDirection__1 As SortDirection) As String
Dim newSortDirection As String = [String].Empty

Select Case sortDirection__1
Case SortDirection.Ascending
newSortDirection = "ASC"
Exit Select

Case SortDirection.Descending
newSortDirection = "DESC"
Exit Select
End Select

Return newSortDirection
End Function

Protected Sub gridView_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub

Protected Sub gridView_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
Dim dataTable As DataTable = TryCast(GridView1.DataSource, DataTable)

If dataTable IsNot Nothing Then
Dim dataView As New DataView(dataTable)
dataView.Sort = (e.SortExpression & " ") + ConvertSortDirectionToSql(e.SortDirection)

GridView1.DataSource = dataView
GridView1.DataBind()
End If
End Sub
End Class

<B>Two problem then occur:
1. The data source does not support server-side data paging.

2. The sort doesn't work.
</B>

I've been wresting with this for quite some time...I'm now getting desperate...What am I doing wrong?

 

[jmeckley]

do not bind the grid directly to the reader. instead load the reader into a datatable. close/dispose the connection. then bind the datatable to the gridview.

ideally your presentation layer should never reference the ado.net (or any DAL) objects. instead the presentation layer should retrieve the data through intermediate services/objects which talk to the database. this allows you use create reusable objects throughout the system.

your sort direction will always be ascending, as the gridview doesn't track it's previous sort. if you want to get descending sort, you need to track the column/direction of each sort (in viewstate/hidden fields/session)/database.

paging is done in the code, not the database. to get custom paging you need to create create a custom datasource which contains the current page of items and total count of all items in the database. there are examples of this online.

Jason Meckley
Programmer
Specialty Bakers, Inc.