Help with Master/Detail view

[Brian]

I am trying to create a master/detail view but it is returning all records in the detail.

In the master datakeynames, I have ProductTypeID (primary key) and ProductID

In the detail datakeynames, I have ID (primary key), ProductTypeID and ProductID

For the Parent Table Relations, I have two rows set to ProductTypeID and ProductID.  For each master record, it is returning every detail row without any filtering.  What am I doing wrong here?

[Jayesh Goyani]

Hello,

<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" DataSourceID="SqlDataSource1">
        <MasterTableView DataSourceID="SqlDataSource1" DataKeyNames="ProductID,ProductTypeID">
            <Columns>
                <telerik:GridBoundColumn DataField="ProductID" HeaderText="ProductID" UniqueName="ProductID">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="ProductTypeID" HeaderText="ProductTypeID" UniqueName="ProductTypeID">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="ProductName" HeaderText="ProductName" UniqueName="ProductName">
                </telerik:GridBoundColumn>
            </Columns>
            <DetailTables>
                <telerik:GridTableView DataSourceID="SqlDataSource2" DataKeyNames="ProductID,ProductTypeID">
                    <ParentTableRelation>
                        <telerik:GridRelationFields DetailKeyField="ProductID" MasterKeyField="ProductID" />
                        <telerik:GridRelationFields DetailKeyField="ProductTypeID" MasterKeyField="ProductTypeID">
                        </telerik:GridRelationFields>
                    </ParentTableRelation>
                    <Columns>
                        <telerik:GridBoundColumn DataField="ProductDetailID" HeaderText="ProductDetailID"
                            UniqueName="ProductDetailID">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="ProductID" HeaderText="ProductID" UniqueName="ProductID">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="ProductTypeID" HeaderText="ProductTypeID" UniqueName="ProductTypeID">
                        </telerik:GridBoundColumn>
                    </Columns>
                </telerik:GridTableView>
            </DetailTables>
        </MasterTableView>
    </telerik:RadGrid>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:JayeshTestConnectionString %>"
        SelectCommand="SELECT [ProductID], [ProductTypeID], [ProductName] FROM [Product]">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:JayeshTestConnectionString %>"
        SelectCommand="SELECT [ProductDetailID], [ProductID], [ProductTypeID] FROM [ProductDetail] WHERE (([ProductID] = @ProductID) AND ([ProductTypeID] = @ProductTypeID))">
        <SelectParameters>
            <asp:SessionParameter Name="ProductID" SessionField="ProductID" Type="Int32" />
            <asp:SessionParameter Name="ProductTypeID" SessionField="ProductTypeID" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

Thanks,
Jayesh Goyani

[Brian]

Please see my code below as my situation is different.  Note that in the sqlActionXRef table, I do not use the primary key.  Don't know if that matters.

