Telerik Forums / UI for ASP.NET AJAX
This is a migrated thread and some comments may be shown as answers.

Third level hierarchical grid insert to detail table problem

1 Answer 71 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Emre
Top achievements
Rank 1
Emre asked on 06 Dec 2011, 12:47 PM
Hi,

I have a third level hierarchical grid.I try to insert/update/delete operations in this grid.I have tried the approach given in this demo,but the problem is I can't insert or update in my second detail table.I can't add or update the data in RecipeIngredients Table.By the way,I am using Sql data source.
My question is how I can insert and update operations in my second detail table?

The database structure of hierarchy is shown below:
 
RecipeTypes(Master Table)     Recipes(Details Table)           RecipeIngredients(Details Table)        
-RecipeTypeID(PK,int)             -RecipeID(PK,int)                    -RecipeID(PK,FK,int)                          
-RecipeType(nvarchar) -RecipeTypeID(FK,int)             -IngredientID(PK,FK,int)                    
-RecipeName (nvarchar)       -IngredientName(FK,nvarchar)

 Ingredients
-IngredientID(PK,int)
 -IngredientName(PK,nvarchar)

ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head runat="server">
    <title></title>
    <telerik:RadStyleSheetManager ID="RadStyleSheetManager1" runat="server" />
</head>
<body>
    <form id="form1" runat="server">
    <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
        <Scripts>
            <%--Needed for JavaScript IntelliSense in VS2010--%>
            <%--For VS2008 replace RadScriptManager with ScriptManager--%>
            <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js" />
            <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js" />
            <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js" />
        </Scripts>
    </telerik:RadScriptManager>
    <script type="text/javascript">
        //Put your JavaScript code here.
    </script>
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadGrid1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <div>
        <telerik:RadGrid ID="RadGrid1" runat="server" ShowStatusBar="True" AutoGenerateColumns="False"
            PageSize="3" AllowSorting="True" AllowPaging="True" GridLines="None" AllowAutomaticDeletes="True"
            AllowAutomaticInserts="True" AllowAutomaticUpdates="True" DataSourceID="SqlDataSource1"
            OnItemUpdated="RadGrid1_ItemUpdated" OnItemDeleted="RadGrid1_ItemDeleted" OnItemInserted="RadGrid1_ItemInserted"
            OnInsertCommand="RadGrid1_InsertCommand" CellSpacing="0">
            <PagerStyle Mode="NumericPages"></PagerStyle>
            <MasterTableView DataKeyNames="RecipeTypeID" AllowMultiColumnSorting="True" Width="100%"
                CommandItemDisplay="Top" Name="RecipeTypes" DataSourceID="SqlDataSource1" AllowAutomaticDeletes="true"
                AllowAutomaticInserts="true" AllowAutomaticUpdates="true">
                <DetailTables>
                    <telerik:GridTableView DataKeyNames="RecipeID" Width="100%" runat="server" CommandItemDisplay="Top"
                        Name="Recipes" DataSourceID="SqlDataSource2" AllowAutomaticDeletes="true" AllowAutomaticInserts="true"
                        AllowAutomaticUpdates="true">
                        <ParentTableRelation>
                            <telerik:GridRelationFields DetailKeyField="RecipeTypeID" MasterKeyField="RecipeTypeID" />
                        </ParentTableRelation>
                        <DetailTables>
                            <telerik:GridTableView DataKeyNames="RecipeID,IngredientID" Width="100%" runat="server"
                                CommandItemDisplay="Top" Name="RecipeIngredients" DataSourceID="SqlDataSource3"
                                AllowAutomaticDeletes="true" AllowAutomaticInserts="true" AllowAutomaticUpdates="true">
                                <ParentTableRelation>
                                    <telerik:GridRelationFields DetailKeyField="RecipeID" MasterKeyField="RecipeID" />
                                </ParentTableRelation>
                                <Columns>
                                    <telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn1">
                                        <HeaderStyle Width="20px" />
                                        <ItemStyle CssClass="MyImageButton" />
                                    </telerik:GridEditCommandColumn>
                                    <telerik:GridBoundColumn SortExpression="RecipeID" HeaderText="RecipeID" HeaderButtonType="TextButton"
                                        DataField="RecipeID" UniqueName="RecipeID" ReadOnly="true" Visible="false">
                                    </telerik:GridBoundColumn>
                                    <telerik:GridBoundColumn SortExpression="IngredientName" HeaderText="Ingredient Name"
                                        HeaderButtonType="TextButton" DataField="IngredientName" UniqueName="IngredientName">
                                    </telerik:GridBoundColumn>
                                    <telerik:GridBoundColumn SortExpression="Quantity1" HeaderText="Quantity1" HeaderButtonType="TextButton"
                                        DataField="Quantity1" UniqueName="Quantity1">
                                    </telerik:GridBoundColumn>
                                    <telerik:GridBoundColumn SortExpression="Quantity2" HeaderText="Quantity2" HeaderButtonType="TextButton"
                                        DataField="Quantity2" UniqueName="Quantity2">
                                    </telerik:GridBoundColumn>
                                    <telerik:GridBoundColumn SortExpression="Quantity3" HeaderText="Quantity3" HeaderButtonType="TextButton"
                                        DataField="Quantity3" UniqueName="Quantity3">
                                    </telerik:GridBoundColumn>
                                    <telerik:GridButtonColumn ConfirmText="Delete this product?" ButtonType="ImageButton"
                                        CommandName="Delete" Text="Delete" UniqueName="DeleteColumn1">
                                        <HeaderStyle Width="20px" />
                                        <ItemStyle HorizontalAlign="Center" CssClass="MyImageButton" />
                                    </telerik:GridButtonColumn>
                                </Columns>
                            </telerik:GridTableView>
                        </DetailTables>
                        <Columns>
                            <telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn2">
                                <HeaderStyle Width="20px" />
                                <ItemStyle CssClass="MyImageButton" />
                            </telerik:GridEditCommandColumn>
                            <telerik:GridBoundColumn SortExpression="RecipeID" HeaderText="RecipeID" HeaderButtonType="TextButton"
                                DataField="RecipeID" UniqueName="RecipeID" ReadOnly="true">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn SortExpression="RecipeName" HeaderText="Recipe Name" HeaderButtonType="TextButton"
                                DataField="RecipeName" UniqueName="RecipeName">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn SortExpression="Calories1" HeaderText="Calories 1" HeaderButtonType="TextButton"
                                DataField="Calories1" UniqueName="Calories1">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn SortExpression="Calories2" HeaderText="Calories 2" HeaderButtonType="TextButton"
                                DataField="Calories2" UniqueName="Calories2">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn SortExpression="Calories3" HeaderText="Calories 3" HeaderButtonType="TextButton"
                                DataField="Calories3" UniqueName="Calories3">
                            </telerik:GridBoundColumn>
                            <telerik:GridButtonColumn ConfirmText="Delete these details record?" ButtonType="ImageButton"
                                CommandName="Delete" Text="Delete" UniqueName="DeleteColumn2">
                                <HeaderStyle Width="20px" />
                                <ItemStyle HorizontalAlign="Center" CssClass="MyImageButton" />
                            </telerik:GridButtonColumn>
                        </Columns>
                    </telerik:GridTableView>
                </DetailTables>
                <Columns>
                    <telerik:GridBoundColumn SortExpression="RecipeTypeID" HeaderText="RecipeTypeID"
                        DataField="RecipeTypeID" UniqueName="RecipeTypeID" ReadOnly="true" DataType="System.Int32">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="RecipeType" HeaderText="RecipeType" DataField="RecipeType"
                        UniqueName="RecipeType">
                    </telerik:GridBoundColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CateringDBConnectionString %>"
            ConflictDetection="CompareAllValues" OldValuesParameterFormatString="original_{0}"
            SelectCommand="SELECT * FROM [RecipeTypes]" InsertCommand="INSERT INTO [RecipeTypes] ([RecipeType]) VALUES (@RecipeType)"
            DeleteCommand="DELETE FROM [RecipeTypes] WHERE [RecipeTypeID] = @original_RecipeTypeID"
            UpdateCommand="UPDATE [RecipeTypes] SET [RecipeType] = @RecipeType WHERE [RecipeTypeID] = @original_RecipeTypeID AND [RecipeType] = @original_RecipeType">
            <InsertParameters>
                <asp:Parameter Name="RecipeType" Type="String" />
            </InsertParameters>
            <DeleteParameters>
                <asp:Parameter Name="original_RecipeTypeID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="RecipeType" Type="String" />
                <asp:Parameter Name="original_RecipeTypeID" Type="Int32" />
                <asp:Parameter Name="original_RecipeType" Type="String" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CateringDBConnectionString %>"
            SelectCommand="SELECT * FROM [Recipes] WHERE ([RecipeTypeID] = @RecipeTypeID)"
            InsertCommand="INSERT INTO [Recipes] ([RecipeTypeID], [RecipeName], [Calories1], [Calories2], [Calories3]) VALUES (@RecipeTypeID,@RecipeName, @Calories1, @Calories2, @Calories3)"
            DeleteCommand="DELETE FROM [Recipes] WHERE [RecipeID] = @original_RecipeID" UpdateCommand="UPDATE [Recipes] SET [RecipeName] = @RecipeName, [Calories1] = @Calories1, [Calories2] = @Calories2, [Calories3] = @Calories3 WHERE [RecipeID] = @original_RecipeID AND [RecipeName] = @original_RecipeName AND [Calories1] = @original_Calories1 AND [Calories2] = @original_Calories2 AND [Calories3] = @original_Calories3"
            ConflictDetection="CompareAllValues" OldValuesParameterFormatString="original_{0}">
            <SelectParameters>
                <asp:SessionParameter Name="RecipeTypeID" SessionField="RecipeTypeID" Type="Int32" />
            </SelectParameters>
            <InsertParameters>
                <asp:SessionParameter Name="RecipeTypeID" SessionField="RecipeTypeID" Type="Int32" />
                <asp:Parameter Name="RecipeName" Type="String" />
                <asp:Parameter Name="Calories1" Type="Int32" />
                <asp:Parameter Name="Calories2" Type="Int32" />
                <asp:Parameter Name="Calories3" Type="Int32" />
            </InsertParameters>
            <DeleteParameters>
                <asp:Parameter Name="original_RecipeID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="RecipeName" Type="String" />
                <asp:Parameter Name="Calories1" Type="Int32" />
                <asp:Parameter Name="Calories2" Type="Int32" />
                <asp:Parameter Name="Calories3" Type="Int32" />
                <asp:Parameter Name="original_RecipeID" Type="Int32" />
                <asp:Parameter Name="original_RecipeName" Type="String" />
                <asp:Parameter Name="original_Calories1" Type="Int32" />
                <asp:Parameter Name="original_Calories2" Type="Int32" />
                <asp:Parameter Name="original_Calories3" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:CateringDBConnectionString %>"
            SelectCommand="SELECT * FROM [RecipeIngredients] WHERE ([RecipeID] = @RecipeID)"
            InsertCommand="INSERT INTO [RecipeIngredients] ([RecipeID], [IngredientName], [Quantity1], [Quantity2], [Quantity3]) VALUES (@RecipeID, @IngredientName, @Quantity1, @Quantity2, @Quantity3)"
            DeleteCommand="DELETE FROM [RecipeIngredients] WHERE [RecipeID] = @original_RecipeID"
            UpdateCommand="UPDATE [RecipeIngredients] SET [IngredientName] = @IngredientName, [Quantity1] = @Quantity1, [Quantity2] = @Quantity2, [Quantity3] = @Quantity3 WHERE [RecipeID] = @original_RecipeID AND [IngredientName] = @original_IngredientName AND [Quantity1] = @original_Quantity1 AND [Quantity2] = @original_Quantity2 AND [Quantity3] = @original_Quantity3"
            OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues">
            <SelectParameters>
                <asp:SessionParameter Name="RecipeID" SessionField="RecipeID" Type="Int32" />
            </SelectParameters>
            <InsertParameters>
                <asp:SessionParameter Name="RecipeID" SessionField="RecipeID" Type="Int32" />
                <asp:Parameter Name="IngredientName" Type="String" />
                <asp:Parameter Name="Quantity1" Type="Int32" />
                <asp:Parameter Name="Quantity2" Type="Int32" />
                <asp:Parameter Name="Quantity3" Type="Int32" />
            </InsertParameters>
            <DeleteParameters>
                <asp:Parameter Name="original_RecipeID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="IngredientName" Type="String" />
                <asp:Parameter Name="Quantity1" Type="Int32" />
                <asp:Parameter Name="Quantity2" Type="Int32" />
                <asp:Parameter Name="Quantity3" Type="Int32" />
                <asp:Parameter Name="original_RecipeID" Type="Int32" />
                <asp:Parameter Name="original_IngredientName" Type="String" />
                <asp:Parameter Name="original_Quantity1" Type="Int32" />
                <asp:Parameter Name="original_Quantity2" Type="Int32" />
                <asp:Parameter Name="original_Quantity3" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>
