Loading ...

GridView Sorting with ObjectDataSource Issues

Who is online?  0 guests and 0 members
home  »  forums   »  asp.net topics   »  web forms / data controls   » GridView Sorting with ObjectDataSource Issues

GridView Sorting with ObjectDataSource Issues

Posts under the topic: GridView Sorting with ObjectDataSource Issues

Posted: 4/11/2009

mazen97
Mazen Al
Lurker 2  points  Lurker
  • Joined on: 4/11/2009
  • Posts: 1

Guys,


I'm developed a quiet complex searh page for my company and produced the results in a datagrid.

I'm following N-Tier structure so I have a view model (SortSearch.aspx), Business Logic (SortSearchManagement.vb) and Data Access Layer (SortsearchDA.vb).

I created ObjectDataSource that is getting list of objects (SearchResultEntity) and the searching conditions are stored in a session as well as sortingexpression, sorting direction and others as it shows below 

<asp:ObjectDataSource ID="SearchResultsObjectDataSource" runat="server" 
        SelectMethod="getRequests" TypeName="ATVS.Initiation.BLL.SortSearchManager" 
        SortParameterName="SortExpression">
        <SelectParameters>
            <asp:SessionParameter Name="searchCriteria" SessionField="SearchCriteria" 
                Type="Object" />
            <asp:SessionParameter DefaultValue="" Name="SearchType" SessionField="TYPE" 
                Type="String" />
            <asp:SessionParameter Name="UserGroup" SessionField="USERGROUP" Type="Int32" />
            <asp:SessionParameter Name="SortExpression" SessionField="SortExpression" 
                Type="String" />
            <asp:SessionParameter Name="SortDirection" SessionField="SortDirestion" 
                Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>




The grid contains multiple columns and some of its columns contains buttons and hyperlink as shown below:


<asp:GridView ID="resultsGrid" runat="server" AllowPaging="True" 
        AutoGenerateColumns="False" 
        DataSourceID="SearchResultsObjectDataSource" CssClass="grid" 
        AllowSorting="True">
        <Columns>
            <asp:HyperLinkField DataNavigateUrlFields="ARID" DataNavigateUrlFormatString="/LastResponse.aspx?ARID={0}"
                            HeaderText="ARID" Target="_blank" DataTextField="ARID" 
                SortExpression="ARID">
                <HeaderStyle CssClass="groupHeading" />
            </asp:HyperLinkField>
            <asp:BoundField DataField="InitiatorName" HeaderText="Initiator Name" 
                SortExpression="INITIATOR_LOGIN" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="LloydeNo" HeaderText="Lloyde #" 
                SortExpression="LLOYDS_NBR" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="TankerName" HeaderText="Tanker Name" 
                SortExpression="CURRENT_NAME" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="Port" HeaderText="Port" SortExpression="PORT_NAME" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="InitiationDate" HeaderText="Initiation Date" 
                SortExpression="created_date" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="Status" HeaderText="Status" 
                SortExpression="REQUEST_STATUS_NAME" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="EvaluatorName" HeaderText="Evaluator Name" 
                SortExpression="EVALUATOR" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="DaysLeft" HeaderText="Days Left" 
                SortExpression="EXPIRATION_DATE" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="Expirydate" HeaderText="Expiry Date" 
                SortExpression="EXPIRATION_DATE" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:ButtonField ButtonType="Button" CommandName="Select_Override" 
                Text="Override" >
                <ControlStyle CssClass="button" />
            </asp:ButtonField>
            <asp:ButtonField ButtonType="Button" CommandName="Select_Followup" 
                Text="Follow Up" >
                <ControlStyle CssClass="button" />
            </asp:ButtonField>
            <asp:ButtonField ButtonType="Button" CommandName="Cancel" Text="Cancel" >
                <ControlStyle CssClass="button" />
            </asp:ButtonField>
            <asp:BoundField DataField="InitiatorGroup" 
                SortExpression="InitiatorGroup" />
            <asp:BoundField DataField="StatusID" SortExpression="StatusID" />
            <asp:BoundField DataField="IsInitiatorReply" 
                SortExpression="IsInitiatorReply" />
        </Columns>
        <FooterStyle HorizontalAlign="Center" />
        <HeaderStyle CssClass="groupHeading" />
    </asp:GridView>



I turned autosorting on and wrote a cutomized sorting event as shown below


    Protected Sub resultsGrid_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles resultsGrid.Sorting
        Session("SortDirestion") = CInt(e.SortDirection)
        Session("SortExpression") = e.SortExpression.ToString()
        resultsGrid.DataBind()
    End Sub



I have two issues:


1. The session value SortExpression is passing nothing to Data Access Layer? While it is passing all others?
2. Once I click on a column header every thing went well but once the grid is rendering it disappears?!!

Here is the Page load event for the aspx file


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsNothing(Session("USERGROUP")) Then
            Response.Redirect("Default.aspx")
        End If
        Session("TYPE") = Request.QueryString("TYPE")

        resultsGrid.Visible = False
        If Session("TYPE") = "" Then
            Session("TYPE") = "ALL"
        End If
        Dim type As String = Session("TYPE")
        If type.Equals("FOLLOWUP") Or type.Equals("OVERRIDE") Then
            statusDropDownList.Visible = False
            StatusLabel.Visible = False
        End If
        If Not IsPostBack Then
            Session("SortDirestion") = 0
            Session("SortExpression") = "ARID"
        End If
    End Sub



Here are all events for GridView control


Protected Sub resultsGrid_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles resultsGrid.PageIndexChanging
        resultsGrid.PageIndex = e.NewPageIndex
        'resultsGrid.DataBind()
        startSearch()
    End Sub

Private Sub resultsGrid_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles resultsGrid.PreRender
        If resultsGrid.Rows.Count = 0 Then
            ErrLabel.Visible = True
            ErrLabel.Text = "There is no result matching your search criteria"
        Else
            ErrLabel.Visible = False
        End If
        'If Results.Count &gt; 0 Then
        populateResultGrid(Session("USERGROUP"))
        'End If
    End Sub

Protected Sub resultsGrid_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles resultsGrid.RowCommand
        Dim x As Integer = 0
        If e.CommandName.Equals("Select_Override") Then
            x = CInt(e.CommandArgument)
            Dim HyperLinkFromGrid As New HyperLink
            HyperLinkFromGrid = resultsGrid.Rows(x).Cells(0).Controls(0)
            Dim ARID As String = HyperLinkFromGrid.Text
            Session("ARID") = ARID
            Session("PORT") = resultsGrid.Rows(x).Cells.Item(4).Text
            Session("OverrideDataTableCreationFlag") = False
            Session("CurrentReqStatusID") = CInt(resultsGrid.Rows(x).Cells.Item(resultsGrid.Columns.Count - 2).Text)
            'Destroy all Sessions
            Session("TYPE") = Nothing
            Session("SearchCriteria") = Nothing
            Response.Redirect("Override_Request.aspx")
        ElseIf e.CommandName.Equals("Select_Followup") Then
            x = CInt(e.CommandArgument)
            Dim HyperLinkFromGrid As New HyperLink
            HyperLinkFromGrid = resultsGrid.Rows(x).Cells(0).Controls(0)
            Dim ARID As String = HyperLinkFromGrid.Text
            Session("ARID") = ARID
            Session("PORT") = resultsGrid.Rows(x).Cells.Item(4).Text
            Dim status As Integer = CInt(resultsGrid.Rows(x).Cells.Item(14).Text)
            'Destroy all Sessions
            Session("TYPE") = Nothing
            Session("SearchCriteria") = Nothing
            If status = 3 Then
                Response.Redirect("Followup_Request.aspx")
            ElseIf status = 9 Then
                Response.Redirect("OverrideFollowupRequest.aspx")
            End If
        ElseIf e.CommandName.Equals("Cancel") Then
            x = CInt(e.CommandArgument)
            Dim HyperLinkFromGrid As New HyperLink
            HyperLinkFromGrid = resultsGrid.Rows(x).Cells(0).Controls(0)
            Dim ARID As String = HyperLinkFromGrid.Text
            Session("ARID") = ARID
            'Destroy all Sessions
            Session("TYPE") = Nothing
            Session("SearchCriteria") = Nothing
            Response.Redirect("CancelRequest.aspx")
        End If
    End Sub

