Problems sorting gridview

[Ana D.]

Hi,

I'm trying to allow sorting my gridview writing in the code behind page. My code for Bind the GridView is like this:
   Private Sub BindMyGridView()
        Dim DatabaseConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("MyServer").ConnectionString)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter
        Dim selectSQL As String = "SELECT * FROM Clients WHERE id = @id"
        Dim selectCMD As New SqlCommand(selectSQL, DatabaseConnection)
        Dim id As Integer = firstGridView.SelectedValue

        selectCMD.Parameters.Add(New SqlParameter("@id ", SqlDbType.Int))
        selectCMD.Parameters("@id ").Value = id

        adapter.SelectCommand = selectCMD

        Dim dTable As DataTable = New DataTable
        adapter.Fill(dTable)

        MyGridView.DataSource = dTable
        MyGridView.DataBind()
    End Sub

And my code for sorting is like this:

Private Function SortDirectionSQL(ByVal direction As SortDirection) As String
        Dim directionSQL As String = ""
        Select Case direction
            Case SortDirection.Ascending
                directionSQL = "ASC"
            Case SortDirection.Descending
                directionSQL = "DESC"
        End Select
        Return directionSQL
    End Function

    Protected Sub MyGridView _OnSorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)
        Dim dTable As DataTable = MyGridView.DataSource
        If dTable.Rows.Count > 0 Then
            Dim dView As New DataView(dTable)
            dView.Sort = e.SortExpression + " " + SortDirectionSQL(e.SortDirection)
            MyGridView.DataSource = dView
            BindMyGridView ()
        End If
    End Sub

However, the proble I'm having is in the line:

dTable.Rows.Count > 0

and this is happening becaus emy DataSource is always nothing.

Does anyone have ideas about how to solve this problem?

Thanks in advance.

Ana

[Ramesh S]

You cannot get the DataSource from GridView. You should store it in the Session or again fill it from database, sort it and then bind to the GridView again.

[mail2saion]

For small amount of data you can use viewstate. Better to use a datatable to bind so that for next time no need to go to the database. You can sort on datatable & rebind to Gridview.

[Ana D.]

I changed my code and now I'm able to retrieve the DataSource. Now what I have is:

Protected Sub MyGridView_OnSorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)
Dim dTable As DataTable = MyGridViewDataSource()
If dTable.Rows.Count > 0 Then
Dim dView As New DataView(dTable)
dView.Sort = e.SortExpression + " " + SortDirectionSQL(e.SortDirection)
MyGridView.DataSource = dView
MyGridView.DataBind()
End If
End Sub

But even now the sorting is not working. It simply doesn't do anything. Does anyone have any ideas about what is happening?

Thanks a lot!

[Ana D.]

Finally my GridView is Sorting the data properly. The solution I found is in this website: http://www.mikepope.com/blog/DisplayBlog.aspx?permalink=1418

Thanks for all the replies :)

[Ramesh S]

Put a break point in this event and check the execution control goes inside dTable.Rows.Count > 0 condition.

[Ramesh S]

Hi Ana,
Ignore my last post. Sorry, I didn't refresh to see your last post.

[Ana D.]

No problem! Thanks anyway :)