<telerik:RadGrid ID="radgvwProductTypes" runat="server" DataSourceID="sqlProductTypes" AutoGenerateDeleteColumn="True" AutoGenerateEditColumn="True" CellSpacing="0" GridLines="None" AutoGenerateColumns="False" AllowSorting="True" Skin="Sunset" AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True">

                    <MasterTableView DataKeyNames="ProductTypeID,ProductID" DataSourceID="sqlProductTypes" AllowSorting="False" EditMode="InPlace" AllowPaging="True" PageSize="20" CommandItemDisplay="Top">

                        <DetailTables>

                            <telerik:GridTableView runat="server" AllowAutomaticInserts="False" DataKeyNames="ID,ProductID,ProductTypeID" DataSourceID="sqlActionXRef">

                                <ParentTableRelation>

                                    <telerik:GridRelationFields DetailKeyField="ProductID" MasterKeyField="ProductID" />

                                    <telerik:GridRelationFields DetailKeyField="ProductTypeID" MasterKeyField="ProductTypeID" />

                                </ParentTableRelation>

                                <Columns>

                                    <telerik:GridBoundColumn DataField="ProductID" FilterControlAltText="Filter column column" UniqueName="ProductID" Visible="False">

                                    </telerik:GridBoundColumn>

                                    <telerik:GridBoundColumn DataField="ProductTypeID" FilterControlAltText="Filter column1 column" UniqueName="ProductTypeID" Visible="False">

                                    </telerik:GridBoundColumn>

                                    <telerik:GridTemplateColumn DataField="ActionTypeID" FilterControlAltText="Filter ActionID column" HeaderText="Action" UniqueName="ActionID">

                                        <EditItemTemplate>

                                            <asp:TextBox ID="ActionTypeIDTextBox" runat="server" Text='<%# Bind("ActionTypeID") %>'></asp:TextBox>

                                        </EditItemTemplate>

                                        <ItemTemplate>

                                            <asp:Label ID="ActionTypeIDLabel" runat="server" Text='<%# Eval("ActionTypeID") %>'></asp:Label>

                                        </ItemTemplate>

                                    </telerik:GridTemplateColumn>

                                    <telerik:GridBoundColumn DataField="ClientPrice" FilterControlAltText="Filter ClientPrice column" HeaderText="Client Price" UniqueName="ClientPrice">

                                    </telerik:GridBoundColumn>

                                </Columns>

                            </telerik:GridTableView>

                        </DetailTables>

                        <CommandItemSettings AddNewRecordText="Add new" />

                        <Columns>

                            <telerik:GridBoundColumn DataField="ProductTypeID" ReadOnly="True" HeaderText="ProductTypeID" SortExpression="ProductTypeID" UniqueName="ProductTypeID" DataType="System.Int32" FilterControlAltText="Filter ProductTypeID column" Visible="False"></telerik:GridBoundColumn>

                            <telerik:GridTemplateColumn DataField="ProductID" HeaderText="Product" SortExpression="ProductID" UniqueName="ProductID" DataType="System.Int32" FilterControlAltText="Filter ProductID column">

                                <EditItemTemplate>

                                    <telerik:RadDropDownList ID="radddlProduct" runat="server" DataSourceID="sqlProducts" DataTextField="ProductName" DataValueField="ProductID" DefaultMessage="Choose Product" SelectedValue='<%# Bind("ProductID") %>' Skin="Sunset">

                                    </telerik:RadDropDownList>

                                </EditItemTemplate>

                                <InsertItemTemplate>

                                    <telerik:RadDropDownList ID="radddlProduct" runat="server" DataSourceID="sqlProducts" DataTextField="ProductName" DataValueField="ProductID" SelectedValue='<%# Bind("ProductID") %>' Skin="Sunset">

                                    </telerik:RadDropDownList>

                                </InsertItemTemplate>

                                <ItemTemplate>

                                    <asp:Label runat="server" ID="ProductIDLabel" Text='<%# Eval("ProductName") %>'></asp:Label>

                                </ItemTemplate>

                            </telerik:GridTemplateColumn>

 

                            <telerik:GridBoundColumn DataField="ProductType" HeaderText="Product Type" SortExpression="ProductType" UniqueName="ProductType" FilterControlAltText="Filter ProductType column"></telerik:GridBoundColumn>

 

                            <telerik:GridBoundColumn DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" UniqueName="ProductName" FilterControlAltText="Filter ProductName column" Visible="False"></telerik:GridBoundColumn>

                            <telerik:GridTemplateColumn DataField="UofMID" DataType="System.Int32" FilterControlAltText="Filter UofMID column" HeaderText="Units" SortExpression="UofMID" UniqueName="UofMID">

                                <EditItemTemplate>

                                    <telerik:RadDropDownList ID="RadDropDownList1" runat="server" DataSourceID="sqlUofM" DataTextField="UofMName" DataValueField="UofMID" SelectedValue='<%# Bind("UofMID") %>' Skin="Sunset">

                                    </telerik:RadDropDownList>

                                     

                                </EditItemTemplate>

                                <InsertItemTemplate>

                                    <telerik:RadDropDownList ID="RadDropDownList2" runat="server" DataSourceID="sqlUofM" DataTextField="UofMName" DataValueField="UofMID" SelectedValue='<%# Bind("UofMID") %>'>

                                    </telerik:RadDropDownList>

                                </InsertItemTemplate>

                                <ItemTemplate>

                                    <asp:Label ID="UofMIDLabel" runat="server" Text='<%# Eval("UofMName") %>'></asp:Label>

                                </ItemTemplate>

                            </telerik:GridTemplateColumn>

 

                            <telerik:GridBoundColumn DataField="UofMName" HeaderText="UofMName" SortExpression="UofMName" UniqueName="UofMName" FilterControlAltText="Filter UofMName column" Visible="False"></telerik:GridBoundColumn>

                            <telerik:GridCheckBoxColumn DataField="IsDeleted" HeaderText="Inactive" SortExpression="IsDeleted" UniqueName="IsDeleted" DataType="System.Boolean" FilterControlAltText="Filter IsDeleted column">

                                <HeaderStyle HorizontalAlign="Center" />

                                <ItemStyle HorizontalAlign="Center" />

                            </telerik:GridCheckBoxColumn>

                        </Columns>

                    </MasterTableView>

                </telerik:RadGrid>

