asked on

How to put this inisde a child node?

Hi, my current TreeView looks something like this...
-X01
-store
-brand
-product
-101
-101.abc
-101.xyz
-102
-102.abc
-102.xyz
101.abc,101.xyz,102.abc and 102.xyz is read from the 2nd localSQL statement.
But what I wanted was for the data from the new database to be under the respective node...
-101
-101.abc
-101.xyz
+102

The following is my code the // part is the part where i connect to the new database and add it to the node.
Thanks for any help in advance.

foreach (DataRow dr in ordt.Rows)
                    {
                        nodeCheck = new TreeNode();
                        nodeCheck.Name = dr[0].ToString();                        
                        strText = dr[2].ToString();
                        strDesc = dr[3].ToString();
                        strList = strText.Split('.');
                        nodeCheck.Text = strList[strList.Length - 1];
                        if (dr[1] != DBNull.Value)
                        {
                            node = treeview1.Nodes.Find(dr[1].ToString(), true);
                            if (strList.Length == 5)
                                {
                                    nodeCheck.Text = nodeCheck.Text;
                                    node[0].Nodes.Add(nodeCheck);
 
                                    //localSQL = "SELECT * FROM PRODUCT_PT WHERE PKEYID LIKE '" + dr[0].ToString() + "'";
                                    //OracleCommand cmdOracle1 = new OracleCommand(localSQL, localConnection);
                                    //cmdOracle1.CommandType = CommandType.Text;
                                    //drOracle = cmdOracle1.ExecuteReader();
                                    //while (drOracle.Read())
                                    //{
                                    //    nodeCheck = new TreeNode();
                                    //    nodeCheck.Name = dr[0].ToString();
                                    //    strText = drOracle.GetOracleString(1).ToString();
                                    //    nodeCheck.Text = strText.ToString();
                                    //    node[0].Nodes.Add(nodeCheck);
 
                                    //nodeCheck.Tag = drOracle.GetOracleValue(0).ToString();
                                    //}
                                    //drOracle.Close();
                                }
                                else
                                { node[0].Nodes.Add(nodeCheck); }
                        }
                        else
                        {
                            treeview1.Nodes.Add(nodeCheck);
                        } 
                    }

Open in new window

mrjoltcola

If you are using 2 Oracle databases, you can just setup a database link and connect directly to one database only. Then you refer to the table as table@remote

Not sure if that helps, as I don't quite understand what you are doing. Let me know if I can help more or if you want syntax for creating the database link. Your DBA has to do it, or grant you "create database link" privilege.

csharp_learner

ASKER

Do you mean the Union function in the SQL statement?
I'm open to hearing u're database link option,since my current code is giving me some Context error due to the 2nd connection.
ps:both databases have the same column PKEYID.

mrjoltcola

Sample uses ORA1 and ORA2, change to match your local and remote.

ORA2 must be in the TNSNAMES.ORA file of the ORA1 database

Verify from ORA1 server by doing: tnsping ORA2

Then, either as DBA create a public database link, or have your DBA grant "create database link to <user>"

Sample from SQLPlus/etc. to create a link to the SCOTT user on the ORA2 database

SQL> create database link ORA2 connect to scott identified by tiger using 'ORA2';

SQL> select * from EMP@ORA2;

mrjoltcola

And you can refer to tables on both databases in 1 query. Say I have EMP table on local database and remote ORA2.

select a.* from EMP a, EMP@ORA2 b
where a.ID = b.id

csharp_learner

ASKER

I've tried to connect using the SQL Editor in Oracle but seems my statement is wrong.
This is my 1st SQL query:
SELECT KEYID,PKEYID, PRODUCT_NAME,PRODUCT_DESC FROM PRODUCT_NODE START WITH PKEYID IS NULL CONNECT BY PRIOR KEYID = PKEYID ORDER BY PRODUCT_NAME
and this is my 2nd SQL query:
SELECT KEYID,PRODUCT_NAME,PRODUCT_DESC,PKEYID,PRODUCT_TYPE FROM PRODUCT_PT

HarryNS

Can you give me the result of the above mentioned 2 queries?

csharp_learner

ASKER

Thanks for offering your help again Harry.
Actually you help solve the 1st part of the code
https://www.experts-exchange.com/questions/24258181/TreeView-from-Database.html

Now i'm trying to connect to a new databse using dr[0].ToString() where it'll grab the KEYID from the previous databse to the current databse KEYID where there will be a few items under it.

Example: after reading 101 which is the last child node in the old databse the new connection of database will grab 101's KEYID '41' then under the new database there will be a list of items under the same KEYID column
(1st databse)
KEYID PKEYID PRODUCT_NAME
41 31 X01.Alpha.metal.cabin.101
(2nd databse)
KEYID PKEYID PRODUCT_NAME
201 41 abc
202 41 xyz

csharp_learner

ASKER

sorry my mistake the 2nd database table is like this
(2nd databse)
KEYID KEYID PRODUCT_NAME
201 41 abc
202 41 xyz

HarryNS

SELECT KEYID, PKEYID, PRODUCT_NAME, PRODUCT_DESC
FROM
PRODUCT_NODE START
WITH PKEYID IS NULL CONNECT BY PRIOR KEYID = PKEYID ORDER BY PRODUCT_NAME

UNION ALL
SELECT KEYID, PKEYID, PRODUCT_NAME, PRODUCT_DESC, PRODUCT_TYPE
FROM PRODUCT_PT

Can you make the result as One. This will give you good application performance and easy to use as well.

csharp_learner

ASKER

I tried
SELECT KEYID,PKEYID, PRODUCT_NAME,PRODUCT_DESC FROM PRODUCT_NODE START WITH PKEYID IS NULL CONNECT BY PRIOR KEYID = PKEYID ORDER BY PRODUCT_NAME UNION ALL SELECT KEYID, PKEYID, PRODUCT_NAME, PRODUCT_DESC, PRODUCT_TYPE FROM PRODUCT_PT
but i got an error SQL command not properly ended @ "union"

ASKER CERTIFIED SOLUTION

HarryNS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csharp_learner

ASKER

When i place the ORDER BY PRODUCT_NAME at the end it prompt "query block has incorrect number of result columns"
I tried to simplify every thing by SELECT * FROM PRODUCT_NODE START WITH PKEYID IS NULL CONNECT BY PRIOR KEYID = PKEYID UNION ALL SELECT * FROM PRODUCT_PT but ehe same "query block..."error came out.

HarryNS

Include Product Type in your first query or remove it from the second query. When you use UNION, you need to produce similar kind of rows.

csharp_learner

ASKER

Sorry i've read wrongly the column name of the 2nd database table.

SELECT KEYID,PKEYID, PRODUCT_NAME,PRODUCT_DESC FROM PRODUCT_NODE
UNION ALL
SELECT KEYID, PKEYID, PRODUCT_DT_NAME, PRODUCT_DT_DESC, PRODUCT_TYPE FROM PRODUCT_PT

Since only the KEYID and PKEYID columns are the same does it mean i can't use this UNION function?

HarryNS

No, No. of columns should be same. First query is having Key ID, PKey ID, Name and Desc. Second query is having Key ID, PKey ID, Name, Desc and TYPE. Type is extra in second query. If you remove Product_type in 2nd query this will work fine. or include Type in first query also.

csharp_learner

ASKER

Sorry for the late reply but in my 2nd query it's named PRODUCT_DT_NAME and PRODUCT_DT_NAME, in the 1st query it's named PRODUCT_NAME and PRODUCT_DESC.