Here is the data access layer class

[CODE]
Imports System.Data.OracleClient
Imports ATVS.Initiation.Entities
Imports Microsoft.ApplicationBlocks.Data
Imports Microsoft.ApplicationBlocks.ExceptionManagement

Public Class SortSearchDA

    Public Function SearchRequests(ByVal searchCriteria As SearchCriteria, ByVal SortExpression As String, ByVal SortDirection As Integer) As List(Of SearchResults)
        Dim searchs As New List(Of SearchResults)

        Try

            Dim SQLStr As String = ""
            SQLStr = PrepareSelectStatement(SQLStr, searchCriteria)
            If Not SortExpression.Equals(String.Empty) Then
                SQLStr += " ORDER BY " + SortExpression + " "
                If SortDirection = 0 Then
                    SQLStr += "ASC"
                Else
                    SQLStr += "DESC"
                End If
            End If
            Dim dsResult As New System.Data.DataSet
            dsResult = OracleHelper.ExecuteDataset(StoredProcedure.strConnection, CommandType.Text, SQLStr)
            Dim tbl As DataTable = dsResult.Tables(0)
            For Each row As DataRow In tbl.Rows
                Dim search As New SearchResults
                Dim dsGroup As New DataSet
                search = prepareSearchEntity(row)
                Dim SQLStr2 As String = "SELECT DISTINCT RE.ROLE_ID FROM T_RTT_TVS_ROLE_EMPLOYEE RE WHERE RE.VTMS_ROLE_OR_LOGIN='" + search.InitiatorName.ToUpper() + "'"
                dsGroup = OracleHelper.ExecuteDataset(StoredProcedure.strConnection, CommandType.Text, SQLStr2)
                Dim table As New DataTable
                table = dsGroup.Tables(0)
                If dsGroup.Tables(0).Rows.Count > 0 Then
                    search.InitiatorGroup = CInt(dsGroup.Tables(0).Rows(0).Item(0).ToString())
                End If
                searchs.Add(search)
            Next
        Catch ex As ApplicationException

        End Try
        Return searchs
    End Function
    Public Function prepareSearchEntity(ByVal row As DataRow) As SearchResults
        Dim search As New SearchResults
        If Not IsDBNull(row) Then
            search.ARID = row("ARID")
            If IsDBNull(row("EVALUATOR")) Then
                search.EvaluatorName = ""
            Else
                search.EvaluatorName = row("EVALUATOR")
            End If
            If Not IsDBNull(row("EXPIRATION_DATE")) Then
                search.Expirydate = row("EXPIRATION_DATE")
                Dim expiry As TimeSpan = search.Expirydate.Subtract(Now)
                If expiry.Days > 0 Then
                    search.DaysLeft = expiry.Days
                Else
                    search.DaysLeft = 0
                End If
            End If
            If Not IsDBNull(row("created_date")) Then
                search.InitiationDate = row("created_date")
            End If
            search.InitiatorName = row("INITIATOR_LOGIN")
            search.LloydeNo = row("LLOYDS_NBR")
            search.Port = row("PORT_NAME")
            search.Status = row("REQUEST_STATUS_NAME")
            search.TankerName = row("CURRENT_NAME")
            search.StatusID = row("REQUEST_STATUS_ID")
            'If Not IsDBNull(row("YN_INITIATOR_REPLY")) Then
            Dim IsReplied As Char = row("YN_INITIATOR_REPLY")
            If IsReplied.Equals(CChar("Y")) Then
                search.IsInitiatorReply = True
            Else
                search.IsInitiatorReply = False
            End If
        End If
        If Not IsDBNull(row("DEADWT_TONNAGE")) Then
            search.DWT = row("DEADWT_TONNAGE")
        Else
            search.DWT = 0
        End If
        'search.InitiatorGroup = row("ROLE_ID")
        Return search
    End Function
        Private Function PrepareSelectStatement(ByVal SQLStr As String, ByVal searchCriteria As SearchCriteria) As String
        SQLStr = "SELECT DISTINCT ar.ARID,ar.INITIATOR_LOGIN,ar.LLOYDS_NBR,TAN.DEADWT_TONNAGE,TAN.CURRENT_NAME,ar.SUBMITTEDDATE created_date,ar.REQUEST_STATUS_ID,"
        SQLStr += "ars.REQUEST_STATUS_NAME,ar.EXPIRATION_DATE, ar.EVALUATOR,"
        SQLStr += "( (CASE WHEN (V.TVS_ACC_ID > 0 OR AR.EXPIRATION_DATE < SYSDATE) THEN 'EXPIRED'"
        SQLStr += "ELSE DECODE(AR.REQUEST_STATUS_ID, 4,'VALID') END)) AS STATUS,"
        SQLStr += "( (CASE WHEN (V.TVS_ACC_ID > 0 OR AR.EXPIRATION_DATE < SYSDATE) THEN ''"
        SQLStr += "ELSE DECODE(AR.REQUEST_STATUS_ID, 3,'Follow up') END)) AS DISPLAY_TEXT,"
        SQLStr += "planned.SAUDI_PORT_CODE,port.PORT_NAME,ars.REQUEST_STATUS_NAME AS STATUS_NAME, AR.YN_INITIATOR_REPLY"
        SQLStr += " FROM   RTTDBA.T_RTT_ACCEPTANCE_REQUEST ar,RTTDBA.T_RTT_ACCEPTANC_REQUEST_STATUS ars,RTTDBA.T_RTT_TANKER TAN,"
        SQLStr += "RTTDBA.T_RTT_VISIT V,RTTDBA.T_RTT_PLANNED_VISIT planned, RTTDBA.T_RTT_SAUDI_PORT port"
        SQLStr += " WHERE  ar.REQUEST_STATUS_ID = ars.REQUEST_STATUS_ID AND"
        SQLStr += " ar.LLOYDS_NBR = TAN.LLOYDS_NBR AND"
        SQLStr += " V.TVS_ACC_ID(+) = AR.ARID AND"
        SQLStr += " planned.SAUDI_PORT_CODE = port.SAUDI_PORT_CODE AND"
        SQLStr += " ar.ARID ="
        If searchCriteria.ARID = 0 Then
            SQLStr += "ar.ARID"
        Else
            SQLStr += searchCriteria.ARID.ToString()
        End If
        SQLStr += " AND planned.ARID = ar.ARID"
        SQLStr += " AND ar.LLOYDS_NBR ="
        If searchCriteria.LloydsNo = 0 Then
            SQLStr += "ar.LLOYDS_NBR "
        Else
            SQLStr += searchCriteria.LloydsNo.ToString()
        End If
        SQLStr += " AND ar.MODIFIED_DATE >="
        If searchCriteria.InitiationDateFrom.Equals(CDate("1/1/1900")) Then
            SQLStr += "ar.MODIFIED_DATE"
        Else
            SQLStr += searchCriteria.InitiationDateFrom.ToString()
        End If
        SQLStr += " AND ar.MODIFIED_DATE <="
        If searchCriteria.InitiationDateTo.Equals(CDate("1/1/1900")) Then
            SQLStr += "ar.MODIFIED_DATE"
        Else
            SQLStr += searchCriteria.InitiationDateTo.ToString()
        End If
        SQLStr += " AND lower(ar.INITIATOR_LOGIN) LIKE '%'||"
        If searchCriteria.InitiatorName.Equals(String.Empty) Then
            SQLStr += "lower(ar.INITIATOR_LOGIN)||'%'"
        Else
            SQLStr += "lower(" + searchCriteria.InitiatorName + ")||'%'"
        End If

        SQLStr += " AND ars.REQUEST_STATUS_ID ="
        If searchCriteria.Status = 0 Then
            SQLStr += "ars.REQUEST_STATUS_ID"
        Else
            SQLStr += searchCriteria.Status
        End If
        SQLStr += " AND lower(TAN.CURRENT_NAME) LIKE '%'||"
        If searchCriteria.TankerName.Equals(String.Empty) Then
            SQLStr += "lower(TAN.CURRENT_NAME)||'%'"
        Else
            SQLStr += "lower(" + searchCriteria.TankerName + ")||'%'"
        End If
        Return SQLStr
    End Function
