Hi,
I have created 3 level hierarchial grid using datagrid control ofasp.net andasp.net ajax v1.0.
In this I have implemented custom paging with datareaders.
If my second level grid contains only one record, my thirdlevel grid paging and sorting works fine.But if my second level grid contains more than one record, my third level grid paging and sorting stops functioning
Similarly if my first level grid contains one record, paging and sorting of records in second level grid works fine. If it contains more than one record, paging and sorting of records in second level grid stops functioning.
Without AJAX, the code is working perfectly.
Can anybody out here, figure out what is the problem and help me in fixing this issue.
Thanks in Advance
Anu
share us your code please!
Hi,
Please have my source code of it,I have connection string in web.config.I am sending you the aspx page and aspx.vb(code behind page)
This is my aspx Page
-----------
<%@. Page Language="VB" MaintainScrollPositionOnPostback="true" AutoEventWireup="false" CodeFile="HierGrid.aspx.vb" Inherits="HierGrid" %>
<%@. Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Namespace="System.Web.UI" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Contract Customer List</title>
<style type="text/css">
<!--
body {
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
}
-->
</style>
<link href="http://links.10026.com/?link=Includes/Style Sheet/hcms_jnj.css" rel="stylesheet" type="text/css">
</head>
<body>
<form id="form1" runat="server">
<table width="100%" border="0" align="center" cellspacing="0" cellpadding="0" height="32">
<tr>
<td height="34" align="center" valign="middle" class="pagetitle" width="852">
<u>Contract - Customer List</u></td>
</tr>
</table>
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" />
<DIV>
<asp:UpdatePanel ID="dgParentPanel" runat="server" UpdateMode=always>
<ContentTemplate>
<asp:DataGrid ID="dgParent" runat="server" CssClass ="grid_body" AllowCustomPaging="True" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" PageSize="10" PagerStyle-Mode="NumericPages" Width="836px">
<ItemStyle CssClass="grid_body"></ItemStyle>
<HeaderStyle CssClass="grid_header"></HeaderStyle>
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:ImageButton ID="imgBtnParent" ImageUrl="~/Images/plus.gif" CommandName="Expand" runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="CONTR_ID" SortExpression="CONTR_ID" HeaderText="Contract ID">
<ItemStyle HorizontalAlign="Left" BorderStyle =Solid BorderColor="White" Width="120px" />
<HeaderStyle Width="130px" ForeColor="Blue" />
</asp:BoundColumn>
<asp:BoundColumn DataField="CONTR_NO" SortExpression="CONTR_NO" HeaderText="Contract Number">
<ItemStyle HorizontalAlign="Left" BorderStyle =Solid BorderColor="White" Width="120px" />
<HeaderStyle Width="120px" ForeColor="Blue" />
</asp:BoundColumn>
<asp:BoundColumn DataField="CONTR_NM" SortExpression="CONTR_NM" HeaderText="Contract Name">
<ItemStyle HorizontalAlign="Left" BorderStyle =Solid BorderColor="White" Width="250px" />
<HeaderStyle Width="250px" ForeColor="Blue" />
</asp:BoundColumn>
<asp:BoundColumn DataField="CONTR_ADDR" SortExpression="CONTR_ADDR" HeaderText="Address">
<ItemStyle HorizontalAlign="Left" BorderStyle =Solid BorderColor="White" Width="250px" />
<HeaderStyle Width="250px" ForeColor="Blue" />
</asp:BoundColumn>
<asp:BoundColumn DataField="EFFTV_STRT_DT" SortExpression="EFFTV_STRT_DT" HeaderText="Effective Start Date">
<ItemStyle HorizontalAlign="Left" BorderStyle =Solid BorderColor="White" Width="10px" />
<HeaderStyle Width="100px" ForeColor="Blue" />
</asp:BoundColumn>
<asp:BoundColumn DataField="EFFTV_END_DT" SortExpression="EFFTV_END_DT" HeaderText="Effective End Date">
<ItemStyle HorizontalAlign="Left" BorderStyle =Solid BorderColor="White" Width="10px" />
<HeaderStyle Width="100px" ForeColor="Blue" />
</asp:BoundColumn>
<asp:TemplateColumn >
<ItemTemplate>
<asp:UpdatePanel ID="dgChild1Panel" runat="server" UpdateMode="Conditional" >
<ContentTemplate>
<asp:PlaceHolder ID="phParent" Visible="false" runat="server" >
</td></tr>
<tr><td colspan="7">
<asp:DataGrid ID="dgChild1" OnPageIndexChanged = "dgChild1_PageIndexChanged" OnSortCommand= "dgChild1_SortCommand" FooterStyle-CssClass ="grid_body" AllowPaging ="true" AllowCustomPaging = "true" BorderStyle = "Solid" BorderWidth="1px" BorderColor ="white" FooterStyle-Height="1px" PagerStyle-Mode="NumericPages" Width="1200px" AllowSorting="True" OnItemCommand = "dgChild1_ItemCommand" CssClass ="grid_body" ShowFooter="True" AutoGenerateColumns="false" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<ItemStyle CssClass="grid_body" ></ItemStyle>
<HeaderStyle CssClass="grid_header" BorderStyle =Solid BorderColor =white ></HeaderStyle>
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:ImageButton BorderStyle =Solid BorderColor =white ID="imgBtnChild" CommandName="ExpChild1" ImageUrl="~/Images/plus.gif" runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn Visible=false ItemStyle-BorderColor =white ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "" DataField="parnt_intract_no" SortExpression="parnt_intract_no" HeaderText="parnt_intract_no" HeaderStyle-ForeColor ="Blue" ></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "100px" DataField="INTRACT_NO" SortExpression="INTRACT_NO" HeaderText="Interact Number" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "200px" DataField="CUST_NM" SortExpression="CUST_NM" HeaderText="Customer Name" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "200px" DataField="CUST_ADDR" SortExpression="CUST_ADDR" HeaderText="Customer Address" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="Relshp_strt_dt" SortExpression="Relshp_strt_dt" HeaderText="Contract Relationship Eff. Date" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="Relshp_end_dt" SortExpression="Relshp_end_dt" HeaderText="Contract Relationship Exp. Date" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="intract_cot_cd" SortExpression="intract_cot_cd" HeaderText="COT Code" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="cust_typ" SortExpression="cust_typ" HeaderText="Customer Type" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="DDD_NO" SortExpression="DDD_NO" HeaderText="DDD Number" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="DEA_NO" SortExpression="DEA_NO" HeaderText="DEA Number" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="PHS_ELGBLT" SortExpression="PHS_ELGBLT" HeaderText="PHS Eligible" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "80px" DataField="NCI_ACCT" SortExpression="NCI_ACCT" HeaderText="NCI Account" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:TemplateColumn >
<ItemTemplate>
sdgsdgs
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn >
<ItemTemplate>
<asp:UpdatePanel ID="dgChild2Panel" runat="server" UpdateMode=always>
<ContentTemplate>
<asp:PlaceHolder ID="phChild1" Visible="false" runat="server" >
</td></tr>
<tr> <td width="7"> </td>
<td colspan="12">
<asp:DataGrid ID="dgChild2" BorderStyle = "Solid" BorderWidth="1px" BorderColor ="white" Width="1000px" FooterStyle-Height="1px" PagerStyle-Mode="NumericPages" AllowCustomPaging="True" AllowPaging="True" AllowSorting="True" ShowFooter="True" AutoGenerateColumns="false" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" CssClass ="grid_body" >
<ItemStyle BorderStyle =Solid BorderWidth =1px CssClass="grid_body"></ItemStyle>
<HeaderStyle BorderStyle =Solid BorderWidth =1px CssClass="grid_header"></HeaderStyle>
<Columns>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" ItemStyle-Width = "120px" DataField="INTRACT_NO" SortExpression="INTRACT_NO" HeaderText="Ship To Interact" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "300px" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" DataField="CUST_NM" SortExpression="CUST_NM" HeaderText="Name" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "300px" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" DataField="SHIPTO_ADDR" SortExpression="SHIPTO_ADDR" HeaderText="Address" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "200px" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" DataField="SHP_TYP" SortExpression="SHP_TYP" HeaderText="Ship To Type" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "100px" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" DataField="COT_CD" SortExpression="COT_CD" HeaderText="COT Code" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "100px" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" DataField="DDD_NO" SortExpression="DDD_NO" HeaderText="DDD Number" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
<asp:BoundColumn HeaderStyle-BorderStyle = Solid HeaderStyle-BorderColor =white HeaderStyle-BorderWidth ="1px" ItemStyle-BorderColor =white ItemStyle-HorizontalAlign= "left" ItemStyle-Width = "100px" ItemStyle-BorderStyle =Solid ItemStyle-BorderWidth ="1px" DataField="DEA_NO" SortExpression="DEA_NO" HeaderText="DEA Number" HeaderStyle-ForeColor ="Blue"></asp:BoundColumn>
</Columns>
<PagerStyle Height ="1px" NextPageText="Next >" PrevPageText="< Prev" CssClass="pager" Mode="NumericPages"></PagerStyle>
</asp:datagrid>
</asp:PlaceHolder>
</ContentTemplate>
</asp:UpdatePanel>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<PagerStyle Height ="1px" NextPageText="Next >" PrevPageText="< Prev" CssClass="pager" Mode="NumericPages"></PagerStyle>
</asp:datagrid>
</asp:PlaceHolder>
</ContentTemplate>
</asp:UpdatePanel>
</ItemTemplate>
<HeaderStyle Width="1px" />
</asp:TemplateColumn>
</Columns>
<PagerStyle NextPageText="Next >" PrevPageText="< Prev" CssClass="pager" Mode="NumericPages"></PagerStyle>
</asp:DataGrid>
</ContentTemplate>
</asp:UpdatePanel>
</DIV>
<table align="center" width="100%" >
<tr>
<td width="70px" ></td>
<td align="left">Rows per Page: <asp:TextBox id="ROWSPERPAGE" CssClass="field1" runat="server" AutoPostBack="False"></asp:TextBox>
<asp:Button id="CHANGEROWSPERPAGE" runat="server" CssClass="submit" Text="Change"></asp:Button>
</td>
</tr>
</table>
<asp:UpdateProgress ID="UpdateProgress1" runat="server">
<ProgressTemplate>
Loading...
</ProgressTemplate>
</asp:UpdateProgress>
</form>
</body>
</html>
This is my code behind page
--------------
#Region "Imported Namespaces"
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.OracleClient
Imports System.Data.OracleClient.OracleType
Imports System.Web
Imports System.Drawing
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Configuration
#End Region
Partial Class HierGrid
Inherits System.Web.UI.Page
#Region "Class level Variables"
Protected OracleConn As OracleConnection
Protected OracleCmd As OracleCommand
Protected OracleDR As OracleDataReader
Protected OracleReader As OracleDataReader
Dim StrSQL As String
Dim modifiedOrderby As String
Dim orderby As String
Dim direction As String
Dim pageindex As String
Dim maximumrows As Integer
Dim vartemp As String
Dim strParentId As String
Dim strInteractId As String
#End Region
#Region "Properties"
Protected Property dgParent_CurrentSortColumn() As String
Get
If Not ViewState("dgParentSortColumn") Is Nothing Then
Return ViewState("dgParentSortColumn").ToString
Else
ViewState("dgParentSortColumn") = "CONTR_ID"
Return "CONTR_ID"
End If
End Get
Set(ByVal value As String)
ViewState("dgParentSortColumn") = value
End Set
End Property
Protected Property dgParent_CurrentSortDirection() As String
Get
If Not ViewState("dgParentSortDirection") Is Nothing Then
Return ViewState("dgParentSortDirection").ToString
Else
ViewState("dgParentSortDirection") = "ASC"
Return "ASC"
End If
End Get
Set(ByVal value As String)
ViewState("dgParentSortDirection") = value
End Set
End Property
Protected Property dgChild1_CurrentSortColumn() As String
Get
If Not ViewState("dgChild1SortColumn") Is Nothing Then
Return ViewState("dgChild1SortColumn").ToString
Else
ViewState("dgChild1SortColumn") = "INTRACT_NO"
Return "INTRACT_NO"
End If
End Get
Set(ByVal value As String)
ViewState("dgChild1SortColumn") = value
End Set
End Property
Protected Property dgChild1_CurrentSortDirection() As String
Get
If Not ViewState("dgChild1SortDirection") Is Nothing Then
Return ViewState("dgChild1SortDirection").ToString
Else
ViewState("dgChild1SortDirection") = "ASC"
Return "ASC"
End If
End Get
Set(ByVal value As String)
ViewState("dgChild1SortDirection") = value
End Set
End Property
Protected Property dgChild2_CurrentSortColumn() As String
Get
If Not ViewState("dgChild2SortColumn") Is Nothing Then
Return ViewState("dgChild2SortColumn").ToString
Else
ViewState("dgChild2SortColumn") = "INTRACT_NO"
Return "INTRACT_NO"
End If
End Get
Set(ByVal value As String)
ViewState("dgChild2SortColumn") = value
End Set
End Property
Protected Property dgChild2_CurrentSortDirection() As String
Get
If Not ViewState("dgChild2SortDirection") Is Nothing Then
Return ViewState("dgChild2SortDirection").ToString
Else
ViewState("dgChild2SortDirection") = "ASC"
Return "ASC"
End If
End Get
Set(ByVal value As String)
ViewState("dgChild2SortDirection") = value
End Set
End Property
#End Region
#Region "Pagelevel Events"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
StrSQL = "SELECT COUNT(1) AS C FROM RCS_CONTR"
GetTotalRecords(StrSQL, dgParent)
dgParent_CurrentSortColumn = "CONTR_ID"
dgParent_CurrentSortDirection = "ASC"
dgParent.CurrentPageIndex = 0
dgParent.PageSize = 10
ActualPagedData(dgParent)
ROWSPERPAGE.Text = dgParent.PageSize.ToString()
End If
End Sub
#End Region
#Region "User Defined Methods"
Private Sub GetTotalRecords(ByVal StrSQL As String, ByVal ogrid As DataGrid)
Try
OracleConn = New OracleConnection(ConfigurationManager.AppSettings("ConnectionString").ToString)
Size=3;Max Pool Size=50;Connection Lifetime=7200;")
OracleConn.Open()
OracleCmd = New OracleCommand(StrSQL, OracleConn)
OracleCmd.CommandType = CommandType.Text
OracleDR = OracleCmd.ExecuteReader
OracleDR.Read()
ogrid.VirtualItemCount = CType(OracleDR.GetDecimal(0), Integer)
Catch ORCLExec As OracleException
Response.Write(ORCLExec.Message)
Finally
If Not OracleDR Is Nothing Then
OracleDR.Close()
End If
If Not OracleCmd Is Nothing Then
OracleCmd.Dispose()
End If
If Not OracleConn Is Nothing Then
OracleConn.Close()
End If
End Try
End Sub
Private Sub ActualPagedData(ByVal oGrid As DataGrid)
Dim modifiedOrderby As String
Dim SQLString, SQLCount As String
Try
Dim Lowlmt As Decimal = (1 + oGrid.CurrentPageIndex * oGrid.PageSize + oGrid.PageSize)
Dim Highlmt As Decimal = (1 + oGrid.CurrentPageIndex * oGrid.PageSize)
SQLString = String.Empty
If oGrid.UniqueID = dgParent.UniqueID Then
modifiedOrderby = dgParent_CurrentSortColumn & " " & dgParent_CurrentSortDirection
SQLString = " SELECT CONTR_ID,CONTR_NO,CONTR_NM,CONTR_ADDR,TO_CHAR(EFFTV_STRT_DT,'MM/DD/YYYY') AS EFFTV_STRT_DT,TO_CHAR(EFFTV_END_DT,'MM/DD/YYYY') AS EFFTV_END_DT " & _
" FROM (SELECT P.*, " & _
" ROWNUM RNUM " & _
" FROM (SELECT A.CONTR_ID,A.CONTR_NO,A.CONTR_NM, B.ADDR1||','||B.CITY||','||B.STT_ABBRD||','||B.ZIP AS CONTR_ADDR,A.EFFTV_STRT_DT,A.EFFTV_END_DT " & _
" FROM RCS_CONTR A, hcms_addr B WHERE " & _
" A.ADDR_ID = B.ADDR_ID (+) " & _
" ORDER BY " & modifiedOrderby & ") P " & _
" WHERE ROWNUM < " & Lowlmt & ")" & _
" WHERE RNUM >= " & Highlmt & ""
ElseIf oGrid.UniqueID = dgChild1.UniqueID Then
' oGrid.Height = 1000
oGrid.CurrentPageIndex = CType(Session("child1_pageindex"), Integer)
modifiedOrderby = dgChild1_CurrentSortColumn & " " & dgChild1_CurrentSortDirection
SQLString = " SELECT PARNT_INTRACT_NO,INTRACT_NO,CUST_NM,CUST_ADDR,CUST_TYP,DEA_NO,DECODE(PHS_ELGBLT,'Y','YES','N','NO') AS PHS_ELGBLT,TO_CHAR(RELSHP_STRT_DT,'MM/DD/YYYY') AS RELSHP_STRT_DT,TO_CHAR(RELSHP_END_DT,'MM/DD/YYYY') AS RELSHP_END_DT,INTRACT_COT_CD,DDD_NO,DECODE(NCI_ACCT,'Y','YES','N','NO') AS NCI_ACCT " & _
" FROM (SELECT P.*, " & _
" ROWNUM RNUM " & _
" FROM (SELECT A.PARNT_INTRACT_NO,A.INTRACT_COT_CD,D.PARM_VAL AS CUST_TYP , A.INTRACT_NO,A.CUST_NM,C.ADDR1||','||C.CITY||','||C.STT_ABBRD||','||C.ZIP AS CUST_ADDR ,A.BIO_ID,A.IN_PATNT_DEA AS DEA_NO,B.PHS_ELGBLT,B.RELSHP_STRT_DT,B.RELSHP_END_DT,DDD_NO,B.NCI_ACCT " & _
" FROM RCS_CUST A,RCS_CONTR_CUST_MAP B,hcms_addr C,RCS_LKUP D " & _
" WHERE A.INTRACT_NO = B.INTRACT_NO AND A.ADDR_ID = C.ADDR_ID(+) AND " & _
" A.CUST_TYP = D.PARM_ID (+) AND UPPER(D.PARM_NM) = 'CUSTOMER TYPE' AND "
If Session("ContractID") <> "" Then SQLString = SQLString & " B.CONTR_ID = '" & Trim(Session("ContractID")) & "'"
SQLString = SQLString & " ORDER BY " & modifiedOrderby & ") P" & _
" WHERE ROWNUM < " & Lowlmt & ")" & _
" WHERE RNUM >= " & Highlmt & ""
SQLCount = " SELECT count(*) " & _
" FROM (SELECT P.*, " & _
" ROWNUM RNUM " & _
" FROM (SELECT A.PARNT_INTRACT_NO,A.INTRACT_COT_CD,D.PARM_VAL AS CUST_TYP , A.INTRACT_NO,A.CUST_NM,C.ADDR1||','||C.CITY||','||C.STT_ABBRD||','||C.ZIP AS CUST_ADDR ,A.BIO_ID,A.IN_PATNT_DEA AS DEA_NO,B.PHS_ELGBLT,TO_CHAR(B.RELSHP_STRT_DT,'MM/DD/YYYY'),TO_CHAR(B.RELSHP_END_DT,'MM/DD/YYYY'),DDD_NO,B.NCI_ACCT " & _
" FROM RCS_CUST A,RCS_CONTR_CUST_MAP B,hcms_addr C,RCS_LKUP D " & _
" WHERE A.INTRACT_NO = B.INTRACT_NO AND A.ADDR_ID = C.ADDR_ID(+) AND " & _
" A.CUST_TYP = D.PARM_ID (+) AND UPPER(D.PARM_NM) = 'CUSTOMER TYPE' AND "
If Session("ContractID") <> "" Then SQLCount = SQLCount & " B.CONTR_ID = '" & Trim(Session("ContractID")) & "'"
SQLCount = SQLCount & " ORDER BY " & modifiedOrderby & ") P " & ")"
Try
OracleConn = New OracleConnection(ConfigurationManager.AppSettings("ConnectionString").ToString)
Pool Size=3;Max Pool Size=50;Connection Lifetime=7200;")
OracleConn.Open()
OracleCmd = New OracleCommand(SQLCount, OracleConn)
OracleCmd.CommandType = CommandType.Text
OracleReader = OracleCmd.ExecuteReader
OracleReader.Read()
oGrid.VirtualItemCount = CType(OracleReader.GetDecimal(0), Integer)
Catch OraEx As OracleException
Response.Write(OraEx.Message)
Finally
If Not OracleReader Is Nothing Then
OracleReader.Dispose()
End If
If Not OracleCmd Is Nothing Then
OracleCmd.Dispose()
End If
If Not OracleConn Is Nothing Then
OracleConn.Close()
End If
End Try
ElseIf oGrid.UniqueID = dgChild2.UniqueID Then
modifiedOrderby = dgChild2_CurrentSortColumn & " " & dgChild2_CurrentSortDirection
SQLString = " SELECT INTRACT_NO, CUST_NM , SHIPTO_ADDR ,SHP_TYP,COT_CD,DEA_NO ,DDD_NO " & _
" FROM (SELECT P.*, " & _
" ROWNUM RNUM " & _
" FROM (SELECT A.INTRACT_NO,A.CUST_NM,B.ADDR1||','||B.CITY||','||B.STT_ABBRD||','||B.ZIP AS SHIPTO_ADDR,A.INTRACT_COT_CD AS COT_CD,A.IN_PATNT_DEA AS DEA_NO,A.DDD_NO,C.PARM_VAL AS SHP_TYP FROM RCS_CUST A ,hcms_addr B , RCS_LKUP C WHERE " & _
" A.ADDR_ID = B.ADDR_ID (+) " & _
" AND A.SHP_TO_TYP = C.PARM_ID (+) "
If Session("InteractID") <> "" Then SQLString = SQLString & " and parnt_intract_no = '" & Trim(Session("InteractID")) & "'"
SQLString = SQLString & " ORDER BY " & modifiedOrderby & ") P " & _
" WHERE ROWNUM < " & Lowlmt & ")" & _
" WHERE RNUM >= " & Highlmt & ""
SQLCount = " SELECT count(*) " & _
" FROM (SELECT P.*, " & _
" ROWNUM RNUM " & _
" FROM (SELECT A.INTRACT_NO,A.CUST_NM,B.ADDR1||','||B.CITY||','||B.STT_ABBRD||','||B.ZIP AS SHIPTO_ADDR,A.INTRACT_COT_CD AS COT_CD,A.IN_PATNT_DEA AS DEA_NO,A.DDD_NO,C.PARM_VAL AS SHP_TYP FROM RCS_CUST A ,hcms_addr B , RCS_LKUP C WHERE " & _
" A.ADDR_ID = B.ADDR_ID (+) " & _
" AND A.SHP_TO_TYP = C.PARM_ID (+) "
If Session("InteractID") <> "" Then SQLCount = SQLCount & " and parnt_intract_no = '" & Trim(Session("InteractID")) & "'"
SQLCount = SQLCount & " ORDER BY " & modifiedOrderby & ") P " & ")"
Try
OracleConn = New OracleConnection(ConfigurationManager.AppSettings("ConnectionString").ToString)
Pool Size=3;Max Pool Size=50;Connection Lifetime=7200;")
OracleConn.Open()
OracleCmd = New OracleCommand(SQLCount, OracleConn)
OracleCmd.CommandType = CommandType.Text
OracleReader = OracleCmd.ExecuteReader
OracleReader.Read()
oGrid.VirtualItemCount = CType(OracleReader.GetDecimal(0), Integer)
Catch OraEx As OracleException
Response.Write(OraEx.Message)
Finally
If Not OracleReader Is Nothing Then
OracleReader.Dispose()
End If
If Not OracleCmd Is Nothing Then
OracleCmd.Dispose()
End If
If Not OracleConn Is Nothing Then
OracleConn.Close()
End If
End Try
End If
OracleConn = New OracleConnection(ConfigurationManager.AppSettings("ConnectionString").ToString)
OracleConn.Open()
OracleCmd = New OracleCommand(SQLString, OracleConn)
OracleCmd.CommandType = CommandType.Text
oGrid.DataSource = OracleCmd.ExecuteReader
oGrid.DataBind()
Catch OraEx As OracleException
Response.Write(OraEx.Message)
Finally
If Not OracleCmd Is Nothing Then
OracleCmd.Dispose()
End If
If Not OracleConn Is Nothing Then
OracleConn.Close()
End If
End Try
End Sub
Protected Sub CHANGEROWSPERPAGE_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CHANGEROWSPERPAGE.Click
Try
Session("ContractID") = ""
Dim Lowlmt As Decimal = (1 + dgParent.CurrentPageIndex * dgParent.PageSize + dgParent.PageSize)
Dim Highlmt As Decimal = (1 + dgParent.CurrentPageIndex * dgParent.PageSize)
Dim newPageIndex, newPageSize As Integer
newPageSize = CType(ROWSPERPAGE.Text, Integer)
newPageIndex = CType((Highlmt / newPageSize), Integer)
dgParent.CurrentPageIndex = newPageIndex
dgParent.PageSize = newPageSize
ActualPagedData(dgParent)
Catch ex As Exception
Response.Write("Please enter a valid integer to change the number of rows per page.")
End Try
End Sub
#End Region
#Region "ParentDataGrid Event Handlers"
Protected Sub dgParent_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgParent.ItemCommand
If e.CommandName = "Expand" Then
Dim imgBtn As ImageButton
Dim pHolder As PlaceHolder
'Before expanding, collapse previously opened child grid
Dim dgItem As DataGridItem
For Each dgItem In dgParent.Items
If dgItem.ItemIndex = ViewState("PrvIndex") And dgItem.ItemIndex <> e.Item.ItemIndex Then
imgBtn = dgItem.Cells(0).FindControl("imgBtnParent")
If imgBtn.ImageUrl = "~/Images/minus.gif" Then
imgBtn.ImageUrl = "~/Images/plus.gif"
pHolder = CType(dgItem.FindControl("phParent"), PlaceHolder)
pHolder.Visible = Not pHolder.Visible
End If
Exit For
End If
Next
' Get the contract id for which to display customers
strParentId = e.Item.Cells(1).Text
Session("ContractID") = strParentId
imgBtn = e.Item.Cells(0).FindControl("imgBtnParent")
If imgBtn.ImageUrl = "~/Images/plus.gif" Then
imgBtn.ImageUrl = "~/Images/minus.gif"
Else
imgBtn.ImageUrl = "~/Images/plus.gif"
End If
Dim exp As PlaceHolder
exp = e.Item.Cells(5).FindControl("phParent")
exp.Visible = Not exp.Visible
If exp.Visible = True Then
dgChild1.CurrentPageIndex = 0
ActualPagedData(dgParent)
End If
'Get the previous selected index in order to collapse the previous DataGrid
ViewState("PrvIndex") = e.Item.ItemIndex
End If
End Sub
Protected Sub dgParent_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgParent.ItemDataBound
' It will be called on binding each and every row of the datagrid to datasource
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
If e.Item.Cells(1).Text.Equals(Session("ContractID")) Then
Dim imgBtn As ImageButton
imgBtn = e.Item.Cells(0).FindControl("imgBtnParent")
imgBtn.ImageUrl = "~/Images/minus.gif"
Dim exp As PlaceHolder
exp = e.Item.Cells(5).FindControl("phParent")
exp.Visible = True
dgChild1.PageSize = CType(ROWSPERPAGE.Text, Integer)
'Session("InteractID") = ""
ActualPagedData(dgChild1)
End If
End If
End Sub
Protected Sub dgParent_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgParent.PageIndexChanged
dgParent.CurrentPageIndex = e.NewPageIndex
Session("ContractID") = ""
ActualPagedData(dgParent)
End Sub
Protected Sub dgParent_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgParent.SortCommand
' Dim direction As String
'Check if this is the second time the same column name is selected (this means we need to sort descending)
' Otherwise, the sort should be done ascending since this is a new column (compared to the one saved in the
' view state) that was selected.
If (dgParent_CurrentSortDirection = "ASC" And dgParent_CurrentSortColumn = e.SortExpression) Then
dgParent_CurrentSortDirection = "DESC"
Else
dgParent_CurrentSortDirection = "ASC"
End If
' Remember what column the data is sorted by.
dgParent_CurrentSortColumn = e.SortExpression
Session("ContractID") = ""
dgParent.CurrentPageIndex = 0
ActualPagedData(dgParent)
End Sub
#End Region
#Region "Second level Grid Event Handlers"
Protected Sub dgChild1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
If e.CommandName = "ExpChild1" Then
Dim imgBtnChild As ImageButton
Dim pHChild As PlaceHolder
Dim dgItem As DataGridItem
Dim dgChild As DataGrid
Dim dgItemChild As DataGridItem
For Each dgItem In dgParent.Items
dgChild = CType(dgItem.FindControl("dgChild1"), DataGrid)
For Each dgItemChild In dgChild.Items
If dgItemChild.ItemIndex = ViewState("prvChIndex") And dgItemChild.ItemIndex <> e.Item.ItemIndex Then
imgBtnChild = dgItemChild.Cells(0).FindControl("imgBtnChild")
If imgBtnChild.ImageUrl = "~/Images/minus.gif" Then
imgBtnChild.ImageUrl = "~/Images/plus.gif"
pHChild = dgItemChild.Cells(2).FindControl("pHChild1")
pHChild.Visible = Not pHChild.Visible
End If
Exit For
End If
Next
Next
strInteractId = e.Item.Cells(1).Text
Session("InteractID") = strInteractId
imgBtnChild = e.Item.Cells(0).FindControl("imgBtnChild")
If imgBtnChild.ImageUrl = "~/Images/plus.gif" Then
imgBtnChild.ImageUrl = "~/Images/minus.gif"
Else
imgBtnChild.ImageUrl = "~/Images/plus.gif"
End If
pHChild = e.Item.Cells(2).FindControl("phChild1")
pHChild.Visible = Not pHChild.Visible
If pHChild.Visible = True Then
dgChild2.CurrentPageIndex = 0
'ActualPagedData(dgChild1)
ActualPagedData(dgParent)
End If
ViewState("prvChIndex") = e.Item.ItemIndex
'pHChild.Visible = True
End If
End Sub
Protected Sub dgChild1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgChild1.Init
End Sub
Protected Sub dgChild1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgChild1.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
If e.Item.Cells(1).Text.Equals(Session("InteractID")) Then
Dim imgBtn As ImageButton
imgBtn = e.Item.Cells(0).FindControl("imgBtnChild")
imgBtn.ImageUrl = "~/Images/minus.gif"
Dim exp As PlaceHolder
exp = e.Item.Cells(5).FindControl("phChild1")
exp.Visible = True
' dgChild2.PageSize = CType(ROWSPERPAGE.Text, Integer)
dgChild2.PageSize = 1
ActualPagedData(dgChild2)
End If
End If
End Sub
Protected Sub dgChild1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)
Dim dgchild As New DataGrid
dgChild1.CurrentPageIndex = e.NewPageIndex
Session("child1_pageindex") = e.NewPageIndex
dgChild1.PageSize = CType(ROWSPERPAGE.Text, Integer)
Session("InteractID") = ""
ActualPagedData(dgChild1)
End Sub
Protected Sub dgChild1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs)
'Check if this is the second time the same column name is selected (this means we need to sort descending)
' Otherwise, the sort should be done ascending since this is a new column (compared to the one saved in the
' view state) that was selected.
If (dgChild1_CurrentSortDirection = "ASC" And dgChild1_CurrentSortColumn = e.SortExpression) Then
dgChild1_CurrentSortDirection = "DESC"
Else
dgChild1_CurrentSortDirection = "ASC"
End If
' Remember what column the data is sorted by.
dgChild1_CurrentSortColumn = e.SortExpression
'Reselect the set of data to show. Note that the page index is reset to 0.
dgChild1.CurrentPageIndex = 0
Session("InteractID") = ""
ActualPagedData(dgChild1)
End Sub
#End Region
#Region "Third level Grid Event Handlers"
Protected Sub dgChild2_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgChild2.SortCommand
'Check if this is the second time the same column name is selected (this means we need to sort descending)
' Otherwise, the sort should be done ascending since this is a new column (compared to the one saved in the
' view state) that was selected.
If (dgChild2_CurrentSortDirection = "ASC" And dgChild2_CurrentSortColumn = e.SortExpression) Then
dgChild2_CurrentSortDirection = "DESC"
Else
dgChild2_CurrentSortDirection = "ASC"
End If
' Remember what column the data is sorted by.
dgChild2_CurrentSortColumn = e.SortExpression
' Make sure the page remembers the sorting direction.
'Reselect the set of data to show. Note that the page index is reset to 0.
dgChild2.CurrentPageIndex = 0
ActualPagedData(dgChild2)
End Sub
Protected Sub dgChild2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgChild2.ItemDataBound
End Sub
Protected Sub dgChild2_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgChild2.PageIndexChanged
dgChild2.CurrentPageIndex = e.NewPageIndex
ActualPagedData(dgChild2)
End Sub
#End Region
End Class
