T
tfsmag
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-----
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-----