asp.net treeview from database

andrew67
andrew67 used Ask the Experts™
on
hi all

im trying to populate a treeview from a database my code is as follows

all works ish the only problem is that i should have two root level folders followed by about 2 or 3 sub folders for the first root item.

however i get the following

  Menus

  Menu

   FtpFolderId
   ParentId
   FolderName
   Menu

    FtpFolderId
    ParentId
    FolderName
    Menu

     FtpFolderId
     ParentId
     FolderName
     Menu

      FtpFolderId
      ParentId
      FolderName

   Menu

    FtpFolderId
    ParentId
    FolderName

   Menu

    FtpFolderId
    ParentId
    FolderName

  Menu

   FtpFolderId
   ParentId
   FolderName


when my table only contains this

FtpFolderId, ParentId, FolderName
1                  0                 AA9 Root
2                  1                 Client 1
3                  1                 Client 2
4                  1                 Client 3
5                  2                 Client 1 b
6                  5                 Client 1 b b
7                  0                 AA9 root 2

can anyone help, im almost certain its something basic

thanks
connectionString = WebConfigurationManager.ConnectionStrings["NewAA9"].ConnectionString;
            DataSet dataSet = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                ErrLbl.Text += "populating dataset<br />";
                SqlCommand cmd = new SqlCommand("Sp_GetFtpFolders", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandType = CommandType.StoredProcedure;
                da.Fill(dataSet, "FtpFolders");
                da.Dispose();
            }
            dataSet.DataSetName = "Menus";
            dataSet.Tables[0].TableName = "Menu";
            DataRelation relation = new DataRelation("ParentChild", dataSet.Tables["Menu"].Columns["FtpFolderId"], dataSet.Tables["Menu"].Columns["ParentId"], false);
            relation.Nested = true;
            dataSet.Relations.Add(relation);
            XmlDataSource Data = new XmlDataSource();
            Data.ID = "XmlSource1";
            Data.Data = dataSet.GetXml().ToString();
            FtpFolders.DataSource = Data;
            FtpFolders.DataBind();

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try replacing 0 with null for first level elements
also your tree is not clear
provide snapshot for clear understanding
I think you havn't mentioned how you are populating the nodes. Have a look at the following linke, it helps.
If you have a populating code, please quote that so I can look at it also.

http://aspalliance.com/732_Display_Hierarchical_Data_with_TreeView_in_ASPNET_20
http://aspalliance.com/822

Author

Commented:
hi thanks for your help my xml is attached and ive attached a screen shot of the page
<Menus>
- <Menu>
  <FtpFolderId>1</FtpFolderId> 
  <ParentId>0</ParentId> 
  <FolderName>AA9 Root</FolderName> 
- <Menu>
  <FtpFolderId>2</FtpFolderId> 
  <ParentId>1</ParentId> 
  <FolderName>Client 1</FolderName> 
- <Menu>
  <FtpFolderId>5</FtpFolderId> 
  <ParentId>2</ParentId> 
  <FolderName>Client 1 b</FolderName> 
- <Menu>
  <FtpFolderId>6</FtpFolderId> 
  <ParentId>5</ParentId> 
  <FolderName>Client 1 b b</FolderName> 
  </Menu>
  </Menu>
  </Menu>
- <Menu>
  <FtpFolderId>3</FtpFolderId> 
  <ParentId>1</ParentId> 
  <FolderName>Client 2</FolderName> 
  </Menu>
- <Menu>
  <FtpFolderId>4</FtpFolderId> 
  <ParentId>1</ParentId> 
  <FolderName>Client 3</FolderName> 
  </Menu>
  </Menu>
- <Menu>
  <FtpFolderId>7</FtpFolderId> 
  <ParentId>0</ParentId> 
  <FolderName>AA9 root 2</FolderName> 
  </Menu>
  </Menus>

Open in new window

ee.gif
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
also this is the tutorial i have based mine on

http://aspalliance.com/822

Author

Commented:
think i see where im going wrong now

1. my datafields are named differently so the xslt formating wont work and secondly he has more fields in his db than me.