ASPX.CS
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Telerik.Web.UI;
using CateringWebApp.CateringDataSetTableAdapters;
using CateringWebApp;
 
public partial class Default : System.Web.UI.Page
{
    
 
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
 
    protected void RadGrid1_ItemUpdated(object source, Telerik.Web.UI.GridUpdatedEventArgs e)
    {
        string item = getItemName(e.Item.OwnerTableView.Name);
        string field = getFieldName(e.Item.OwnerTableView.Name);
        if (e.Exception != null)
        {
            e.KeepInEditMode = true;
            e.ExceptionHandled = true;
            DisplayMessage(item + " " + e.Item[field].Text + " cannot be updated. Reason: " + e.Exception.Message);
        }
        else
        {
            DisplayMessage(item + " " + e.Item[field].Text + " updated");
        }
    }
 
    protected void RadGrid1_ItemInserted(object source, GridInsertedEventArgs e)
    {
        string item = getItemName(e.Item.OwnerTableView.Name);
        if (e.Exception != null)
        {
            e.ExceptionHandled = true;
            DisplayMessage(item + " cannot be inserted. Reason: " + e.Exception.Message);
        }
        else
        {
            DisplayMessage(item + " inserted");
        }
    }
 
    protected void RadGrid1_ItemDeleted(object source, GridDeletedEventArgs e)
    {
        string item = getItemName(e.Item.OwnerTableView.Name);
        string field = getFieldName(e.Item.OwnerTableView.Name);
        if (e.Exception != null)
        {
            e.ExceptionHandled = true;
            DisplayMessage(item + " " + e.Item[field].Text + " cannot be deleted. Reason: " + e.Exception.Message);
        }
        else
        {
            DisplayMessage(item + " " + e.Item[field].Text + " deleted");
        }
    }
 