End Class
[/CODE]


and last but not least the Business Logic Layer

[CODE]Imports ATVS.Common
Imports ATVS.Initiation.Entities
Imports ATVS.Initiation.DAL
Imports ATVS.Initiation.DAL.TnakerInfoDA
Public Class SortSearchManager
    Public Function getRequests(ByVal searchCriteria As SearchCriteria, ByVal SearchType As String, ByVal UserGroup As Integer, ByVal SortExpression As String, ByVal SortDirection As Integer) As List(Of SearchResults)
        Dim results As New List(Of SearchResults)
        Dim Search As New SortSearchDA
        If SearchType = "" Then
            Return results
        End If
        results = Search.SearchRequests(searchCriteria, SortExpression, SortDirection)
        'Apply filtering if user group is basic
        If UserGroup = UserRole.INITIATOR_BASIC Then
            results = FilterSearch(results, UserGroup)
        End If
        'Apply Filtering if the search is by override or followup
        If SearchType.Equals("OVERRIDE") Or SearchType.Equals("FOLLOWUP") Then
            results = FilterSearch(results, SearchType, searchCriteria)
        End If

        'results(0).DaysLeft = results(0).Expirydate.Day - results(0).InitiationDate.Day
        Return results
    End Function
    Private Function FilterSearch(ByVal results As List(Of SearchResults), ByVal SearchType As String, ByVal searchCriteria As SearchCriteria) As List(Of SearchResults)
        Dim TempResults As New List(Of SearchResults)
        If SearchType.Equals("OVERRIDE") Then
            For Each result As SearchResults In results
                If result.StatusID = 4 Or result.StatusID = 5 Or result.StatusID = 6 Then
                    If searchCriteria.InitiationDateFrom.Equals(CDate("1/1/1900")) And searchCriteria.InitiationDateTo.Equals(CDate("1/1/1900")) Then
                        If Not result.InitiationDate < Now.AddMonths(-1) Then
                            TempResults.Add(result)
                        End If
                    Else
                        TempResults.Add(result)
                    End If
                End If
            Next
        ElseIf SearchType.Equals("FOLLOWUP") Then
            For Each result As SearchResults In results
                If result.StatusID = 3 Or result.StatusID = 9 Then
                    If searchCriteria.InitiationDateFrom.Equals(CDate("1/1/1900")) And searchCriteria.InitiationDateTo.Equals(CDate("1/1/1900")) Then
                        If Not result.InitiationDate < Now.AddMonths(-1) Then
                            TempResults.Add(result)
                        End If
                    Else
                        TempResults.Add(result)
                    End If
                End If
            Next
        End If
        results = New List(Of SearchResults)
        results = TempResults
        Return results
    End Function
    Private Function FilterSearch(ByVal results As List(Of SearchResults), ByVal UserGroup As Integer) As List(Of SearchResults)
        Dim TempResults As New List(Of SearchResults)
        For Each Result As SearchResults In results
            If Result.InitiatorGroup = UserRole.INITIATOR_BASIC Then
                TempResults.Add(Result)
            End If
        Next
        Return TempResults
    End Function
End Class
[/CODE]

Guys, Your help is highly appreciated and I'm sorry in advance for the amount of code I included but I would like to make it as clear as possible for every one.

I'm sorry also for my weak english

PS: I'm trying to find a way to sort the grid using ObjectDataSource for weeks and this is what I got and I'm new to all of this I'm originally a Java Developer !!

Mazen
Page 1 of 1 (1 items)