FAQ - Frequently Asked Questions

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

krishanmanral — Mon, 30 Jul 2007 06:56:02 GMT

thank you very much it works for me

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

krishanmanral — Mon, 30 Jul 2007 06:40:10 GMT

thanks it works for me

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

shakthi_001 — Fri, 27 Jul 2007 16:30:37 GMT

Hi Ryan thx for the post......i copied the code(GridView Sorting/Paging w/o a DataSourceControl DataSource)and it was working gr8.....but i moved the PopulatePublishersGridView() from page load to Button_Click.....Once i click the button i can see the Grid.....but if try clicking header text for sorting iam getting an message "NO DATA FOUND".....even if i click page number same error...but every thing was fine if it is in page_load......can plz tell me the problem...................thank you

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

osmanmz — Fri, 27 Jul 2007 07:00:42 GMT

Store your sort direction in a viewStateVariable.

Save the last direction once you're completed sorting. I've used three states below: ASC, DESC, unsorted:

(In "grdvwdesignmaster_Sorting" change

"dataView.Sort = e.SortExpression + " " + ConvertSortDir(e.SortDirection); "

to:

if (GridViewSortDirection.Length > 0)

{

dataView.Sort = e.SortExpression + " " + GridViewSortDirection;

}

ChangeSortDirection();

ViewState property & function:

private string GridViewSortDirection

{

get { return ViewState["SortDirection"] as string ?? "ASC"; }

set { ViewState["SortDirection"] = value; }

}

private string ChangeSortDirection()

{

switch (GridViewSortDirection)

{

case "ASC":

GridViewSortDirection = "DESC";

break;

case "DESC":

GridViewSortDirection = "";

break;

default:

GridViewSortDirection = "ASC";

break;

}

return GridViewSortDirection;

}

private string GridViewSortDirection

{

get { return ViewState["SortDirection"] as string ?? "ASC"; }

set { ViewState["SortDirection"] = value; }

}

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

krishanmanral — Fri, 27 Jul 2007 05:58:42 GMT

i m having problem that when i select any header item for sorting i got the same result of sortdirection = acsending every time.

ConvertSortDirectionToSql function alwawys returns "Asc" what should i do next

my code is as below

private string ConvertSortDir(SortDirection sortDireciton)

{

string newSortDirection = String.Empty;switch (sortDireciton)

{

case SortDirection.Ascending:

newSortDirection = "ASC";

break;

case SortDirection.Descending:

newSortDirection = "DESC";

break;

}

return newSortDirection;

}

protected void grdvwdesignmaster_Sorting(object sender, GridViewSortEventArgs e)

{

DataTable objds1 = (DataTable)Session["desmas"];if (objds1 != null)

{

DataView dataView = new DataView(objds1);

dataView.Sort = e.SortExpression + " " + ConvertSortDir(e.SortDirection);

grdvwdesignmaster.DataSource = dataView;

grdvwdesignmaster.DataBind();

}

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

krishanmanral — Fri, 27 Jul 2007 05:57:38 GMT

i m having problem that when i select any header item for sorting i got the same result of sortdirection = acsending every time.

ConvertSortDirectionToSql function alwawys returns "Asc" what should i do next

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

osmanmz — Wed, 25 Jul 2007 08:31:18 GMT

Thanks for the post its helped a great deal.

How is it that your page does an auto postback on sorting or paging.

I'm using the following code which is very similar, it does the sorting or paging only when I click a button on the page, not when I sort or page.

i.e. it is a delayed reaction. I've also had to add the an invalid postback or call argument error with the suggestion of setting

EnableEventValidation="false" which resolves that issue:

The only difference is that I've using a Master page.

Please have a look at my code below, I've attached the code for the GridView.

Thanks and regards

Mohamed

<%@ Page Language="C#" MasterPageFile="~/ShareX.master" AutoEventWireup="true" EnableEventValidation="false" CodeBehind="BrokerDailyTrade.aspx.cs" Inherits="ShareXIntranet.BrokerDailyTradeForm" %>

<%@ mastertype virtualpath="~/ShareX.master" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

...

<asp:GridView id="gridViewBrokerDailyTrade" runat="server" AutoGenerateColumns="false" DataKeyNames="ipkBrokerDailyTradeID"

OnSelectedIndexChanged="gridViewBrokerDailyTrade_SelectedIndexChanged" AllowPaging="true" AllowSorting="true"

OnPageIndexChanging="gridViewBrokerDailyTrade_PageIndexChanging" OnSorting="gridViewBrokerDailyTrade_Sorting"

PagerSettings-Mode="NumericFirstLast" PageSize="25"

<asp:BoundField ReadOnly="True" DataField="ipkBrokerDailyTradeID" HeaderText="ipkTransactionTradeDetailID" Visible="False"></asp:BoundField>

<asp:BoundField DataField="dtTrade" HeaderText="Trade date" DataFormatString="{0:dd MMM yyyy}" HtmlEncode="False" SortExpression="dtTrade"></asp:BoundField>

<asp:BoundField DataField="dYieldAmount" HeaderText="Yield amount" DataFormatString="{0:C}" HtmlEncode="False" SortExpression="dYieldAmount"></asp:BoundField>

<asp:BoundField DataField="dtYieldAmountPaid" HeaderText="Paid date" DataFormatString="{0:dd MMM yyyy}" HtmlEncode="False" SortExpression="dtYieldAmountPaid"></asp:BoundField>

<asp:ButtonField Text="Select" CommandName="Select" />

</Columns>

<asp:Label id="lblGridNoData" runat="server" Text="There are no broker daily trades awaiting processing." ForeColor="Red" Font-Bold="True"></asp:Label>

</EmptyDataTemplate>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

ajrich — Fri, 13 Jul 2007 15:48:10 GMT

Hi, thanks for the post. Although I was never able to get this working without manually changing the sort direction between postbacks (for some reason it would always stay ascending) I did think of a different approach to this problem that worked for me.

One problem I have with this is that felt so...DataGrid-y. I disliked the cumbersome work needed for paging and sorting on the 1.0/1.1 DataGrid and was thrilled to see that the GridView dealt with those problem inside of it's "Black Box". So, after hours trying to get manual sorting things to work in a clean manner I thought maybe I could trick it into thinking my on-the-fly data source is just a standard sortable/pagable datasource (datatable, datagrid or dataview).

What I did is create a class, as explained in an example on msdn, that has an attribute explaining that the shared method is the Select statement to be used for the object data source. Here's a snippit of the code I used.

Namespace IB
    ''' <summary>
    ''' Class writen to bind to the object data source.  I need to do this so that I can dynamically change the data based on the filters chosen via the dropdowns
    ''' </summary>
    ''' <remarks></remarks>
    <ComponentModel.DataObject(True)> _
    Public Class FilteredDataManager
        <ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select)> _
        Public Shared Function GetFilteredNewsData() As DataView
            Dim dt As DataTable = Nothing
            
            Try
                Using cm As New Baird.Internet.ContentManagers.CMSNewsContentManager
                    dt = cm.GetNewsData()
                End Using

	'<Custom filtering based on selections on drop downs performed here>
            Catch ex As Exception
                Throw New ApplicationException(String.Format("IB.FilteredDataManager.GetFilteredNewsData: Error {0}{1}", vbCrLf, ex.ToString()))
            End Try

            Return dt
        End Function
    End Class
End Namespace

Now that you have this class you can set this as the source to your ObjectDataSource as so (you can do this in code on even in design mode):


<asp:GridView ID="GridView" runat="server" AutoGenerateColumns="False" DataKeyNames="NewsLinkID" AllowSorting="True" HeaderStyle-HorizontalAlign="left" GridLines="none" Width="100%" DataSourceID="ObjectDataSourceNewsData">

<asp:ObjectDataSource ID="ObjectDataSourceNewsData" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetFilteredNewsData" TypeName="UI.News.IB.FilteredDataManager"></asp:ObjectDataSource>

As long as you return a DataSet, DataTable or DataView, the GridView handles sorting and paging automagically - which is how I like it.

Hope this helps someone that has a similar issue as I.

Thanks.

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

Mr Blue Coat — Tue, 10 Jul 2007 22:51:47 GMT

For some reason, this forum tool replaced the word "E x p r e s s i o n" with "BLOCKED E X P R E S S I O N" above (remove spaces between letters).

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

Mr Blue Coat — Tue, 10 Jul 2007 22:35:06 GMT

Excellent post - Thanks!

Here's my newbie ASP.NET variation for SQL Server using Session variables (instead of ViewState) and support for paging, sorting, data filtering and selecting:

web.config:

<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=<SERVER>;Initial Catalog=<DATABASE>;Persist Security Info=True;User ID=<USER>;Password=<PASSWORD>" providerName="System.Data.SqlClient"/>
</connectionStrings>

app_code (DatabaseConnection.vb):

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class DatabaseConnection

Public Shared connection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)

