Question GridView Sort problem?

[raysefo]

Hi,

I have a gridview and i would like sort this gridview. The problem is, datatable is empty. Here is the code; How can i make this work?

thanks in advance.

Best Regards

Protected Sub gridView_Sort(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        [B]Dim dataTable As DataTable = CType(GridView1.DataSource, DataTable)[/B]

        If Not IsNothing(dataTable) Then
            Dim dataView As New DataView(dataTable)
            dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)

            GridView1.DataSource = dataView
            GridView1.DataBind()

        End If




    End Sub
    Private Function ConvertSortDirectionToSql(ByVal sortDirection As SortDirection) As String
        Dim newSortDirection As String = String.Empty


        Select Case sortDirection

            Case sortDirection.Ascending
                newSortDirection = "ASC"
                Exit Select
            Case sortDirection.Descending
                newSortDirection = "DESC"
                Exit Select

        End Select

        Return newSortDirection
    End Function

 

[kulrom]

Try this first:

    Protected Sub GridView1_Sorting(ByVal sender As Object, _
                                    ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting

        Dim dataTable As DataTable = CType(GridView1.DataSource, DataTable)
        If Not IsNothing(dataTable) Then
            Response.Write("It's not empty")
        End If

    End Sub

Btw, how do you call the Sorting event? show me the whole listing

 

[raysefo]

Here is my aspx:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There is no data for this request to display." AllowPaging="True"
CellPadding="4" ForeColor="#333333" GridLines="None" Style="margin-right: 0px"
Width="422px" DataKeyNames="EformID,EformType,TaskID,CreatedBy,ApprovalNumber,EformTable"
AllowSorting="True">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField DataField="Talebi Yapan" HeaderText="Talebi Yapan" SortExpression="Talebi Yapan">
<ItemStyle Font-Bold="True" ForeColor="Red" HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="Talep Zamanı" HeaderText="Talep Zamanı" SortExpression="Talep Zamanı"
ReadOnly="True">
<ItemStyle Font-Bold="True" HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="EForm Adı" HeaderText="EForm Adı" SortExpression="EForm Adı">
<ItemStyle Font-Italic="True" HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="EformID" HeaderText="EformID" SortExpression="EformID"
Visible="False" />
<asp:BoundField DataField="EformType" HeaderText="EformType" SortExpression="EformType"
Visible="False" />
<asp:BoundField DataField="TaskID" HeaderText="TaskID" SortExpression="TaskID" Visible="False" />
<asp:BoundField DataField="CreatedBy" HeaderText="CreatedBy" SortExpression="CreatedBy"
Visible="False" />
<asp:BoundField DataField="ApprovalNumber" HeaderText="ApprovalNumber" SortExpression="ApprovalNumber"
Visible="False" />
<asp:BoundField DataField="CreationDate" HeaderText="CreationDate" SortExpression="CreationDate"
Visible="False" />
<asp:BoundField DataField="EformTable" HeaderText="EformTable" SortExpression="EformTable"
Visible="False" />
<asp:CommandField ButtonType="Button" ShowSelectButton="True" ShowCancelButton="False">
<ItemStyle HorizontalAlign="Center" />
</asp:CommandField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

and here is my vb:

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

If Not IsNothing(dataTable) Then
Dim dataView As New DataView(dataTable)
dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)

GridView1.DataSource = dataView
GridView1.DataBind()

End If




End Sub
Private Function ConvertSortDirectionToSql(ByVal sortDirection As SortDirection) As String
Dim newSortDirection As String = String.Empty


Select Case sortDirection

Case sortDirection.Ascending
newSortDirection = "ASC"
Exit Select
Case sortDirection.Descending
newSortDirection = "DESC"
Exit Select

End Select

Return newSortDirection
End Function


Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting
gridView_Sort(sender, e)
End Sub

 

[kulrom]

If you use SqlDataSource and bind it via the DataSourceId, the DataSource property will be not set.
Thats why it always returns nothing. Why you don't use DataSource for binding?

 

[raysefo]

I m using like below;

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EFormsConnectionString1 %>"
ProviderName="<%$ ConnectionStrings:EFormsConnectionString1.ProviderName %>"
SelectCommand="SELECT Users.name AS 'Talebi Yapan', CONVERT (varchar(20), Tasks.CreationDate, 113) AS 'Talep Zamanı', EformType.EformName as 'EForm Adı', Tasks.EformID, Tasks.EformType, Tasks.TaskID, Tasks.CreatedBy, Tasks.ApprovalNumber, EformType.EformTable FROM Tasks INNER JOIN EformType ON Tasks.EformType = EformType.EformType INNER JOIN Users ON Tasks.CreatedBy = Users.SAMAccountName WHERE (Tasks.IsApproved = 0) and Tasks.Approver =@user ORDER BY Tasks.CreationDate asc">
<SelectParameters>
<asp:SessionParameter Name="user" SessionField="user" />
</SelectParameters>
</asp:SqlDataSource>

How should i change this to datasource?

 

[kulrom]

delete DataSourceID from the gridview and then bind the gridview in the code-behind. Use sqlcommand object for that. I will show you later.
Before we change the approach let's try another thing if you still want to keep the existing architecture.

Instead casting datasource you should cast the sqldatasource itself e.g.

Protected Sub gridView_Sort(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
        If dv IsNot Nothing Then
            dv.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)
            GridView1.DataSource = dv
            GridView1.DataBind()
        End If
    End Sub

 

[raysefo]

If i remove DataSourceID then when page opens it displays nothing as usual. And if i add your code like below it gives error "oth DataSource and DataSourceID are defined on 'GridView1'. Remove one definition." BUT dataTable is NOT empty when i add your code.

code:
Protected Sub gridView_Sort(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
Dim dv As System.Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
Dim dataTable As DataTable = TryCast(dv.Table, DataTable)


If Not IsNothing(dataTable) Then
Dim dataView As New DataView(dataTable)
dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)

GridView1.DataSource = dataView
GridView1.DataBind()

End If




End Sub

 

[kulrom]

No no. For NOW i suggest that you change nothing in the asp.net code (.aspx). Just copy paste the code i posted above (i modified it in the meantime) so this is the code you should change:

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

        If Not IsNothing(dataTable) Then
            Dim dataView As New DataView(dataTable)
            dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)

            GridView1.DataSource = dataView
            GridView1.DataBind()

        End If
    End Sub

change to this:

Protected Sub gridView_Sort(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
        If dv IsNot Nothing Then
            dv.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)
            GridView1.DataSource = dv
            GridView1.DataBind()
        End If
    End Sub

Remember! You should change nothing else! Only this Sub!

 

[raysefo]

Still getting same error
Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.


Protected Sub gridView_Sort(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
If dv IsNot Nothing Then
dv.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)
GridView1.DataSource = dv
GridView1.DataBind()
End If

End Sub

 

[kulrom]

Yeah my bad. I overlooked that. However you should read this article Sorting Data with Data Source Controls