dynamically created gridview sorting/paging

Discussion in 'ASP .Net' started by tfsmag, Mar 13, 2006.

  1. tfsmag

    tfsmag Guest

    Hello,

    I have a function that returns a dynamically created gridview. This
    works fine, however it does not seem to be able to maintain state when
    adding sorting or paging to the gridview. Does anyone have any idea how
    to get this to work? below is the code. Please bear in mind that the
    function is actually located in a seperate class file from the page
    that actually returns the grid.

    ---code for function that returns the grid, this is located in an
    object called "totalincurred"---
    Function GenerateTotIncurredGrid(ByVal view_type As String)
    Dim thegrid As New GridView
    Dim theDataSet_ As New DataSet
    Dim theSessionVars_ As New ICE_SessionVars

    'our parameter list

    'first we'll build the gridview properties
    thegrid.ShowHeader = True
    thegrid.EnableTheming = True
    thegrid.SkinID = "ClaimsGrids"
    thegrid.AutoGenerateColumns = False

    'now let's create the common columns for the grid
    'Dim bcTotalPaid2 As New TemplateField()
    Dim bcTotalPaid, bcOutReserve, bcTotRecovered,
    bcTotIncurred, bcNoOfClaims, bcCostPerClaim, bcOpenedClaims,
    bcClosedClaims As New BoundField()
    bcTotalPaid.Equals("total_paid")
    bcTotalPaid.DataField = "total_paid"
    bcTotalPaid.HeaderText = "Total Paid"
    bcTotalPaid.HtmlEncode = False

    bcOutReserve.DataField = "outstanding_reserve"
    bcOutReserve.HeaderText = "Out. Reserve"
    bcOutReserve.HtmlEncode = False

    bcTotRecovered.DataField = "total_recovered"
    bcTotRecovered.HeaderText = "Tot. Recovered"
    bcTotRecovered.HtmlEncode = False

    bcTotIncurred.DataField = "total_incurred"
    bcTotIncurred.HeaderText = "Tot. Incurred"
    bcTotIncurred.HtmlEncode = False

    bcNoOfClaims.DataField = "claim_count"
    bcNoOfClaims.HeaderText = "# Of Claims"

    bcCostPerClaim.DataField = "cost_per_claim"
    bcCostPerClaim.HeaderText = "Incurred $ Per Claim"
    bcCostPerClaim.HtmlEncode = False

    bcOpenedClaims.DataField = "open_count"
    bcOpenedClaims.HeaderText = "Open Claims"

    bcClosedClaims.DataField = "closed_count"
    bcClosedClaims.HeaderText = "Closed Claims"


    'let's create the "grouped by" column for the grid'
    'current requirements: by year of loss, by location, for
    each month of last year, for each month closed, by cov code, by state
    'by severity class, by policy period
    Dim GroupedColumn As New BoundField()
    Dim WhereClause As String
    Dim strGrouping As String
    Dim strGroupColumnName As String
    Dim strFirstCol As String
    WhereClause = " WHERE c.association_number='" &
    theSessionVars_.accessAssnNumber.ToString & "' "
    Select Case view_type
    Case "YearOfLoss"
    thegrid.AllowSorting = False
    thegrid.AllowPaging = False
    thegrid.ShowFooter = True
    GroupedColumn.DataField = "year"
    GroupedColumn.HeaderText = "Year"
    WhereClause = WhereClause & "AND date_of_loss
    BETWEEN '1/1/" & Now.AddYears(-6).Year.ToString() & "' AND '12/31/" &
    Now.Year.ToString() & "' "
    strGrouping = "GROUP BY DATENAME(year,
    date_of_loss), YEAR(date_of_loss) ORDER BY year"
    strGroupColumnName = "Year"
    strFirstCol = "DATENAME(year, date_of_loss) AS
    year,"
    Case "ByLocation"
    thegrid.AllowPaging = False
    'thegrid.PageSize = 25
    thegrid.AllowSorting = True
    thegrid.ShowFooter = False
    GroupedColumn.SortExpression = "member_name"
    GroupedColumn.DataField = "member_name"
    GroupedColumn.HeaderText = "Location"
    WhereClause = "INNER JOIN MSICLAIM.dbo.member m on
    c.location=m.location AND c.association_number=m.association_number AND
    c.member_number=m.member_number " & WhereClause & " AND date_of_loss
    BETWEEN '1/1/" & Now.AddYears(-1).Year.ToString() & "' AND GETDATE()"
    strGrouping = "GROUP BY
    m.member_name,m.location,m.member_number"
    strGroupColumnName = "Location"
    strFirstCol = "m.member_name as
    member_name,m.location,m.member_number,"
    Case "ByMonth"
    thegrid.AllowSorting = False
    thegrid.AllowPaging = False
    thegrid.ShowFooter = True
    GroupedColumn.DataField = "month"
    GroupedColumn.HeaderText = "Month"
    WhereClause = WhereClause & "AND date_of_loss
    BETWEEN '1/1/" & Now.AddYears(-1).Year.ToString() & "' AND '12/31/" &
    Now.AddYears(-1).Year.ToString() & "' "
    strGrouping = "GROUP BY DATENAME(month,
    date_of_loss), MONTH(date_of_loss) ORDER BY monthnumber"
    strGroupColumnName = "Month"
    strFirstCol = "DATENAME(month, date_of_loss) AS
    month , MONTH(date_of_loss) AS MonthNumber,"
    Case "ByMonthClosed"
    thegrid.AllowSorting = False
    thegrid.AllowPaging = False
    thegrid.ShowFooter = True
    GroupedColumn.DataField = ""
    WhereClause = ""
    Case "CoverageCode"
    thegrid.AllowSorting = True
    thegrid.AllowPaging = True
    thegrid.ShowFooter = True
    GroupedColumn.DataField = ""
    WhereClause = ""
    Case "ByState"
    thegrid.AllowSorting = True
    thegrid.AllowPaging = True
    thegrid.ShowFooter = True
    GroupedColumn.DataField = ""
    WhereClause = ""
    Case "SeverityClass"
    thegrid.AllowSorting = True
    thegrid.AllowPaging = True
    thegrid.ShowFooter = True
    GroupedColumn.DataField = ""
    WhereClause = ""
    Case "PolicyPeriod"
    thegrid.AllowPaging = False
    thegrid.AllowSorting = False
    thegrid.ShowFooter = True
    GroupedColumn.DataField = ""
    End Select

    'now lets add our columns to the grid
    'bcOutReserve, bcTotRecovered, bcTotIncurred, bcNoOfClaims,
    bcCostPerClaim, bcOpenedClaims, bcClosedClaims
    thegrid.Columns.Add(GroupedColumn)
    thegrid.Columns.Add(bcTotalPaid)
    thegrid.Columns.Add(bcOutReserve)
    thegrid.Columns.Add(bcTotRecovered)
    thegrid.Columns.Add(bcTotIncurred)
    thegrid.Columns.Add(bcNoOfClaims)
    thegrid.Columns.Add(bcCostPerClaim)
    thegrid.Columns.Add(bcOpenedClaims)
    thegrid.Columns.Add(bcClosedClaims)

    'populate dataset
    theCommand_.Parameters.Clear()
    theCommand_.CommandType = CommandType.StoredProcedure
    theCommand_.CommandText = "proc_ICE_PortalFetchTotIncurred"

    If strFirstCol = "" Then
    strFirstCol = "DATENAME(month, date_of_loss) AS
    month,MONTH(date_of_loss) AS MonthNumber,"
    End If
    If strGrouping = "" Then
    strGrouping = "GROUP BY DATENAME(month, date_of_loss),
    MONTH(date_of_loss)"
    End If

    theCommand_.Parameters.Add("@assn", SqlDbType.VarChar,
    8).Value = theSessionVars_.accessAssnNumber.ToString()
    theCommand_.Parameters.Add("@whereclause",
    SqlDbType.VarChar, 2000).Value = WhereClause
    theCommand_.Parameters.Add("@grouping", SqlDbType.VarChar,
    200).Value = strGrouping
    theCommand_.Parameters.Add("@firstcol", SqlDbType.VarChar,
    500).Value = strFirstCol

    theDataSet_ = theDataUtil_.ExecuteQueryDS(theCommand_)

    'apply the datasource to the grid
    thegrid.DataSource = theDataSet_
    thegrid.DataBind()

    Dim drow As GridViewRow
    Dim TotalPaid As Decimal
    Dim OutReserve As Decimal
    Dim TotalRecovered As Decimal
    Dim TotalIncurred As Decimal
    Dim ClaimCount As Integer
    Dim CostPerClaim As Decimal
    Dim RowCounter As Integer
    For Each drow In thegrid.Rows

    'now let's build the footers for the gridview (to
    contain the totals) if it has a footer

    If thegrid.ShowFooter = True Then
    TotalPaid +=
    Convert.ToDecimal(drow.Cells.Item(1).Text)
    OutReserve +=
    Convert.ToDecimal(drow.Cells.Item(2).Text)
    TotalRecovered +=
    Convert.ToDecimal(drow.Cells.Item(3).Text)
    TotalIncurred +=
    Convert.ToDecimal(drow.Cells.Item(4).Text)
    ClaimCount +=
    Convert.ToInt32(drow.Cells.Item(5).Text)
    CostPerClaim +=
    Convert.ToDecimal(drow.Cells.Item(6).Text)
    RowCounter += 1
    thegrid.FooterRow.Cells(0).Text =
    "<strong>Totals:</strong>"
    thegrid.FooterRow.Cells(1).Text =
    FormatCurrency(TotalPaid.ToString())
    thegrid.FooterRow.Cells(2).Text =
    FormatCurrency(OutReserve.ToString())
    thegrid.FooterRow.Cells(3).Text =
    FormatCurrency(TotalRecovered.ToString())
    thegrid.FooterRow.Cells(4).Text =
    FormatCurrency(TotalIncurred.ToString())
    thegrid.FooterRow.Cells(5).Text =
    ClaimCount.ToString()
    thegrid.FooterRow.Cells(6).Text =
    FormatCurrency((CostPerClaim / RowCounter).ToString())
    End If
    If drow.RowType = DataControlRowType.DataRow Then
    drow.Cells.Item(1).Text =
    FormatCurrency(drow.Cells.Item(1).Text)
    drow.Cells.Item(2).Text =
    FormatCurrency(drow.Cells.Item(2).Text)
    drow.Cells.Item(3).Text =
    FormatCurrency(drow.Cells.Item(3).Text)
    drow.Cells.Item(4).Text =
    FormatCurrency(drow.Cells.Item(4).Text)
    drow.Cells.Item(5).Text = drow.Cells.Item(5).Text
    drow.Cells.Item(6).Text =
    FormatCurrency(drow.Cells.Item(6).Text)
    End If
    Next


    Return thegrid

    End Function
    -------end function code--------

    ------here is the code from the page that actually returns the
    grid-----

    Dim TopTabs As New Menu
    Dim theGrid As New GridView
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles Me.Load
    TopTabs = CType(Page.Master.FindControl("TopTabs"), Menu)
    theGrid =
    tot_incFunc.GenerateTotIncurredGrid(TopTabs.SelectedValue.ToString())

    System.Diagnostics.Debug.WriteLine(TopTabs.SelectedValue.ToString())
    phGetGrid.Controls.Add(theGrid)
    AssocName.Text = theSessionVars_.accessAssnName.ToString()
    End Sub

    -------end code-----
     
    tfsmag, Mar 13, 2006
    #1
    1. Advertising

  2. tfsmag

    tfsmag Guest

    wow i'm surprised that nobody else has had this problem
     
    tfsmag, Mar 14, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gaurav Gargate
    Replies:
    2
    Views:
    580
    Gaurav Gargate
    Oct 6, 2004
  2. ck388
    Replies:
    1
    Views:
    10,700
    Brock Allen
    Jun 6, 2005
  3. ami
    Replies:
    2
    Views:
    1,227
    Valerio Santinelli
    May 30, 2007
  4. msimmons
    Replies:
    0
    Views:
    514
    msimmons
    Jul 16, 2009
  5. Korneel Vandamme

    sorting dynamically created columns in a datagrid

    Korneel Vandamme, Sep 30, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    143
    Korneel Vandamme
    Sep 30, 2004
Loading...

Share This Page