    protected void RadGrid1_InsertCommand(object source, GridCommandEventArgs e)
    {
        if ("Recipes".Equals(e.Item.OwnerTableView.Name))
        {
            GridDataItem parentItem = (GridDataItem)e.Item.OwnerTableView.ParentItem;
            SqlDataSource2.InsertParameters["RecipeTypeID"].DefaultValue = parentItem.OwnerTableView.DataKeyValues[parentItem.ItemIndex]["RecipeTypeID"].ToString();
        }
        else if ("RecipeIngredients".Equals(e.Item.OwnerTableView.Name))
        {
            GridDataItem parentItem = (GridDataItem)e.Item.OwnerTableView.ParentItem;
            SqlDataSource3.InsertParameters["RecipeID"].DefaultValue = parentItem.OwnerTableView.DataKeyValues[parentItem.ItemIndex]["RecipeID"].ToString();
        }
    }
 
    private String getItemName(string tableName)
    {
        switch (tableName)
        {
            case ("RecipeTypes"):
                {
                    return "RecipeTypes";
                }
            case ("Recipes"):
                {
                    return "Recipes";
                }
            case ("RecipeIngredients"):
                {
                    return "RecipeIngredients";
                }
            default: return "";
        }
    }
 
    private String getFieldName(string tableName)
    {
        switch (tableName)
        {
            case ("RecipeTypes"):
                {
                    return "RecipeTypeID";
                }
            case ("Recipes"):
                {
                    return "RecipeID";
                }
            case ("RecipeIngredients"):
                {
                    return "RecipeID";
                }
            default: return "";
        }
    }
 
    private void DisplayMessage(string text)
    {
        RadGrid1.Controls.Add(new LiteralControl(string.Format("<span style='color:red'>{0}</span>", text)));
    }
 
 
 
}

1 Answer, 1 is accepted

Sort by
0
Antonio Stoilkov
Telerik team
answered on 08 Dec 2011, 05:43 PM
Hi Emre,

Based on the supplied information, it is hard to determine what is causing the issue.

Please send us a small working project, demonstrating your full setup and showing the unwanted behavior.
We will debug it locally and get back to you with our findings.

Greetings,
Antonio Stoilkov
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
Tags
Grid
Asked by
Emre
Top achievements
Rank 1
Answers by
Antonio Stoilkov
Telerik team
Share this question
or