chime
Member
Hi
I have a complex view that pulls data down into this format
ClientID, ClientName, FundID, ProductID
Each Client can have more than one fund
Each fund can have more than one Product
Client1, MyClient1, Fund1, 101
Client1, MyClient1, Fund2, 101
Client1, MyClient1, Fund2, 301
Client2, MyClient2, Fund3, 101
Client3, MyClient3, Fund4, 101
Client3, MyClient3, Fund6, 302
Client4, MyClient4, Fund8, 503
I want to add a column for each fund (there are only 6 type) into the datagrid
which I do with this code
This works perfectly in that it give a datagrids
Client1, MyClient1, Fund1, 101 - - - - -
Client1, MyClient1, Fund2, 101 - - - - -
Client1, MyClient1, Fund2, - - 301 - - -
Client2, MyClient2, Fund3, 101 - - - - -
Client3, MyClient3, Fund4, 101 - - - - -
Client3, MyClient3, Fund4, - - - - - 503
Client4, MyClient4, Fund1, - - - - - 503
But what I want is the following
Client1, MyClient1, Fund1, 101 - - - - -
Client1, MyClient1, Fund2, 101 - 301 - - -
Client2, MyClient2, Fund3, 101 - - - - -
Client3, MyClient3, Fund4, 101 - - - - 503
Client4, MyClient4, Fund1, - - - - - 503
This is the rest of my code
I have a complex view that pulls data down into this format
ClientID, ClientName, FundID, ProductID
Each Client can have more than one fund
Each fund can have more than one Product
Client1, MyClient1, Fund1, 101
Client1, MyClient1, Fund2, 101
Client1, MyClient1, Fund2, 301
Client2, MyClient2, Fund3, 101
Client3, MyClient3, Fund4, 101
Client3, MyClient3, Fund6, 302
Client4, MyClient4, Fund8, 503
I want to add a column for each fund (there are only 6 type) into the datagrid
which I do with this code
VB.NET:
Private Sub DataGridClientProducts_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGridClientProducts.ItemDataBound
Try
' process data rows only (skip the header, footer etc.)
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
' Fund Accounting
If e.Item.Cells(4).Text = "502" Then
e.Item.Cells(6).Text() = " FA "
Else
e.Item.Cells(6).Text() = " - "
End If
' Transfer Agency
If e.Item.Cells(4).Text = "101" Then
e.Item.Cells(7).Text() = " TA "
Else
e.Item.Cells(7).Text() = " - "
End If
' Trustee and Custodial
If e.Item.Cells(4).Text = "304" Then
e.Item.Cells(8).Text() = " T&C "
Else
e.Item.Cells(8).Text() = " - "
End If
' Trustee Only
If e.Item.Cells(4).Text = "301" Then
e.Item.Cells(9).Text() = " T "
Else
e.Item.Cells(9).Text() = " - "
End If
' Custody only
If e.Item.Cells(4).Text = "302" Then
e.Item.Cells(10).Text() = " C "
Else
e.Item.Cells(10).Text() = " - "
End If
' Middle Office
If e.Item.Cells(4).Text = "503" Then
e.Item.Cells(11).Text() = " MO "
Else
e.Item.Cells(11).Text() = " - "
End If
End If
Catch ex As Exception
End Try
End SubThis works perfectly in that it give a datagrids
Client1, MyClient1, Fund1, 101 - - - - -
Client1, MyClient1, Fund2, 101 - - - - -
Client1, MyClient1, Fund2, - - 301 - - -
Client2, MyClient2, Fund3, 101 - - - - -
Client3, MyClient3, Fund4, 101 - - - - -
Client3, MyClient3, Fund4, - - - - - 503
Client4, MyClient4, Fund1, - - - - - 503
But what I want is the following
Client1, MyClient1, Fund1, 101 - - - - -
Client1, MyClient1, Fund2, 101 - 301 - - -
Client2, MyClient2, Fund3, 101 - - - - -
Client3, MyClient3, Fund4, 101 - - - - 503
Client4, MyClient4, Fund1, - - - - - 503
This is the rest of my code
VB.NET:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
'If its loaded for the first time
Session("FilterClientID") = "%"
Session("SelectedClientIDIndex") = 0
' fill the ClientNames DropDownList
fill_ClientNames()
' Enable the drop down list and check the check boxes
DropDownListClientNames.Enabled = True
' set the variables for the sorting of the Finance datagrid
Session("SortField") = "ClientName"
strSortField = "ClientName"
Session("SortOrder") = "Descending"
Else
Dim EventSource As String = Nothing
' If its a postback (not the initial page load)
EventSource = Request.Form("__EVENTTARGET")
' Which Event called the page load postback
If EventSource Like "DataGridClientProducts:_ctl#:_ctl#" Then
'If the Finance datagrid caused the postback
boolSortFlag = True
Else
boolSortFlag = False
End If
End If
' fill the Client Products DataGrid
fill_ClientProducts()
' Filter with the drop down lists
ApplyFilter()
End Sub
' Fill the drop down list
Private Sub fill_ClientNames()
' Reset all the filters to select all
' Set the FilterClientNames to %
If (Session("FilterClientID").ToString = "Client Names") Then
Session("FilterClientID") = "%"
End If
Try
' Run the select statement to collect data to fill the dataset
SqlSelectCommandClientNames.CommandText = "SELECT DISTINCT ClientID, ClientName " & _
"FROM vClientLobsidFundPeriod " & _
"ORDER BY ClientName"
DataSetClientNames.Clear()
' Fill the dataset
SqlDataAdapterClientNames.Fill(DataSetClientNames)
' Display the data in the datasource
DropDownListClientNames.DataSource = DataSetClientNames
DropDownListClientNames.DataBind()
'Add a new listitem to the beginning of the Drop Down list
DropDownListClientNames.Items.Insert(0, New ListItem("Client Names"))
Catch ex As Exception
End Try
End Sub
' Fill the drop down list
Private Sub fill_ClientProducts()
Try
' Run the select statement to collect data to fill the dataset
SqlSelectCommandClientProducts.CommandText = "SELECT ClientID, ClientName, EntityID, EntityName, ProductID, ProductName " & _
"FROM vClientProducts"
DataSetClientProducts.Clear()
' Fill the dataset
SqlDataAdapterClientProducts.Fill(DataSetClientProducts)
Catch ex As Exception
End Try
End Sub
Private Sub Refresh_ClientNames()
'If the ClientID dropdown list is selected
If DropDownListClientNames.SelectedIndex <> -1 Then
Session("FilterClientID") = DropDownListClientNames.SelectedItem.Value
Session("SelectedClientIDIndex") = DropDownListClientNames.SelectedIndex
Else
Session("SelectedClientIDIndex") = 0
End If
End Sub
' Choose a new Client ID to filter
Private Sub DropdownlistClientNames_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DropDownListClientNames.SelectedIndexChanged
' Refresh the ClientNames DropDownList
Refresh_ClientNames()
End Sub
' Apply the filters from the drop down lists
Private Sub ApplyFilter()
Try
Dim strField As String
' Which client ID has been selected
Session("ClientIDColumnFilter") = "%"
' and Sort the fields
strField = Session("SortField").ToString
' and bind the data to the datagrid
BindGrid(strField)
Catch ex As Exception
End Try
End Sub
' Bind the filtered and sorted data to the datagrid
Public Sub BindGrid(ByVal SortField As Object)
Dim strSortOrder As String = Session("SortOrder").ToString
' Create a DataView based on the only DataTable in DataSetUsers
'Dim myDataView As DataView = DataSetInvoiceHistory.Tables(0).DefaultView
myDataView = New DataView(DataSetClientProducts.Tables(0))
Try
' Page reload called from the datagrid so we have to check and/or change the sorting direction
If boolSortFlag = True Then
' If the field is clicked again change the sort direction
If Session("SortField").ToString = strSortField Then
' Change from ascending to descending
If Session("SortOrder").ToString = "Ascending" Then
myDataView.Sort = strSortField & " DESC"
Session("SortOrder") = "Descending"
Else
' Change from descending to ascending
If Session("SortOrder").ToString = "Descending" Then
myDataView.Sort = strSortField & " ASC"
Session("SortOrder") = "Ascending"
End If
End If
Else
myDataView.Sort = strSortField & " ASC"
Session("SortOrder") = "Ascending"
End If
' Change the session sort field
Session("SortField") = strSortField
Else
Try
' sort by this field
myDataView.Sort = "ClientName ASC"
' set the default sort field
Session("SortField") = "ClientName"
Session("SortOrder") = "Ascending"
Catch ex As Exception
End Try
End If
' Display the field you are sorting and its direction
lblSortField.Text = "Sorting on " & Session("SortField").ToString & " in " & Session("SortOrder").ToString & " Order."
' Filter out rows according to ClientName, Losbid, FundID and Period
Row_filter()
myDataView.RowFilter = strRowFilter
' Bind the DataGrid to the sorted DataView.
' But the datagrid properties must be empty for the datagrid to sort on the view (not the dataset)
DataGridClientProducts.DataSource = myDataView
DataGridClientProducts.DataBind()
Catch ex As Exception
End Try
Try
' Count the rows of data
Dim strCount As Integer = myDataView.Count
' Show the No Records warning label and
' hide the sort fields label
If strCount = 0 Then
lblNoRecords.Visible = True
lblSortField.Visible = False
DataGridClientProducts.Visible = False
Else
' Hide the No Records warning label and
' show the sort fields label
lblNoRecords.Visible = False
lblSortField.Visible = True
DataGridClientProducts.Visible = True
End If
Catch ex As Exception
End Try
End Sub
' Set the filter variables
Public Sub Row_filter()
Try
Dim strClientIDFilter As String = Trim(Session("ClientIDColumnFilter").ToString)
' filter on ClientID
If strClientIDFilter = "Client ID" Then
' Filter the dataview to show all ClientIDs
strClientIDFilter = "ClientID LIKE '%'"
Else
' Filter the dataview to only show ClientIDs starting with the Session("ClientIDColumnFilter") variable
strClientIDFilter = "ClientID LIKE '" & strClientIDFilter & "%'"
End If
' delete these lines when working filter
strClientIDFilter = ""
strClientIDFilter = "ClientID LIKE '" & strClientIDFilter & "%'"
' filter the data on the filter columns
strRowFilter = "(" & strClientIDFilter & ")"
'strRowFilter = "(" & strClientIDFilter & " AND " & strFundIDFilter & ")"
lblFilter.Text = strRowFilter
Catch ex As Exception
End Try
End Sub
Private Sub DataGridClientProducts_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGridClientProducts.SortCommand
Try
' fill the datagrid
fill_ClientProducts()
'The DataGridSort function takes the event arguments object DataGridSortCommandEventArgs E,
' from the OnSortCommand method in the DataGrid control, and passes it to BindGrid.
strSortField = e.SortExpression
BindGrid(strSortField)
Catch ex As Exception
End Try
End Sub
Private Sub DataGridClientProducts_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGridClientProducts.ItemDataBound
Try
' process data rows only (skip the header, footer etc.)
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
' Fund Accounting
If e.Item.Cells(4).Text = "502" Then
e.Item.Cells(6).Text() = " FA "
Else
e.Item.Cells(6).Text() = " - "
End If
' Transfer Agency
If e.Item.Cells(4).Text = "101" Then
e.Item.Cells(7).Text() = " TA "
Else
e.Item.Cells(7).Text() = " - "
End If
' Trustee and Custodial
If e.Item.Cells(4).Text = "304" Then
e.Item.Cells(8).Text() = " T&C "
Else
e.Item.Cells(8).Text() = " - "
End If
' Trustee Only
If e.Item.Cells(4).Text = "301" Then
e.Item.Cells(9).Text() = " T "
Else
e.Item.Cells(9).Text() = " - "
End If
' Custody only
If e.Item.Cells(4).Text = "302" Then
e.Item.Cells(10).Text() = " C "
Else
e.Item.Cells(10).Text() = " - "
End If
' Middle Office
If e.Item.Cells(4).Text = "503" Then
e.Item.Cells(11).Text() = " MO "
Else
e.Item.Cells(11).Text() = " - "
End If
End If
Catch ex As Exception
End Try
End Sub
End Class