ill correct them two points then get back to you
Can you try the similar quary and try (instead of the SqlCommand you've used)?
like following
using(SqlConnection conn = newSqlConnection(connStr))
    {
      string sql = "Select FtpFolderId, FolderName, ParentID from Menu";
      SqlDataAdapter da = newSqlDataAdapter(sql, conn);
      da.Fill(ds);
      da.Dispose();
    }

Open in new window

have you done this

FtpFolderId, ParentId, FolderName
1                  NULL               AA9 Root
2                  1                 Client 1
3                  1                 Client 2
4                  1                 Client 3
5                  2                 Client 1 b
6                  5                 Client 1 b b
7                  NULL                AA9 root 2

replace 0 with NULL for first level element as i said you earlier
and also the datarelation you've given false, but the reference doc says true.
your code is working fine
look at this
create your xslt and you are done
ee.gif

Author

Commented:
i tried setting it to true but i get the following error message

This constraint cannot be enabled as not all values have corresponding parent values.

Author

Commented:
ive updated my code so that it now reads as
connectionString = WebConfigurationManager.ConnectionStrings["NewAA9"].ConnectionString;
            DataSet dataSet = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                ErrLbl.Text += "populating dataset, got a connection<br />";
                string sql = "SELECT m.FtpFolderId AS MenuID,m.FolderName AS Text, m.FolderName AS Description,m.ParentId AS ParentID  FROM FtpFolders AS m ORDER BY FtpFolderId";
                //SqlCommand cmd = new SqlCommand("Sp_GetFtpFolders", con);
                //SqlDataAdapter da = new SqlDataAdapter(cmd);
                //cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(sql, connectionString);
                da.Fill(dataSet);
                da.Dispose();
            }
            StreamWriter xmlDoc = new StreamWriter(Server.MapPath("~/pre.xml"), false);
            dataSet.WriteXml(xmlDoc);
            xmlDoc.Close();

            dataSet.DataSetName = "Menus";
            dataSet.Tables[0].TableName = "Menu";
            DataRelation relation = new DataRelation("ParentChild", dataSet.Tables["Menu"].Columns["MenuId"], dataSet.Tables["Menu"].Columns["ParentID"], true);
            relation.Nested = true;
            dataSet.Relations.Add(relation);
            XmlDataSource Data = new XmlDataSource();
            
            Data.ID = "XmlSource1";
            Data.Data = dataSet.GetXml().ToString();
            StreamWriter xmlDoc2 = new StreamWriter(Server.MapPath("~/post.xml"), false);
            dataSet.WriteXml(xmlDoc2);
            xmlDoc2.Close();
            FtpFolders.DataSource = Data;
            FtpFolders.DataBind();

Open in new window

Author

Commented:
im just thinking should i need to attach hte TransForm.xslt file in any way or is it automatically attached to style the xml???
As Rakesh mentioned, can you null if it is 0 atleast in the query, like below?
using(SqlConnection conn = newSqlConnection(connStr))
    {
      string sql = "Select FtpFolderId, FolderName, IF(ParentID = 0, '<Null>', ParentID) from Menu";
      SqlDataAdapter da = newSqlDataAdapter(sql, conn);
      da.Fill(ds);
      da.Dispose();
    }


OR


using(SqlConnection conn = newSqlConnection(connStr))
    {
      string sql = "Select FtpFolderId, FolderName, NullIf(ParentID ,0) from Menu";
      SqlDataAdapter da = newSqlDataAdapter(sql, conn);
      da.Fill(ds);
      da.Dispose();
    }

Open in new window

Set that value to true and use one of the above query, or change itin the database (0 to null as rakesh mentioned), you will be fine I believe.

Author

Commented:
ok done all that, corrected a few errors in the xslt file and its still exactly the same its as though the xslt file isnt formating it in any way

any clues

Author

Commented:
oh sh** man ive just notice that tutorial isnt for a treeview its for an asp menu, any idea how i can conver it to a treeview????

Author

Commented:
the only problem with that is that he has a different method for the children and then for the children children so its very limiting

ive modified my code slightly, i have now put the value 0 back into the table and filled a dataset with the contents of the table. I then call the following method (see code)

the only problem with this is that it doesnt add the children to the parents but just as parents them selves

any clues how i can correct this issue

thanks


