This is a migrated thread and some comments may be shown as answers.

Help with Master/Detail view

6 Answers 60 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Brian
Top achievements
Rank 1
Brian asked on 29 Jul 2013, 10:03 AM
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?

6 Answers, 1 is accepted

Sort by
0
Jayesh Goyani
Top achievements
Rank 2
answered on 29 Jul 2013, 10:45 AM
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
0
Brian
Top achievements
Rank 1
answered on 29 Jul 2013, 11:06 AM
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>

0
Princy
Top achievements
Rank 1
answered on 29 Jul 2013, 11:30 AM
Hi,

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

Thanks,
Princy
0
Brian
Top achievements
Rank 1
answered on 29 Jul 2013, 11:56 AM
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.
0
Jayesh Goyani
Top achievements
Rank 2
answered on 29 Jul 2013, 01:46 PM
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
0
amador
Top achievements
Rank 1
answered on 23 Apr 2018, 05:56 PM
Thanks Jayesh This is what I needed
Tags
Grid
Asked by
Brian
Top achievements
Rank 1
Answers by
Jayesh Goyani
Top achievements
Rank 2
Brian
Top achievements
Rank 1
Princy
Top achievements
Rank 1
amador
Top achievements
Rank 1
Share this question
or