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