Heirarchical TreeView - NonUnique data

9 posts, 0 answers
  1. David
    David avatar
    87 posts
    Member since:
    Jul 2010

    Posted 18 May 2011 Link to this post

    Hi,

    I wanted to populate a Treeview based upon a DataTable retrieved from the AdventureWorks database using a recursive CTE in a Stored Procedure. It is the good old recursive problem of components witin components within a product. The SP is:

    USE [AdventureWorks]
    GO
    /****** Object:  StoredProcedure [dbo].[CreateProductComponentHeirarchyForProduct]    Script Date: 05/18/2011 20:44:38 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
     
    ALTER PROCEDURE [dbo].[CreateProductComponentHeirarchyForProduct]
        @ComponentID int
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
     
        ; WITH BillOfMaterialsCTE
        (
            BillOfMaterialsID,
            ProductAssemblyID,
            ComponentID,
            Quantity,
            Level
        )
        AS
        (
            SELECT  bom.BillOfMaterialsID,
                bom.ProductAssemblyID,
                bom.ComponentID,
                bom.PerAssemblyQty,
                0 as Level
            FROM Production.BillOfMaterials bom
            WHERE bom.ComponentID = @ComponentID
             
            UNION ALL
             
            SELECT  bom.BillOfMaterialsID,
                bom.ProductAssemblyID,
                bom.ComponentID,
                bom.PerAssemblyQty,
                Level + 1
            FROM Production.BillOfMaterials bom
            INNER JOIN BillOfMaterialsCTE bomCTE
                ON bom.ProductAssemblyID = bomCTE.ComponentID
            WHERE bom.EndDAte IS NULL
        )
        SELECT 
            bomCTE.ProductAssemblyID,
            p.ProductID,
            p.ProductNumber,
            p.Name,
            p.Color,
            bomCTE.Quantity,
            bomCTE.Level
        FROM BillOfMaterialsCTE bomCTE
        INNER JOIN Production.Product p
            on bomCTE.ComponentID = p.ProductID
         
    END

    I used the following method to populate a RadTreeView on a page:

    public void SetResults(DataTable results)
    {
        this.ProductsRadTreeView.DataSource = results;
     
        this.ProductsRadTreeView.DataFieldID = "ProductAssemblyID";
        this.ProductsRadTreeView.DataFieldParentID = "ProductID";
        this.ProductsRadTreeView.DataTextField = "Name";
     
        this.ProductsRadTreeView.DataBind();
    }

    When it hits the DataBind() call, the error message is: These columns don't currently have unique values.

    This is no surprise to me. I did not expect them to be unique It is a recursive algorithm where components can be used in more than one component/product, so of course there will be duplicates.

    Can the RadTreeView display such a ResultSet?

    Cheers
  2. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 20 May 2011 Link to this post

    Hello David,

    There isn't a problem of binding the Text field to a non-unique column in a DataTable, even the ParentID field can be bound to a non-unique column. The problem is when you try to bind the ID field to a non-unique column.

    Here is a nice article that should explain all the requirements the binding to Hierarchical Data in RadTreeView has:http://www.telerik.com/help/aspnet-ajax/treeview-data-binding-hierarchical.html


    Regards,
    Nikolay Tsenkov
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  3. David
    David avatar
    87 posts
    Member since:
    Jul 2010

    Posted 24 May 2011 Link to this post

    Hi Nikolay,

    Thanks for the link to that article. It was helpful. However, I am still not achieving the desired result. The article states that A unique identifier must be assigned to the DataFieldID.

    To that end, I modified the stored procedure:
    USE [AdventureWorks]
    GO
    /****** Object:  StoredProcedure [dbo].[CreateProductComponentHeirarchyForProduct]    Script Date: 05/24/2011 16:25:14 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
     
    ALTER PROCEDURE [dbo].[CreateProductComponentHeirarchyForProduct]
        @ComponentID int
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
     
        ; WITH BillOfMaterialsCTE
        (
            BillOfMaterialsID,
            ProductAssemblyID,
            ComponentID,
            Quantity,
            [Level]
        )
        AS
        (
            SELECT  bom.BillOfMaterialsID,
                bom.ProductAssemblyID,
                bom.ComponentID,
                bom.PerAssemblyQty,
                0 as [Level]
            FROM Production.BillOfMaterials bom
            WHERE bom.ComponentID = @ComponentID
             
            UNION ALL
             
            SELECT  bom.BillOfMaterialsID,
                bom.ProductAssemblyID,
                bom.ComponentID,
                bom.PerAssemblyQty,
                [Level] + 1
            FROM Production.BillOfMaterials bom
            INNER JOIN BillOfMaterialsCTE bomCTE
                ON bom.ProductAssemblyID = bomCTE.ComponentID
            WHERE bom.EndDAte IS NULL
        )
        SELECT 
            CAST(bomCTE.ProductAssemblyID  AS VARCHAR(10)) AS 'ProductAssemblyID',
            CAST(p.ProductID AS VARCHAR(10)) + CAST(bomCTE.ProductAssemblyID AS VARCHAR(10)) AS 'ProdPlusParent',
            p.ProductNumber,
            p.Name,
            p.Color,
            bomCTE.Quantity,
            bomCTE.[Level]
        FROM BillOfMaterialsCTE bomCTE
        INNER JOIN Production.Product p
            on bomCTE.ComponentID = p.ProductID
         
    END

    The ProdPlusParent column returns a unique set of values. However, when I pass in the component ID 774, the error msg for an exception is
    These columns don't currently have unique values.

    Not unique? I cannot figure that one out.

    Also, when I pass in the ID 774, the error msg is:

    This constraint cannot be enabled as not all values have corresponding parent values

    So, there are problems. I'm not sure where to start in resolving them

    How does the TreeView work? Exactly what is it expecting?

    Thanks

  4. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 27 May 2011 Link to this post

    Hi David,

    Could you, please, post a table resulted from the StoreProc (with the actual data in it)?
    Thanks.


    Regards,
    Nikolay Tsenkov
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  5. David
    David avatar
    87 posts
    Member since:
    Jul 2010

    Posted 28 May 2011 Link to this post

    Hi Nikolay,

    Of course.
    Here are a couple of result sets CTE_Recursive_774 and CTE_Recursive_743.
    The parameters passed in were 774 and 743.

    You should be able to reproduce those by running the stored proc against the original AdventureWorks database.

    Cheers
    David
  6. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 01 Jun 2011 Link to this post

    Hi David,

    Here are my observations:
     - in the 774 set, everything appears to be normal - there is only 1 root-level node (with parent field NULL). This data should be easily bound to the TreeView;
     - in the 773 set, though, there isn't root-level nodes (none of them are without parent nodes). This resultSet should fail the binding.


    Regards,
    Nikolay Tsenkov
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  7. David
    David avatar
    87 posts
    Member since:
    Jul 2010

    Posted 04 Jun 2011 Link to this post

    Hi Nikolay,

    If you have time, can you please run your eye over my code to see if you can identify why it does not even work with the 774 component.

    You just need to run the CreateProductComponentHeirarchyForProduct.sql in the root directory over the AdventureWorks database (not AdventureWorks2008) and change the connection string in the Web.config to your local.
    I've also included in the root directory a screeshot of the datavisualizer for the results that the service returns.
    774 is hard-coded into the service so there's always a root node of null.
    The page in question is ProductsAndComponents.aspx in the Products folder of the Website project. The solution was created in VS 2008 and the Telerik assemblies have been removed (for IP protection).

    I've spent heaps of time on this and just cannot figure out why it won't work.

    I eagerly await your findings. I'm so keen to see whether this control can display data in this way. If so, it will be an excellent choice for a project that we have coming up.
  8. David
    David avatar
    87 posts
    Member since:
    Jul 2010

    Posted 16 Jun 2011 Link to this post

    Any chance you got it working?
  9. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 21 Jun 2011 Link to this post

    Hello David,

    Please, open a support ticket and send us a simple runnable sample that will reproduce the problem you specify. You can abstract the dataSource in a DataTable with hard-coded data in the code-behind of the page which should look similar to the following:
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("Text");
    dataTable.Columns.Add("ID");
    dataTable.Columns.Add("ParentID");
    dataTable.Columns.Add("IsChecked");
     
    dataTable.Rows.Add(new String[] {"root node1", "1", null, "false"});
    dataTable.Rows.Add(new String[] { "root node2", "2", null, "false" });
    dataTable.Rows.Add(new String[] { "root node3", "3", null, "false" });
    dataTable.Rows.Add(new String[] { "root node4", "4", null, "false" });
    dataTable.Rows.Add(new String[] { "root node5", "5", null, "false" });
    dataTable.Rows.Add(new String[] { "root node6", "6", null, "false" });
    dataTable.Rows.Add(new String[] { "node11", "7", "1", "false" });
    dataTable.Rows.Add(new String[] { "node12", "8", "1", "true" });
    dataTable.Rows.Add(new String[] { "node13", "9", "1", "true" });
    dataTable.Rows.Add(new String[] { "node14", "10", "1", "false" });
    dataTable.Rows.Add(new String[] { "node15", "11", "1", "false" });
    dataTable.Rows.Add(new String[] { "node16", "12", "1", "false" });
    dataTable.Rows.Add(new String[] { "node111", "13", "7", "false" });
    dataTable.Rows.Add(new String[] { "node112", "14", "7", "false" });
    dataTable.Rows.Add(new String[] { "node123", "15", "7", "false" });


    Regards,
    Nikolay Tsenkov
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

Back to Top