<asp:SqlDataSource runat="server" ID="sqlProductTypes" ConnectionString='<%$ ConnectionStrings:dbBidManager %>' SelectCommand="SELECT dbo.tblProductTypes.ProductTypeID, dbo.tblProductTypes.ProductType, dbo.tblProductTypes.ProductID, dbo.tblProductTypes.UofMID, dbo.tblUnitsofMeasure.UofMName, dbo.tblProducts.ProductName, dbo.tblProductTypes.IsDeleted FROM dbo.tblProductTypes INNER JOIN dbo.tblUnitsofMeasure ON dbo.tblProductTypes.UofMID = dbo.tblUnitsofMeasure.UofMID INNER JOIN dbo.tblProducts ON dbo.tblProductTypes.ProductID = dbo.tblProducts.ProductID WHERE (dbo.tblProductTypes.ProductTypeID <> 0) ORDER BY dbo.tblProductTypes.ProductType" UpdateCommand="UPDATE [tblProductTypes] SET [ProductType] = @ProductType, [ProductID] = @ProductID,  [UofMID] = @UofMID,  [IsDeleted] = @IsDeleted WHERE [ProductTypeID] = @ProductTypeID">

                    <UpdateParameters>

                        <asp:Parameter Name="ProductType" Type="String"></asp:Parameter>

                        <asp:Parameter Name="ProductID" Type="Int32"></asp:Parameter>

                        <asp:Parameter Name="UofMID" Type="Int32"></asp:Parameter>

                        <asp:Parameter Name="IsDeleted" Type="Boolean"></asp:Parameter>

                        <asp:Parameter Name="ProductTypeID" Type="Int32"></asp:Parameter>

                    </UpdateParameters>

                </asp:SqlDataSource>

<asp:SqlDataSource ID="sqlActionXRef" runat="server" ConnectionString="<%$ ConnectionStrings:dbBidManager %>" SelectCommand="SELECT dbo.tblActionTypeProductXRef.ID, dbo.tblActionTypeProductXRef.ActionTypeID, dbo.tblActionTypeProductXRef.ProductTypeID, dbo.tblActionTypeProductXRef.ProductID, dbo.tblActionTypeProductXRef.ClientPrice, dbo.tblActionTypeProductXRef.ContractorPrice, dbo.tblActionTypes.ActionTypeName FROM dbo.tblActionTypeProductXRef INNER JOIN dbo.tblActionTypes ON dbo.tblActionTypeProductXRef.ActionTypeID = dbo.tblActionTypes.ActionTypeID"></asp:SqlDataSource>

[Princy]

Hi,

Can you please check with this demo and see if it helps you Grid - Update/Insert/Delete in Hierarchy

Thanks,
Princy

[Brian]

It's not quite the same.  In looking at the documentation, I think (but I'm not certain) that the problem may be because I'm not using the primary key in the child relationship.  

At the master level, ProductTypeID is the master key on Table A.  I also use ProductID from this table.  At the detail level in Table B, when I use ProductTypeID and ProductID, neither one of those are the primary key.  Note:  I have the primary key, plus these two values, listed in DataKeyNames.  But it returns every detail row and does no filtering.

[Jayesh Goyani]

Hello,

You have to provide two parameter in child' datasource (sqlActionXRef).

As i have given in below demo.

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:JayeshTestConnectionString %>"
        SelectCommand="SELECT [ProductDetailID], [ProductID], [ProductTypeID] FROM [ProductDetail] WHERE (([ProductID] = @ProductID) AND ([ProductTypeID] = @ProductTypeID))">
        <SelectParameters>
            <asp:SessionParameter Name="ProductID" SessionField="ProductID" Type="Int32" />
            <asp:SessionParameter Name="ProductTypeID" SessionField="ProductTypeID" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

Thanks,
Jayesh Goyani

[amador]

Thanks Jayesh This is what I needed