Vijaianand
Top achievements
Rank 1
Rank 1
Vijaianand
asked on 13 Sep 2011, 04:22 PM
It looks like the issue with databinding on Treeview with ParentID is been long one. I have seen old entries going back to 2005 till today with people experiencing the ParentID constraint issue. If you are here looking for a solution on the exception which was thrown at you starts as "The constraint cannot allow you...". Then you are in the right place.
Basically, the dataset should have records for all parent ID's referred in the table. In order to make the Node as a Root so the tree can recognize it as the Root instead of looking for record in the dataset, you need to make that record Parent value as NULL. That's the solution for the issue. There were suggestions to do SELECT ChildID, Value, IF(ParentID=0, NULL, ParentID). This doesn't work in SQL, you have to do
SELECT ChildID, Value, CASE WHEN ParentID='' THEN NULL ELSE ParentID END As ParentID
It worked for me and it works for you. Also if you need to join the same table to get a parent, child relationship. Here is the query which you can use
WITH UserTable AS (SELECT U.EmployeeID, U.Employee, U.ManagerID FROM Employees U WHERE [EmployeeId] = '3434443'
UNION ALL SELECT U.EmployeeID, U.Employee, U.ManagerID FROM Employees U JOIN UserTable U2 ON (U.[ManagerId]=U2.[EmployeeId]))
SELECT EmployeeId,Employee, CASE WHEN EmployeeId='3434443' THEN NULL ELSE ManagerID END FROM UserTable
Hope this helps
Basically, the dataset should have records for all parent ID's referred in the table. In order to make the Node as a Root so the tree can recognize it as the Root instead of looking for record in the dataset, you need to make that record Parent value as NULL. That's the solution for the issue. There were suggestions to do SELECT ChildID, Value, IF(ParentID=0, NULL, ParentID). This doesn't work in SQL, you have to do
SELECT ChildID, Value, CASE WHEN ParentID='' THEN NULL ELSE ParentID END As ParentID
It worked for me and it works for you. Also if you need to join the same table to get a parent, child relationship. Here is the query which you can use
WITH UserTable AS (SELECT U.EmployeeID, U.Employee, U.ManagerID FROM Employees U WHERE [EmployeeId] = '3434443'
UNION ALL SELECT U.EmployeeID, U.Employee, U.ManagerID FROM Employees U JOIN UserTable U2 ON (U.[ManagerId]=U2.[EmployeeId]))
SELECT EmployeeId,Employee, CASE WHEN EmployeeId='3434443' THEN NULL ELSE ManagerID END FROM UserTable
Hope this helps