Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

VB.NET TreeView using Stored Procedure Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 1:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
There are 5 tables we have which has a parent child relation we have following tables.

Order (Fields Order#, RefType, RefNum, RefLine)
OrderLine (Fields OrderLine, RefType, RefNum, RefLine)
Job (Fields JobNum, JobLine, RefType, RefNum, RefLine)
POLine (Fields PONum, POLine, RefType, RefNum, RefLine)
TransferLIne (Fields TONum, TOLine, RefType, RefNum, RefLine)

All the Tables ar related to each other using 3 fields 1.RefType 2.RefNum 3.RefLine.
RefType is either 'Order', 'OrderLine', 'Job', POLine' , 'TransferLine'

We would like to present the information something like below in the Treeview in Vb.net application using Stored Proc .
Order#
- OrderLine
-- Job
--- POLine
----Job
------ Order
- OrderLine
-- Job
--- POLine
----Job
------ Order

I am also attaching the screen shot of how the program should look like.
Here is the link to the image file.
http://www.evernote.com/shard/s144/sh/a88f77fe-f7db-4b83-b0b0-f3641f8e61ad/b5feaf67b936b20cc8f500ceeef8a498

Post #1452290
Posted Monday, May 13, 2013 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
skb 44459 (5/13/2013)
There are 5 tables we have which has a parent child relation we have following tables.

Order (Fields Order#, RefType, RefNum, RefLine)
OrderLine (Fields OrderLine, RefType, RefNum, RefLine)
Job (Fields JobNum, JobLine, RefType, RefNum, RefLine)
POLine (Fields PONum, POLine, RefType, RefNum, RefLine)
TransferLIne (Fields TONum, TOLine, RefType, RefNum, RefLine)

All the Tables ar related to each other using 3 fields 1.RefType 2.RefNum 3.RefLine.
RefType is either 'Order', 'OrderLine', 'Job', POLine' , 'TransferLine'

We would like to present the information something like below in the Treeview in Vb.net application using Stored Proc .
Order#
- OrderLine
-- Job
--- POLine
----Job
------ Order
- OrderLine
-- Job
--- POLine
----Job
------ Order

I am also attaching the screen shot of how the program should look like.
Here is the link to the image file.
http://www.evernote.com/shard/s144/sh/a88f77fe-f7db-4b83-b0b0-f3641f8e61ad/b5feaf67b936b20cc8f500ceeef8a498



I am guessing that the question is something like "How do I create a stored procedure that will display the hierarchy for a given (not sure what the root is here)?"

Based on the lack of details the best answer you are likely to get is "with a recursive cte".

If that answer is enough then I am glad I could help. However, I suspect you probably would like a bit more detailed assistance than that. I am happy to help but first you need to help me. You will need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data.

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1452301
Posted Monday, June 3, 2013 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
Here is the Data Defination and Data and how the tree should look like. What format the stored procedure should give the output data so it can be used in TreeView in stored Procedure ?

CREATE TABLE CO( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE COLine( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE Job( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE Lot( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE POLine( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)

insert into CO values ( 'CO', 'KB11-02916' , 0, null , null , null)
insert into COLine values ( 'COLine', 'KB11-02916' , 1, 'CO' , 'KB11-02916' , 0)
insert into Job values ( 'Job', 'J000503035' , 0, 'COLine' , 'KB11-02916' , 1)
insert into Job values ( 'Job', 'J000503035' , 1, 'Job' , 'J000503035' , 0)
insert into Lot values ( 'Lot', 'LOTJ5030350000' , 0, 'Job' , 'J000503035' , 1)
insert into POLine values ( 'POLine', 'PH00022127' , 1, 'Job' , 'J000503035' , 1)
insert into POLine values ( 'POLine', 'PH00022127' , 2, 'Job' , 'J000503035' , 1)
insert into Job values ( 'Job', 'J000503035' , 3, 'Job' , 'J000503035' , 1)
insert into POLine values ( 'POLine', 'PH00022137' , 1, 'Job' , 'J000503035' , 3)
insert into POLine values ( 'POLine', 'PH00022137' , 2, 'Job' , 'J000503035' , 3)
insert into LOT values ( 'LOT', 'PHL13938' , 0, 'POLine' , 'PH00022127' , 1)
insert into LOT values ( 'LOT', 'PHL13939' , 0, 'POLine' , 'PH00022127' , 1)
insert into LOT values ( 'LOT', 'PHL13940' , 0, 'POLine' , 'PH00022127' , 2)
insert into LOT values ( 'LOT', 'PHL13941' , 0, 'POLine' , 'PH00022127' , 2)
insert into LOT values ( 'LOT', 'PHL13100' , 0, 'POLine' , 'PH00022137' , 1)
insert into LOT values ( 'LOT', 'PHL13101' , 0, 'POLine' , 'PH00022137' , 1)
insert into LOT values ( 'LOT', 'PHL13102' , 0, 'POLine' , 'PH00022137' , 2)


Here how the tree should look like
CO KB11-02916
-COLine KB11-02916-1
--Job J000503035-0
---Job J000503035-1
----Lot LOTJ5030350000-0
----POLine PH00022127-1
-----Lot PHL13938-0
-----Lot PHL13939-0
----POLine PH00022127-2
-----Lot PHL13940-0
-----Lot PHL13941-0
----Job J000503035-3
-----POLine PH00022137-1
------Lot PHL13100-0
------Lot PHL13101-0
-----POLine PH00022137-2
------Lot PHL13102-0
------Lot PHL13103-0
Post #1459245
Posted Monday, June 3, 2013 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
What is the relationship between these tables? This sort of looks like a hybrid EAV. Do you have to look in the RefModule column in each table to know which table it relates to?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1459270
Posted Monday, June 3, 2013 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
The realtionship is defined using three fields RefModule, RefNum, RefLine.
The RefModule field is in all the tables , thats how it determines which table to look for.

FYI :
The actual design of the tables are different. I just simplified the design to acheive the goal.
For example : the POLine table has fields like po_num, po_line, ref_module, ref_num, ref_line
The Job table has fields like job, suffix, ref_module, ref_num, ref_line.
Post #1459309
Posted Tuesday, June 4, 2013 12:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
Any Luck ?
Post #1459901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse