I am having a table having (Treeid as int , ParentId as nvarchar(50), ChildId as nvarchar(50) and childLevel as nvachar(50)). FOR Getting all joining through parentId I have written query which is showing "conversion failed while converting nvarchar data
type AA1000 to data type int" . Please check my query and tell me where i did mistake.
ALTER PROCEDURE [dbo].[Sp_GetGenelogy]
@UserID as nvarchar(50),
@limit int
AS
declare @start int
DECLARE @sum nvarchar(MAX)
Declare @Outputid nvarchar(MAX)
Set @Outputid=0;
set @start=0;
set @sum = @UserID;
Exec Spp_GenoalogyArrSeries @UserID,@limit,@start,@sum output
@UserID as nvarchar(50),
@limit int,
@start int,
@sum nvarchar(MAX) output
AS
Declare @ID as nvarchar(50);
declare @Parenthol as int
declare @childhold as int
Declare @ParentReferId as nvarchar(50);
Declare @ChildSponsorId as nvarchar(50);
Declare @Empty int
Set @Empty=0
SELECT @ParentReferId=CONVERT(INT, ParentReferId),@ChildSponsorId=CONVERT(INT, ChildSponsorId) FROM Trree_Aand where ParentReferId=@UserID
Set @start=@start+1;
set @Parenthol=CONVERT(int,@ParentReferId)
set @childhold=CONVERT(int,@ChildSponsorId)
If @Parenthol <> 0 or @childhold <>0
begin
If @Parenthol <> 0
Begin
if (LEN(@sum) > 0)
set @sum = @sum + ','+ convert(varchar,@ParentReferId);
else
set @sum =convert(varchar,@ParentReferId);
If @start <= @limit
exec Sp_GenoalogyArrSeries @ParentReferId ,@limit,@start, @sum OUTPUT;
End
Else
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
If @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
If @childhold <> 0
Begin
if (LEN(@sum) > 0)
set @sum = @sum + ','+ convert(varchar,@ChildSponsorId);
else
set @sum =convert(varchar,@ChildSponsorId);
if @start <= @limit
exec Sp_GenoalogyArrSeries @ChildSponsorId,@limit,@start,@sum OUTPUT;
End
Else
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
If @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
End
Else
Begin
If @ParentReferId is null
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
If @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
If @ChildSponsorId is null
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
if @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
End
ALTER FUNCTION [dbo].[fn_Split]
(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
as the message saying, somewhere in the INT field you are trying to put AA1000 text, I'll recommend you to put a select statement before every convert statement to find where you are actually doing above. You can also try to use SQLProfiler to achieve the
same. It can be a bit difficult for us to find where you are getting the error in absence of data.
Ashutosh Pathak
Blog: http://catchcode.blogspot.com Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Marked as answer by Banwari on Nov 02, 2012 04:53 AM
I made store- procedure from your suggested code , it's only returning table ( trree_aand) data not of cte table in front end.
My store procedure is this
ALTER procedure[dbo].[View_treeChart]
(
@UserID as nvarchar(50)
)
as
begin
SELECT * FROM Trree_Aand
;WITH CTE AS
(
SELECT [AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition] FROM Trree_Aand WHERE ParentReferId = @UserID -- Parent Code
UNION ALL
SELECT T.[AandTreeId], T.[ParentReferId], T.[ChildSponsorId], T.[ChildPosition] FROM Trree_Aand T INNER JOIN CTE C ON T.ParentReferId = C.ChildSponsorId
)
SELECT [ChildSponsorId], [ChildPosition] FROM CTE
DROP TABLE Trree_Aand
Ashutosh Pathak
Blog: http://catchcode.blogspot.com Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Marked as answer by Banwari on Nov 02, 2012 04:51 AM
you want to show the record in Binary Tree or a normal tree view control in asp.net
Ashutosh Pathak
Blog: http://catchcode.blogspot.com Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Banwari
Member
28 Points
66 Posts
Please Tell me my Query Error
Oct 31, 2012 06:21 AM|LINK
I am having a table having (Treeid as int , ParentId as nvarchar(50), ChildId as nvarchar(50) and childLevel as nvachar(50)). FOR Getting all joining through parentId I have written query which is showing "conversion failed while converting nvarchar data type AA1000 to data type int" . Please check my query and tell me where i did mistake.
-------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Sp_GetGenelogy]
@UserID as nvarchar(50),
@limit int
AS
declare @start int
DECLARE @sum nvarchar(MAX)
Declare @Outputid nvarchar(MAX)
Set @Outputid=0;
set @start=0;
set @sum = @UserID;
Exec Spp_GenoalogyArrSeries @UserID,@limit,@start,@sum output
select item from fn_Split(@sum,',')
----------------------------------------------------------------------------------------
alter PROCEDURE [dbo].[Spp_GenoalogyArrSeries]
@UserID as nvarchar(50),
@limit int,
@start int,
@sum nvarchar(MAX) output
AS
Declare @ID as nvarchar(50);
declare @Parenthol as int
declare @childhold as int
Declare @ParentReferId as nvarchar(50);
Declare @ChildSponsorId as nvarchar(50);
Declare @Empty int
Set @Empty=0
SELECT @ParentReferId=CONVERT(INT, ParentReferId),@ChildSponsorId=CONVERT(INT, ChildSponsorId) FROM Trree_Aand where ParentReferId=@UserID
Set @start=@start+1;
set @Parenthol=CONVERT(int,@ParentReferId)
set @childhold=CONVERT(int,@ChildSponsorId)
If @Parenthol <> 0 or @childhold <>0
begin
If @Parenthol <> 0
Begin
if (LEN(@sum) > 0)
set @sum = @sum + ','+ convert(varchar,@ParentReferId);
else
set @sum =convert(varchar,@ParentReferId);
If @start <= @limit
exec Sp_GenoalogyArrSeries @ParentReferId ,@limit,@start, @sum OUTPUT;
End
Else
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
If @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
If @childhold <> 0
Begin
if (LEN(@sum) > 0)
set @sum = @sum + ','+ convert(varchar,@ChildSponsorId);
else
set @sum =convert(varchar,@ChildSponsorId);
if @start <= @limit
exec Sp_GenoalogyArrSeries @ChildSponsorId,@limit,@start,@sum OUTPUT;
End
Else
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
If @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
End
Else
Begin
If @ParentReferId is null
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
If @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
If @ChildSponsorId is null
Begin
set @sum = @sum + ','+ convert(varchar,@Empty);
if @start <= @limit
exec Sp_GenoalogyArrSeries @Empty ,@limit,@start, @sum OUTPUT;
End
End
---------------------------------------------------------------------------------------------------------------------
ALTER FUNCTION [dbo].[fn_Split]
(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
me_ritz
Star
9337 Points
1447 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 06:26 AM|LINK
May be you are trying to cast nvarchar "ParentReferId" & "ChildSponsorId" to INT and put it into nvarchar variables @ParentReferId & @ChildSponsorId
Ashutosh Pat...
Contributor
5737 Points
1105 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 06:28 AM|LINK
as the message saying, somewhere in the INT field you are trying to put AA1000 text, I'll recommend you to put a select statement before every convert statement to find where you are actually doing above. You can also try to use SQLProfiler to achieve the same. It can be a bit difficult for us to find where you are getting the error in absence of data.
Blog: http://catchcode.blogspot.com
Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Banwari
Member
28 Points
66 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 06:50 AM|LINK
Thanks
me_ritz and Ashutosh for your valuable reply.
your suggestion corrected my half problem. Please read the table structure i.e sql script of table structre.
---------------------------------------------------------------------------------------------
USE [MLMExperiment]
GO
/****** Object: Table [dbo].[Trree_Aand] Script Date: 10/30/2012 23:42:08 ******/
DROP TABLE [dbo].[Trree_Aand]
GO
/****** Object: Table [dbo].[Trree_Aand] Script Date: 10/30/2012 23:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trree_Aand](
[AandTreeId] [int] IDENTITY(1,1) NOT NULL,
[ParentReferId] [nvarchar](50) NULL,
[ChildSponsorId] [nvarchar](50) NULL,
[ChildPosition] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Trree_Aand] ON
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (1, N'0', N'AA1000', N'1')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (2, N'AA1000', N'BB1000', N'2')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (3, N'AA1000', N'BB1001', N'2')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (4, N'AA1000', N'BB1002', N'2')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (5, N'AA1000', N'BB1003', N'2')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (6, N'BB1000', N'CC1000', N'3')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (7, N'BB1000', N'CC1001', N'3')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (8, N'AA1000', N'DD1000', N'3')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (9, N'AA1000', N'DD1001', N'4')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (10, N'AA1000', N'EE1000', N'5')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (11, N'AA1000', N'FF1000', N'6')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (12, N'BB1000', N'DD1002', N'4')
INSERT [dbo].[Trree_Aand] ([AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition]) VALUES (13, N'BB1001', N'FF1001', N'6')
SET IDENTITY_INSERT [dbo].[Trree_Aand] OFF
----------------------------------------------------------------
This table contains some sample data. Here you find AA1000 doesnot have any parent but AA1000 have many direct child as well as indirect child such as
Direct child of AA1000 is BB1000 ,BB1001,BB1002 ,BB1003,DD1000,DD1001,EE1000,FF1000.
Indirect Child are : CC1000 by BB1000, CC1001 by BB1000,DD1002 by BB1000 and FF1001 by BB1001.
I want to get all direct as well indirect Child with there child position.
me_ritz
Star
9337 Points
1447 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 07:14 AM|LINK
Banwari
Member
28 Points
66 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 10:25 AM|LINK
Hello me_ritz,
I made store- procedure from your suggested code , it's only returning table ( trree_aand) data not of cte table in front end.
My store procedure is this
ALTER procedure[dbo].[View_treeChart]
(
@UserID as nvarchar(50)
)
as
begin
SELECT * FROM Trree_Aand
;WITH CTE AS
(
SELECT [AandTreeId], [ParentReferId], [ChildSponsorId], [ChildPosition] FROM Trree_Aand WHERE ParentReferId = @UserID -- Parent Code
UNION ALL
SELECT T.[AandTreeId], T.[ParentReferId], T.[ChildSponsorId], T.[ChildPosition] FROM Trree_Aand T INNER JOIN CTE C ON T.ParentReferId = C.ChildSponsorId
)
SELECT [ChildSponsorId], [ChildPosition] FROM CTE
DROP TABLE Trree_Aand
end
Ashutosh Pat...
Contributor
5737 Points
1105 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 10:28 AM|LINK
remove above line from your code
Blog: http://catchcode.blogspot.com
Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Banwari
Member
28 Points
66 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 11:14 AM|LINK
Now I am geeting the data , how can i represent this in tree view please find the image (Treview) .
I am having Id's and it's level. Now my problem is how to represent data in tree view with its actual level.
Ashutosh Pat...
Contributor
5737 Points
1105 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 11:18 AM|LINK
you want to show the record in Binary Tree or a normal tree view control in asp.net
Blog: http://catchcode.blogspot.com
Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Banwari
Member
28 Points
66 Posts
Re: Please Tell me my Query Error
Oct 31, 2012 11:25 AM|LINK
No Idon't want to show data in binary tree viewe. You can see in image that User AA100 have more than two direct joining
that is BB1000,BB1001,BB1002,EE1001, FF1000.
Any Parent can have any number of direct child , not necesary that it have only two child.