Heirarchy Grid Problem

[Adeel]

Hi,

I have a heirarchy grid. But I am having a problem with it.

The detail row is showing all the records from the table rather than just the ones which match the parent row.

My Grid Code is:

    <telerik:RadGrid ID="RadGrid1" DataSourceID="SessionDataSource1" runat="server" ShowStatusBar="false"
    AutoGenerateColumns="False" AllowSorting="True" AllowMultiRowSelection="False"
    AllowPaging="False" GridLines="None" ShowFooter="False" Skin="Web20"
    style="top: 5px; left: 5px; position: absolute;" Width="780px" Height="470px">
    <PagerStyle Mode="NumericPages"></PagerStyle>
    <MasterTableView DataSourceID="SessionDataSource1" DataKeyNames="TOPID" AllowMultiColumnSorting="True"
        Width="100%" CommandItemDisplay="None" Name="Customers" HierarchyDefaultExpanded="true">
        <DetailTables>
            <telerik:GridTableView DataKeyNames="BOTID" DataSourceID="SessionDataSource2" Width="100%"
                runat="server" CommandItemDisplay="None" Name="Orders">
                <ParentTableRelation>
                    <telerik:GridRelationFields DetailKeyField="BOTID" MasterKeyField="TOPID" />
                </ParentTableRelation>
                <Columns>
                    <telerik:GridBoundColumn SortExpression="BOTID" HeaderText="JournalID" HeaderButtonType="TextButton"
                        DataField="BOTID" UniqueName="BOTID" Visible="true">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="Account" HeaderText="Account" HeaderButtonType="TextButton"
                        DataField="Account" UniqueName="Account">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="AccountName" HeaderText="Account Name" HeaderButtonType="TextButton"
                        DataField="AccountName" UniqueName="AccountName">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="Debit" HeaderText="Debit" HeaderButtonType="TextButton"
                        DataField="Debit" UniqueName="Debit">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="Credit" HeaderText="Credit" HeaderButtonType="TextButton"
                        DataField="Credit" UniqueName="Credit">
                    </telerik:GridBoundColumn>
                </Columns>
                <SortExpressions>
                    <telerik:GridSortExpression FieldName="Account"></telerik:GridSortExpression>
                </SortExpressions>
            </telerik:GridTableView>
        </DetailTables>
        <Columns>

            <telerik:GridBoundColumn SortExpression="Date" HeaderText="Date" HeaderButtonType="TextButton"
                DataField="Date" UniqueName="Date">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn SortExpression="TOPID" HeaderText="JournalID" HeaderButtonType="TextButton"
                DataField="TOPID" UniqueName="TOPID" Visible="true">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn SortExpression="JournalTitle" HeaderText="Title" HeaderButtonType="TextButton"
                DataField="JournalTitle" UniqueName="JournalTitle">
            </telerik:GridBoundColumn>
        </Columns>
        <SortExpressions>
            <telerik:GridSortExpression FieldName="Date"></telerik:GridSortExpression>
        </SortExpressions>
    </MasterTableView>
</telerik:RadGrid>
<asp:SqlDataSource ID="SessionDataSource1" ConnectionString="<%$ ConnectionStrings:SASConnectionString %>"
    ProviderName="System.Data.SqlClient" runat="server"
    SelectCommand="Select DISTINCT JournalID AS TOPID, Convert(char(10),CreationDate,103) AS [Date], JournalTitle from Journal
                        Where (JournalID Like @drpJournal) AND (Journal.CreationDate >= @DateFrom) AND (Journal.CreationDate <= @DateTo)"
    >
    <SelectParameters>
        <asp:ControlParameter ControlID="drpJournal" Name="drpJournal" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="RadDatePicker1" Name="DateFrom" PropertyName="SelectedDate" Type="DateTime" />
        <asp:ControlParameter ControlID="RadDatePicker2" Name="DateTo" PropertyName="SelectedDate" Type="DateTime" />
    </SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SessionDataSource2" ConnectionString="<%$ ConnectionStrings:SASConnectionString %>"
    ProviderName="System.Data.SqlClient" runat="server"
    SelectCommand="Select JournalID AS BOTID, Journal.Account, AccountName, SUM(Debit) AS Debit, SUM(Credit) AS Credit from Journal JOIN Ledger ON Journal.Account = Ledger.Account
                        Where (JournalID Like @drpJournal) AND (Journal.CreationDate >= @DateFrom) AND (Journal.CreationDate <= @DateTo) GROUP BY JournalID, Journal.Account, AccountName"
     >
     <SelectParameters>
        <asp:ControlParameter ControlID="drpJournal" Name="drpJournal" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="RadDatePicker1" Name="DateFrom" PropertyName="SelectedDate" Type="DateTime" />
        <asp:ControlParameter ControlID="RadDatePicker2" Name="DateTo" PropertyName="SelectedDate" Type="DateTime" />
    </SelectParameters>
</asp:SqlDataSource>

The grid brings the 3 Parent record where the JournalID = 96, 99 and 100. But then under each of them it shows the records with all 3 JournalIDs, while it should only show the records with JournalID 96 under Parent 96 etc.

Any ideas how to resolve this.

Ads

[Princy]

Hello Adeel,

The problem is because in the where condition of select query of 'SessionDataSource2' you have not specified  that '([BOTID] = @TOPID)'. For information on this please check out the following documentation and see what is missing in your code.
Master/Detail Grids

Thanks,
Princy.

[Adeel]

Thanks