Heirarchical TreeView - NonUnique data

[David]

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

[Nikolay Tsenkov]

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

[David]

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

[Nikolay Tsenkov]

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

[David]

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

[Nikolay Tsenkov]

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

[David]

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.

[David]

Any chance you got it working?

[Nikolay Tsenkov]

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