?
Solved

How to populate Windows.Systems.Forms.TreeView from mySQL db

Posted on 2005-03-02
8
Medium Priority
?
2,289 Views
Last Modified: 2008-02-20
I'm trying to populate a TreeView in C#.net from three tables in a mySQL database.

I am having problems compiling it though. For the life of me I can't think where the problem lies. Any help would be greatly appreciated!!

What follows is extracts from the 'TreeView' form. The database connection is masked, but otherwise the rest of the code is as I have it here!! I think the trouble starts when I try and declare the relationships between the nodes. Thanks again!



using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

......


private void InitializeComponent() {
                  this.TreeView1 = new System.Windows.Forms.TreeView();
                  this.SuspendLayout();
                  //
                  // TreeView1
                  //
                        
                  this.TreeView1.ImageIndex = -1;
                  this.TreeView1.Location = new System.Drawing.Point(16, 16);
                  this.TreeView1.Name = "TreeView1";
                  
                  
                  
                  MySqlConnection conn = new MySqlConnection("server=127.0.0.1;user id=username;password=password;database=databasename");
                  conn.Open();
                  //declare dataset
                  DataSet treeDS = new DataSet();
                  
                  //create root data adapter
                  
                  MySqlDataAdapter tree1DA = new MySqlDataAdapter("SELECT articatid, catdesc FROM articat", conn);
                  
                  //create second level data adapter
                  
                  MySqlDataAdapter tree2DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc FROM artisubcat AS t1, articat AS t2 WHERE t1.articatid=t2.articatid", conn);
                  
                  //create third level data adapter
                  
                  MySqlDataAdapter tree3DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc t1.articletitle FROM articles AS t1, artisubcat AS t2 WHERE t1.articatid=t2.articatid, t1.artisubcatid=t2.artisubcatid", conn);
                  
                  tree1DA.Fill(treeDS,"articat");
                  tree2DA.Fill(treeDS,"artisubcat");
                  tree3DA.Fill(treeDS,"articles");
                  
                  conn.Close();


// I think the trouble starts here!! //

            
                  //create relations
                  
                  treeDS.Relations.Add("SubcatToCat", treeDS.Tables("articat").Columns("articatid"), treeDS.Tables("artisubcat").Columns("articatid"));
                  treeDS.Relations.Add("ArticleToSubcat", treeDS.Tables("artisubcat").Columns("artisubcatid"), treeDS.Tables("articles").Columns("artisubcatid"));
                  
                  //create TreeView datatypes
                  
                  TreeNode nodeCat = new TreeNode();
                  TreeNode nodeSubCat = new TreeNode();
                  TreeNode nodeArticle = new TreeNode();
                  
                  DataRow rowCat = new DataRow();
                  DataRow rowSubCat = new DataRow();
                  DataRow rowArticle = new DataRow();
                  
                  foreach (DataRow rowCat in treeDS.Tables("articat").Rows)
                        nodeCat = new TreeNode();
                        nodeCat.Text = rowCat("catdesc");
                        TreeView1.Nodes.Add(nodeCat);
                        
                        foreach (DataRow rowSubCat in rowCat.GetChildRows("SubcatToCat"))
                        nodeSubCat = new TreeNode();
                        nodeSubCat.Text = rowSubCat("subcatdesc");
                        nodeCat.Nodes.Add(nodeSubCat);
                        
                        foreach (DataRow rowArticle in rowSubCat.GetChildRows("ArticleToSubcat"))
                        nodeArticle = new TreeNode();
                        nodeArticle.Text = rowArticle("articletitle");
                        nodeSubCat.Nodes.Add(nodeArticle);
                        
                  
                  
                  this.TreeView1.SelectedImageIndex = -1;
                  this.TreeView1.Size = new System.Drawing.Size(256, 216);
                  this.TreeView1.TabIndex = 0;
0
Comment
Question by:alexnorth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:jonvaughan
ID: 13446576
What are the compilation errors ?

Can you add the compete Class code too ?
0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 2000 total points
ID: 13446710
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13446838
try this one.... you forgot to put {} around for loops



=======================
using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

......


private void InitializeComponent()
{
               this.TreeView1 = new System.Windows.Forms.TreeView();
               this.SuspendLayout();
               //
               // TreeView1
               //
                   
               this.TreeView1.ImageIndex = -1;
               this.TreeView1.Location = new System.Drawing.Point(16, 16);
               this.TreeView1.Name = "TreeView1";
               
               
               
               MySqlConnection conn = new MySqlConnection("server=127.0.0.1;user id=username;password=password;database=databasename");
               conn.Open();
               //declare dataset
               DataSet treeDS = new DataSet();
               
               //create root data adapter
               
               MySqlDataAdapter tree1DA = new MySqlDataAdapter("SELECT articatid, catdesc FROM articat", conn);
               
               //create second level data adapter
               
               MySqlDataAdapter tree2DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc FROM artisubcat AS t1, articat AS t2 WHERE t1.articatid=t2.articatid", conn);
               
               //create third level data adapter
               
               MySqlDataAdapter tree3DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc t1.articletitle FROM articles AS t1, artisubcat AS t2 WHERE t1.articatid=t2.articatid, t1.artisubcatid=t2.artisubcatid", conn);
               
               tree1DA.Fill(treeDS,"articat");
               tree2DA.Fill(treeDS,"artisubcat");
               tree3DA.Fill(treeDS,"articles");
               
               conn.Close();


// I think the trouble starts here!! //

         
               //create relations
               
               treeDS.Relations.Add("SubcatToCat", treeDS.Tables("articat").Columns("articatid"), treeDS.Tables("artisubcat").Columns("articatid"));
               treeDS.Relations.Add("ArticleToSubcat", treeDS.Tables("artisubcat").Columns("artisubcatid"), treeDS.Tables("articles").Columns("artisubcatid"));
               
               //create TreeView datatypes
               
               TreeNode nodeCat = new TreeNode();
               TreeNode nodeSubCat = new TreeNode();
               TreeNode nodeArticle = new TreeNode();
               
               DataRow rowCat = new DataRow();
               DataRow rowSubCat = new DataRow();
               DataRow rowArticle = new DataRow();
               
               foreach (DataRow rowCat in treeDS.Tables("articat").Rows)
                     {
                    nodeCat = new TreeNode();
                    nodeCat.Text = rowCat("catdesc");
                    TreeView1.Nodes.Add(nodeCat);
                   
                    foreach (DataRow rowSubCat in rowCat.GetChildRows("SubcatToCat"))
                              {
                          nodeSubCat = new TreeNode();
                          nodeSubCat.Text = rowSubCat("subcatdesc");
                          nodeCat.Nodes.Add(nodeSubCat);
                          
                          foreach (DataRow rowArticle in rowSubCat.GetChildRows("ArticleToSubcat"))
                                    {
                                nodeArticle = new TreeNode();
                                nodeArticle.Text = rowArticle("articletitle");
                                nodeSubCat.Nodes.Add(nodeArticle);
                                    }
                              }
                    }
               
               
               this.TreeView1.SelectedImageIndex = -1;
               this.TreeView1.Size = new System.Drawing.Size(256, 216);
               this.TreeView1.TabIndex = 0;

=======================================

itsvtk
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:alexnorth
ID: 13447772
Thanks for that guys.

itsvtk: I put in the brackets, but on compile I'm now getting the following error message...

enigmakb\TopicTree.cs(89,59): error CS0118: 'System.Data.DataSet.Tables' denotes a 'property' where a 'method' was expected
enigmakb\TopicTree.cs(89,106): error CS0118: 'System.Data.DataSet.Tables' denotes a 'property' where a 'method' was expected
enigmakb\TopicTree.cs(90,63): error CS0118: 'System.Data.DataSet.Tables' denotes a 'property' where a 'method' was expected
enigmakb\TopicTree.cs(90,116): error CS0118: 'System.Data.DataSet.Tables' denotes a 'property' where a 'method' was expected
enigmakb\TopicTree.cs(98,33): error CS1501: No overload for method 'DataRow' takes '0' arguments
enigmakb\TopicTree.cs(99,36): error CS1501: No overload for method 'DataRow' takes '0' arguments
enigmakb\TopicTree.cs(100,37): error CS1501: No overload for method 'DataRow' takes '0' arguments
enigmakb\TopicTree.cs(102,50): error CS0118: 'System.Data.DataSet.Tables' denotes a 'property' where a 'method' was expected

There has to be something fundamental going wrong here, but I just can't see it!!
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13447848
Can you type the 89, 90, 98, 99, 100, 102 lines code here....
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13447860
Yes Got the problem try this code

=======================
using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

......


private void InitializeComponent()
{
               this.TreeView1 = new System.Windows.Forms.TreeView();
               this.SuspendLayout();
               //
               // TreeView1
               //
                   
               this.TreeView1.ImageIndex = -1;
               this.TreeView1.Location = new System.Drawing.Point(16, 16);
               this.TreeView1.Name = "TreeView1";
               
               
               
               MySqlConnection conn = new MySqlConnection("server=127.0.0.1;user id=username;password=password;database=databasename");
               conn.Open();
               //declare dataset
               DataSet treeDS = new DataSet();
               
               //create root data adapter
               
               MySqlDataAdapter tree1DA = new MySqlDataAdapter("SELECT articatid, catdesc FROM articat", conn);
               
               //create second level data adapter
               
               MySqlDataAdapter tree2DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc FROM artisubcat AS t1, articat AS t2 WHERE t1.articatid=t2.articatid", conn);
               
               //create third level data adapter
               
               MySqlDataAdapter tree3DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc t1.articletitle FROM articles AS t1, artisubcat AS t2 WHERE t1.articatid=t2.articatid, t1.artisubcatid=t2.artisubcatid", conn);
               
               tree1DA.Fill(treeDS,"articat");
               tree2DA.Fill(treeDS,"artisubcat");
               tree3DA.Fill(treeDS,"articles");
               
               conn.Close();


// I think the trouble starts here!! //

         
               //create relations
               
               treeDS.Relations.Add("SubcatToCat", treeDS.Tables("articat").Columns("articatid"), treeDS.Tables("artisubcat").Columns("articatid"));
               treeDS.Relations.Add("ArticleToSubcat", treeDS.Tables("artisubcat").Columns("artisubcatid"), treeDS.Tables("articles").Columns("artisubcatid"));
               
               //create TreeView datatypes
               
               TreeNode nodeCat = new TreeNode();
               TreeNode nodeSubCat = new TreeNode();
               TreeNode nodeArticle = new TreeNode();
               
               DataRow rowCat = new DataRow();
               DataRow rowSubCat = new DataRow();
               DataRow rowArticle = new DataRow();
               
               foreach (DataRow rowCat in treeDS.Tables("articat").Rows)
                  {
                    nodeCat = new TreeNode();
                    nodeCat.Text = rowCat.Item("catdesc");
                    TreeView1.Nodes.Add(nodeCat);
                   
                    foreach (DataRow rowSubCat in rowCat.GetChildRows("SubcatToCat"))
                         {
                         nodeSubCat = new TreeNode();
                         nodeSubCat.Text = rowSubCat.Item("subcatdesc");
                         nodeCat.Nodes.Add(nodeSubCat);
                         
                         foreach (DataRow rowArticle in rowSubCat.GetChildRows("ArticleToSubcat"))
                              {
                              nodeArticle = new TreeNode();
                              nodeArticle.Text = rowArticle.Item("articletitle");
                              nodeSubCat.Nodes.Add(nodeArticle);
                              }
                         }
                 }
               
               
               this.TreeView1.SelectedImageIndex = -1;
               this.TreeView1.Size = new System.Drawing.Size(256, 216);
               this.TreeView1.TabIndex = 0;

=======================================
0
 

Author Comment

by:alexnorth
ID: 13450023
Well, we're making some progress - I think! I've solved the 'System.Data.DataTable.Tables' / '.Columns' denotes a 'property where a 'method' was expected ...

It was due to C# using [ ] not ( ) for indexes! I now have 9 new errors to play with they are as follows...

enigmakb\TopicTree.cs(94,33): error CS1501: No overload for method 'DataRow' takes '0' arguments
enigmakb\TopicTree.cs(95,36): error CS1501: No overload for method 'DataRow' takes '0' arguments
enigmakb\TopicTree.cs(96,37): error CS1501: No overload for method 'DataRow' takes '0' arguments
enigmakb\TopicTree.cs(98,33): error CS0136: A local variable named 'rowCat' cannot be declared in this scope because it would give a different meaning to 'rowCat', which is already used in a 'parent or current' scope to denote something else
enigmakb\TopicTree.cs(101,36): error CS0117: 'System.Data.DataRow' does not contain a definition for 'Item'
enigmakb\TopicTree.cs(104,38): error CS0136: A local variable named 'rowSubCat' cannot be declared in this scope because it would give a different meaning to 'rowSubCat', which is already used in a 'parent or current' scope to denote something else
enigmakb\TopicTree.cs(107,44): error CS0117: 'System.Data.DataRow' does not contain a definition for 'Item'
enigmakb\TopicTree.cs(110,43): error CS0136: A local variable named 'rowArticle' cannot be declared in this scope because it would give a different meaning to 'rowArticle', which is already used in a 'parent or current' scope to denote something else
enigmakb\TopicTree.cs(113,50): error CS0117: 'System.Data.DataRow' does not contain a definition for 'Item'

Build complete -- 9 errors, 0 warnings


* * *  < < <  > > >  * * *

Here is the new code (for the whole form - in case I've missed something daft)


* * *  < < <  > > >  * * *


      public class TopicTree : System.Windows.Forms.Form
      {
            private System.Windows.Forms.TreeView treeView1;
            public TopicTree()
            {
                  //
                  // The InitializeComponent() call is required for Windows Forms designer support.
                  //
                  InitializeComponent();
                  
                  //
                  // TODO: Add constructor code after the InitializeComponent() call.
                  //
            }
            
            #region Windows Forms Designer generated code
            /// <summary>
            /// This method is required for Windows Forms designer support.
            /// Do not change the method contents inside the source code editor. The Forms designer might
            /// not be able to load this method if it was changed manually.
            /// </summary>
            private void InitializeComponent() {
                  this.treeView1 = new System.Windows.Forms.TreeView();
                  this.SuspendLayout();
                  //
                  // treeView1
                  //
                  this.treeView1.ImageIndex = -1;
                  this.treeView1.Location = new System.Drawing.Point(64, 40);
                  this.treeView1.SelectedImageIndex = -1;
                  this.treeView1.TabIndex = 0;
this.treeView1.Name = "treeView1";
               
               
               
               MySqlConnection conn = new MySqlConnection("server=127.0.0.1;user id=username;password=password;database=databasename");
               conn.Open();
               //declare dataset
               DataSet treeDS = new DataSet();
               
               //create root data adapter
               
               MySqlDataAdapter tree1DA = new MySqlDataAdapter("SELECT articatid, catdesc FROM articat", conn);
               
               //create second level data adapter
               
               MySqlDataAdapter tree2DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc FROM artisubcat AS t1, articat AS t2 WHERE t1.articatid=t2.articatid", conn);
               
               //create third level data adapter
               
               MySqlDataAdapter tree3DA = new MySqlDataAdapter("SELECT t1.artisubcatid, t1.subcatdesc t1.articletitle FROM articles AS t1, artisubcat AS t2 WHERE t1.articatid=t2.articatid, t1.artisubcatid=t2.artisubcatid", conn);
               
               tree1DA.Fill(treeDS,"articat");
               tree2DA.Fill(treeDS,"artisubcat");
               tree3DA.Fill(treeDS,"articles");
               
               conn.Close();


// I think the trouble starts here!! //

         
               //create relations
               
               treeDS.Relations.Add("SubcatToCat", treeDS.Tables["articat"].Columns["articatid"], treeDS.Tables["artisubcat"].Columns["articatid"]);
               treeDS.Relations.Add("ArticleToSubcat", treeDS.Tables["artisubcat"].Columns["artisubcatid"], treeDS.Tables["articles"].Columns["artisubcatid"]);
               
               //create TreeView datatypes
               
               TreeNode nodeCat = new TreeNode();
               TreeNode nodeSubCat = new TreeNode();
               TreeNode nodeArticle = new TreeNode();
               
               DataRow rowCat = new DataRow();
               DataRow rowSubCat = new DataRow();
               DataRow rowArticle = new DataRow();
               
               foreach (DataRow rowCat in treeDS.Tables["articat"].Rows)
                  {
                    nodeCat = new TreeNode();
                    nodeCat.Text = rowCat.Item("catdesc");
                    treeView1.Nodes.Add(nodeCat);
                   
                    foreach (DataRow rowSubCat in rowCat.GetChildRows("SubcatToCat"))
                         {
                         nodeSubCat = new TreeNode();
                         nodeSubCat.Text = rowSubCat.Item("subcatdesc");
                         nodeCat.Nodes.Add(nodeSubCat);
                         
                         foreach (DataRow rowArticle in rowSubCat.GetChildRows("ArticleToSubcat"))
                              {
                              nodeArticle = new TreeNode();
                              nodeArticle.Text = rowArticle.Item("articletitle");
                              nodeSubCat.Nodes.Add(nodeArticle);
                              }
                         }
                 }
               
0
 

Author Comment

by:alexnorth
ID: 13454840
Thanks for your help... decided on trying a completely different tack - which worked!!

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

namespace WindowsApplication11
{
     /// <summary>
     /// Summary description for Form1.
     /// </summary>
     public class Form1 : System.Windows.Forms.Form
     {
            private System.Windows.Forms.TreeView treeView1;
          /// <summary>
          /// Required designer variable.
          /// </summary>
          private System.ComponentModel.Container components = null;

          public Form1()
          {
               //
               // Required for Windows Form Designer support
               //
               InitializeComponent();

               //
               // TODO: Add any constructor code after InitializeComponent call
               //
          }

          /// <summary>
          /// Clean up any resources being used.
          /// </summary>
          protected override void Dispose( bool disposing )
          {
               if( disposing )
               {
                    if (components != null)
                    {
                         components.Dispose();
                    }
               }
               base.Dispose( disposing );
          }

          #region Windows Form Designer generated code
          /// <summary>
          /// Required method for Designer support - do not modify
          /// the contents of this method with the code editor.
          /// </summary>
          private void InitializeComponent() {
                  this.treeView1 = new System.Windows.Forms.TreeView();
                  this.SuspendLayout();
                  //
                  // treeView1
                  //
                  this.treeView1.ImageIndex = -1;
                  this.treeView1.Location = new System.Drawing.Point(8, 8);
                  this.treeView1.Name = "treeView1";
                  this.treeView1.SelectedImageIndex = -1;
                  this.treeView1.Size = new System.Drawing.Size(152, 248);
                  this.treeView1.TabIndex = 0;
                  //
                  // Form1
                  //
                  this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
                  this.ClientSize = new System.Drawing.Size(292, 266);
                  this.Controls.Add(this.treeView1);
                  this.Name = "Form1";
                  this.Text = "Form1";
                  this.Load += new System.EventHandler(this.Form1Load);
                  this.ResumeLayout(false);
            }
          #endregion

          private DataSet BuildDataSet()
          {
                
                 MySqlConnection conn = new MySqlConnection("server=127.0.0.1;user id=username;password=password;database=dbname");
               conn.Open();
               
               MySqlDataAdapter tree1DA = new MySqlDataAdapter("SELECT * FROM categories", conn);
               
               //declare dataset
               
               DataSet ds = new DataSet();
               
                     tree1DA.Fill(ds,"cat");
               
               
               return ds;
          }

          /// <summary>
          /// The main entry point for the application.
          /// </summary>
          [STAThread]
          static void Main()
          {
               Application.Run(new Form1());
          }
          private TreeNode BuildNode(string Text)
          {
               return new TreeNode(Text);
          }

          private DataSet ds;

          public  void FillChildren(TreeNode _Parent, int _ParentID)
          {
               DataView Data = new DataView(ds.Tables["cat"]);
               Data.RowFilter = "ParentId = " + _ParentID;
               foreach(System.Data.DataRowView foo in Data)
               {
                    TreeNode t = BuildNode((string)foo["Name"]) ;
                    FillChildren(t, (int) foo["id"]);
                    _Parent.Nodes.Add(t) ;
               }
          }
         
          void Form1Load(object sender, System.EventArgs e)
          {
                ds = this.BuildDataSet();
               DataView Data = new DataView(ds.Tables["cat"]);
               Data.RowFilter = "ParentId = " + 0;
               foreach(System.Data.DataRowView foo in Data)
               {
                    TreeNode t = BuildNode((string)foo["Name"]) ;
                    FillChildren(t, (int) foo["id"]);
                    this.treeView1.Nodes.Add(t);
               }
          }
         
     }
}
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question