End Class

app_code (Utils.vb):

Imports Microsoft.VisualBasic
Imports System.Web.HttpContext
Imports System.Data
Imports System.Data.SqlClient
Imports DatabaseConnection

Public Class Utils

Public Shared Function GetCompareStatement(ByVal firstValue As String, ByVal secondValue As String, ByVal operation As String) As String
        Select Case operation
            Case "Contains"
                Return "LIKE '%" + firstValue + "%' "
            Case "Begins with"
                Return "LIKE '" + firstValue + "%' "
            Case "Ends with"
                Return "LIKE '%" + firstValue + "' "
            Case "Equals"
                Return "= '" + firstValue + "' "
            Case "Date Equals"
                Return "= CONVERT(DATETIME, '" + firstValue + "', 101) "
            Case "After"
                Return "> CONVERT(DATETIME, '" + firstValue + "', 101) "
            Case "Before"
                Return "< CONVERT(DATETIME, '" + firstValue + "', 101) "
            Case "Between"
                Return "BETWEEN CONVERT(DATETIME, '" + firstValue + "', 101) AND CONVERT(DATETIME, '" + secondValue + "', 101) "
            Case Else
                Return ""
        End Select
    End Function

    Public Shared Sub BindPagingSortingGridView(ByVal query As String, ByVal gridViewControl As GridView)
        'Binds Paging/Sorting GridView with data from the specified query
        Dim sqlComm As SqlCommand
        Dim sqlReader As SqlDataReader
        Dim dataTableControl As New DataTable()
        Dim dataTableRowCount As Integer

        sqlComm = New SqlCommand(query, connection)
        sqlReader = sqlComm.ExecuteReader()
        dataTableControl.Load(sqlReader)
        dataTableRowCount = dataTableControl.Rows.Count

        If dataTableRowCount > 0 Then
            gridViewControl.DataSource = dataTableControl
            gridViewControl.DataBind()
        End If

        sqlReader.Close()
    End Sub

    Public Shared 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

    Public Shared Property GridViewSortBLOCKED EXPRESSION As String
        Get
            Return IIf(Current.Session("SortBLOCKED EXPRESSION = Nothing, String.Empty, Current.Session("SortBLOCKED EXPRESSION)
        End Get
        Set(ByVal value As String)
            Current.Session("SortBLOCKED EXPRESSION = value
        End Set
    End Property

    Private Shared Function GetSortDirection() As String
        Select Case GridViewSortDirection
            Case "ASC"
                GridViewSortDirection = "DESC"
            Case "DESC"
                GridViewSortDirection = "ASC"
        End Select
        Return GridViewSortDirection
    End Function

    Public Shared Property GridViewSortDirection() As String
        Get
            Return IIf(Current.Session("SortDirection") = Nothing, "ASC", Current.Session("SortDirection"))
        End Get
        Set(ByVal value As String)
            Current.Session("SortDirection") = value
        End Set
    End Property

End Class

aspx page:

                <asp:GridView ID="SupplierGridView" CssClass="datatable" Width="100%" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
                        EmptyDataText="No records found" PageSize="15" runat="server">
                    <Columns>
                        <asp:ButtonField ButtonType="Link" Text="Select" CommandName="SELECT" />
                        <asp:BoundField DataField="ENT_SUPPLIER_NBR" HeaderText="ESD Number" SortExpression="ENT_SUPPLIER_NBR" />
                        <asp:BoundField DataField="SUP_NAME1" HeaderText="Name" SortExpression="SUP_NAME1" />
                        <asp:BoundField DataField="STREET" HeaderText="Street" SortExpression="STREET" />
                        <asp:BoundField DataField="CITY" HeaderText="City" SortExpression="CITY" />
                        <asp:BoundField DataField="STATE_REGION" HeaderText="State" SortExpression="STATE_REGION" />
                        <asp:BoundField DataField="ACTIVE" HeaderText="ACTIVE" SortExpression="ACTIVE" />
                    </Columns>
                </asp:GridView>

vb page:

Imports System.Data
Imports System.Data.SqlClient
Imports Utils
Imports DatabaseConnection

...

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       If Not Page.IsPostBack Then
            ' Clear user paging/sort
            Session("CurrentPage") = 0
            Session("SortBLOCKED EXPRESSION = Nothing
            GridViewSortDirection = "ASC"
            ' Set current query
            Session("CurrentQuery") = "SELECT ENT_SUPPLIER_NBR, SUP_NAME1, STREET, CITY, STATE_REGION, Active FROM INFO_SUPPLIERS WHERE ENT_SUPPLIER_NBR IS NOT NULL AND ACTIVE = 'Active' ORDER BY SUP_NAME1 ASC"
        End If

        ' Bind GridView to current query
        connection.Open()
        BindPagingSortingGridView(Session("CurrentQuery"), SupplierGridView)
        connection.Close()

End Sub

Protected Sub ApplyFilterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ApplyFilterButton.Click
        ' Filter GridView contents

        Dim tempQuery As String = "SELECT ENT_SUPPLIER_NBR, SUP_NAME1, STREET, CITY, STATE_REGION, Active FROM INFO_Suppliers WHERE ENT_SUPPLIER_NBR IS NOT NULL"
        ' Filter ESD Number
        If Len(EsdNumberTextBox.Text) > 0 Then
            tempQuery += " AND ENT_SUPPLIER_NBR " & GetCompareStatement(EsdNumberTextBox.Text, Nothing, EsdNumberDropDownList.SelectedValue)
        End If
        ' Filter Supplier Name
        If Len(SupplierNameTextBox.Text) > 0 Then
            tempQuery += " AND SUP_NAME1 " & GetCompareStatement(SupplierNameTextBox.Text, Nothing, SupplierNameDropDownList.SelectedValue)
        End If
        ' Filter Supplier City
        If Len(SupplierCityTextBox.Text) > 0 Then
            tempQuery += " AND CITY " & GetCompareStatement(SupplierCityTextBox.Text, Nothing, SupplierCityDropDownList.SelectedValue)
        End If
        ' Filter Supplier State
        If Len(SupplierStateTextBox.Text) > 0 Then
            tempQuery += " AND STATE_REGION " & GetCompareStatement(SupplierStateTextBox.Text, Nothing, SupplierStateDropDownList.SelectedValue)
        End If
        ' Filter Active
        If ActiveDropDownList.SelectedIndex > 0 Then
            tempQuery += " AND Active = '" & ActiveDropDownList.SelectedValue & "'"
        End If

        ' Clear user paging/sort
        Session("CurrentPage") = 0
        Session("SortBLOCKED EXPRESSION = Nothing
        GridViewSortDirection = "ASC"

        ' Update current query
        Session("CurrentQuery") = Trim(tempQuery) & " ORDER BY SUP_NAME1 ASC"

        ' Bind GridView to current query
        connection.Open()
        BindPagingSortingGridView(Session("CurrentQuery"), SupplierGridView)
        connection.Close()

    End Sub

   Protected Sub ShowAllButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ShowAllButton.Click
        ' Show all suppliers

        ' Clear filter options
        EsdNumberDropDownList.SelectedIndex = 0
        SupplierNameDropDownList.SelectedIndex = 0
        SupplierCityDropDownList.SelectedIndex = 0
        SupplierStateDropDownList.SelectedIndex = 0
        ActiveDropDownList.SelectedIndex = 0
        EsdNumberTextBox.Text = ""
        SupplierNameTextBox.Text = ""
        SupplierCityTextBox.Text = ""
        SupplierStateTextBox.Text = ""

        ' Clear user paging/sort
        Session("CurrentPage") = 0
        Session("SortDirection") = Nothing

        ' Update current query
        Session("CurrentQuery") = "SELECT ENT_SUPPLIER_NBR, SUP_NAME1, STREET, CITY, STATE_REGION, Active FROM INFO_Suppliers WHERE ENT_SUPPLIER_NBR IS NOT NULL ORDER BY SUP_NAME1 ASC"

        ' Bind GridView to current query
        connection.Open()
        BindPagingSortingGridView(Session("CurrentQuery"), SupplierGridView)
        connection.Close()
    End Sub

   Protected Sub SupplierGridView_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles SupplierGridView.SelectedIndexChanged
        ' Bind current data
        SupplierGridView.DataSource = SortDataTable(SupplierGridView.DataSource, True)
        SupplierGridView.PageIndex = Session("CurrentPage")
        SupplierGridView.DataBind()

        ' <This is the row the user selected -- do with it what you want>
        supplierNumber.Text = SupplierGridView.SelectedRow.Cells(1).Text
        supplierName.Text = SupplierGridView.SelectedRow.Cells(2).Text
        supplierStreet.Text = SupplierGridView.SelectedRow.Cells(3).Text
      ...

        ' Clear session variables
        Session("CurrentPage") = Nothing
        Session("SortBLOCKED EXPRESSION = Nothing
        Session("CurrentQuery") = Nothing

    End Sub

    Protected Sub SupplierGridView_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles SupplierGridView.Sorting
        GridViewSortExpression = e.SortExpression
        Dim pageIndex As Integer = SupplierGridView.PageIndex
        If SupplierGridView.DataSource.GetType().ToString = "System.Data.DataTable" Then
            SupplierGridView.DataSource = SortDataTable(SupplierGridView.DataSource, False)
            SupplierGridView.PageIndex = Session("CurrentPage")
            SupplierGridView.DataBind()
        End If
    End Sub

    Protected Sub SupplierGridView_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles SupplierGridView.PageIndexChanging
        If SupplierGridView.DataSource.GetType().ToString = "System.Data.DataTable" Then
            SupplierGridView.DataSource = SortDataTable(SupplierGridView.DataSource, True)
            SupplierGridView.PageIndex = e.NewPageIndex
            Session("CurrentPage") = e.NewPageIndex
            SupplierGridView.DataBind()
        End If
    End Sub

Hopefully this helps someone out there as much as this forum posting helped me!

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

pelegk2 — Tue, 10 Jul 2007 21:18:01 GMT

when i am using the

productsGridView_PageIndexChanging

i see in the debbuger that the function is called twice

why is that?

thnaks i nadvance

peleg

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

peterthegreat — Thu, 05 Jul 2007 14:52:30 GMT

like you i am a new member,also to asp.net.

my question is can you not accomplish this via asp.net?

i am not sure what you are trying to achieve.but in time i suppose i will come across its use.so i have decided to save a copy of code...just in case.

i would like to create own websites,like you.

are there any useful sites where you can get useful code/examples from

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

StrongTypes — Thu, 05 Jul 2007 14:21:04 GMT

mcmillan_associates@hotmail.com:
Ryan - this is very helpful but can you post this code in VB.NET?

There is a VB.NET version.

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

Raafat4 — Thu, 05 Jul 2007 09:22:01 GMT

Hi,

I am Ra'fat not Ryan ,

But i forget the VB.NET Syntax, specially Function Definition

Try this code:

private string ConvertSortDirectionToSql(SortDirection sortDireciton)

{

Dim newSortDirection as String

newSortDirection = " "

switch (sortDireciton)

{

case SortDirection.Ascending:

newSortDirection = "ASC"

break

case SortDirection.Descending:

newSortDirection = "DESC"break

}

return newSortDirection

}

There is an event in the GridView events called Sorting ,put the code velow in it:

protected void gvDataSources_Sorting(object sender, GridViewSortEventArgs e)

{

Dim dt as new DataTable

dt = LoadDataSources().Tables(0); ' LoadDataSources() function that return DataSet and the GridView Get its data from it

if (dt <> null) Dim dvSortas new DataView(dt)

dvSort.Sort = e.SortExpression

dvSort.Sort = e.SortExpression & " " & ConvertSortDirectionToSql(e.SortDirection)

gvDataSources.DataSource = dvSort

gvDataSources.DataBind()

End If

}

Best Regards

Ra'fat

Re: HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource

mcmillan_associates@hotmail.com — Wed, 04 Jul 2007 23:25:06 GMT

Ryan - this is very helpful but can you post this code in VB.NET?