protected void PopulateTree(int parentId)
        {
            DataSet ds = ftpDs.FolderData();
            if (ds == null)
            {
                ErrLbl.Text = "dataset not populated<br />";
            }
            else
            {
                ErrLbl.Text += "dataset populated<br />";
                DataView dv = ds.Tables["FtpFolders"].DefaultView;
                dv.RowFilter = "ParentId = " + parentId;
                foreach (DataRowView drv in dv)
                {
                    folderName = new TreeNode();
                    folderName.Text += drv["FolderName"].ToString();
                    AA9FtpStructure.Nodes.Add(folderName);
                    if ((int)drv["NumChild"] != 0)
                    {
                        ErrLbl.Text += "calling children<br />";
                        PopulateTree((int)drv["FtpFolderId"]);
                    }
                }
            }

Open in new window

Author

Commented:

Author

Commented:
well following on from this lovely thing im now at the following position. ive got it to create the firstlevel sub tree but its not creating the second or third level. it going round because i can see from the text i have it outputting but no sub level elements in the tree

any one got any ideas why??
FtpFolderDataSet ftpDs = new FtpFolderDataSet();
        protected void Page_Load(object sender, EventArgs e)
        {
            AA9FtpStructure.Nodes.Clear();
            ErrLbl.Text = "calling function<br />";
            DataSet ds = ftpDs.FolderData();
            DataView dv = ds.Tables["FtpFolders"].DefaultView;
            dv.RowFilter = "ParentId = 0";
            foreach (DataRowView drv in dv)
            {
                TreeNode tnParent = new TreeNode();
                tnParent.Text = drv["FolderName"].ToString();
                tnParent.Expand();
                AA9FtpStructure.Nodes.Add(tnParent);
                if ((int)drv["NumChild"] != 0)
                {
                    ErrLbl.Text += "calling children<br />";
                    PopulateTree(tnParent, (int)drv["FtpFolderId"]);
                }
            }
        }
        protected void PopulateTree(TreeNode parent,int parentId)
        {
            DataSet ds = ftpDs.FolderData();
            if (ds == null)
            {
                ErrLbl.Text = "dataset not populated<br />";
            }
            else
            {
                DataView dv = ds.Tables["FtpFolders"].DefaultView;
                dv.RowFilter = "ParentId = " + parentId;

                ErrLbl.Text += "dataset populated parent id : " + parentId.ToString() + "<br />";
                foreach (DataRowView drv in dv)
                {
                    TreeNode child = new TreeNode();
                    child.Text = drv["FolderName"].ToString() + drv["FtpFolderId"].ToString();
                    child.Collapse();
                    parent.ChildNodes.Add(child);
                    if ((int)drv["NumChild"] != 0)
                    {
                        ErrLbl.Text += "calling children : " + drv["FolderName"].ToString() + " : " + drv["FtpFolderId"].ToString() + "<br />";
                        PopulateTree(child,(int)drv["FtpFolderId"]);
                    }
                }
            }
        }

Open in new window

Author

Commented:
just for another bit of information ive found a bit of a tutorial that leads me to think line 44 above should read

PopulateTree(child.ChildNodes,(int)drv["FtpFolderId"]);

not

PopulateTree(child,(int)drv["FtpFolderId"]);

however when i change it i get the following error message

cannot convert from 'System.Web.UI.WebControls.TreeNodeCollection' to 'System.Web.UI.WebControls.TreeNode'

Author

Commented:
all is working now for future reference I have now change this line

child.Collapse();

to this

child.Expand();

just wonder is it worth me uploading the complete code for future reference???

let me know
good to hear about that.
good to post the complete answer code for future reference.

Author

Commented:
ill post the complete code monday morning when im in work next, im also planning on writing a different version using <ul><li> just a bit disapointed that the default one uses loads of nested tables

Author

Commented:
Hi all sorry i havent uploaded the completed source code yet i will do it today just been a tad busy

andrew
Commented:
ok folks ive uploaded the complete solution but the only files in there worth looking at are FtpFoloders.aspx and FtpFolderDataSet.cs ive had to rename the following files to allow them to be uploaded but as you can see its easy enough to name them back

chp8eg1.csproj.txt: user
chp8eg1.txt: suo
Default.txt: aspx
folderStructure2.txt: aspx
FtpFolders.aspx: txt
TransForm.txt: xslt

good luck and im sure that some one with more time will be able to streamline it even more


treeview.zip

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial