+ Post New Thread
Results 1 to 6 of 6
Web Development Thread, ASP.net c# treeview from SQL in Coding and Web Development; Hi I am trying to write an asp page to iterate through my knowledge base (SQL table) i.e Category ->Question ...
  1. #1

    Join Date
    Jun 2007
    Location
    Middlesbrough
    Posts
    143
    Thank Post
    17
    Thanked 10 Times in 10 Posts
    Rep Power
    16

    ASP.net c# treeview from SQL

    Hi I am trying to write an asp page to iterate through my knowledge base (SQL table)

    i.e

    Category
    ->Question
    ->Answer
    Category
    Category

    Ive pasted the code below, what im having problems with is getting the 3rd level to use the parent nodes text value in the sql string *1

    Any help will be greatfully appreciated

    Kindest regards

    Damien

    PS this is the output i am getting



    Blackberry
    Cannot find table 0.
    PARIS
    Input string was not in a correct format.

    Blackberry and Paris are categories.

    Cheers SYNACK have altered that line.



    Code:
    <%@ Page Language="C#" Debug="true" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="ICCMeSD_KnowledgeCP_Default" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <script runat="server">
    
      void PopulateNode(Object sender, TreeNodeEventArgs e)
      {
      
     
    
        // Call the appropriate method to populate a node at a particular level.
        switch(e.Node.Depth)
        {
        
          case 0:
            // Populate the first-level nodes.
            PopulateCategories(e.Node);
            
            break;
          case 1:
            // Populate the second-level nodes.
            PopulateProducts(e.Node);
            break;
           case 2:
           PopulateDetails (e.Node);
           break;
          default:
            // Do nothing.
            break;
        }
    
      }
    
      void PopulateCategories(TreeNode node)
      {
      
    
        // Query for the product categories. These are the values
        // for the second-level nodes.
        DataSet ResultSet = RunQuery("SELECT DISTINCT Category FROM Knowledge_Request WHERE Category !='' ");
        
        // Create the second-level nodes.
        if(ResultSet.Tables.Count > 0)
        {
    
          // Iterate through and create a new node for each row in the query results.
          // Notice that the query results are stored in the table of the DataSet.
          foreach (DataRow row in ResultSet.Tables[0].Rows)
          {
    
            // Create the new node. Notice that the CategoryId is stored in the Value property 
            // of the node. This will make querying for items in a specific category easier when
            // the third-level nodes are created. 
            
            TreeNode newNode = new TreeNode();
            newNode.Text = row["Category"].ToString();
            newNode.Value = row["Category"].ToString();
            // Set the PopulateOnDemand property to true so that the child nodes can be 
            // dynamically populated.
            newNode.PopulateOnDemand = true;
    
            // Set additional properties for the node.
            newNode.SelectAction = TreeNodeSelectAction.Expand;
    
            // Add the new node to the ChildNodes collection of the parent node.
            node.ChildNodes.Add(newNode);
    
          }
    
        }
    
      }
    
      void PopulateProducts(TreeNode node)
      {
      if(!IsPostBack){   LinksTreeView.Attributes.Add("onclick", "return OnTreeClick(event)");}
        // Query for the products of the current category. These are the values
        // for the third-level nodes.
     
    *1
       DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category=" + node.Text);
    
       
    
          // Iterate through and create a new node for each row in the query results.
          // Notice that the query results are stored in the table of the DataSet.
          foreach (DataRow row in ResultSet.Tables[0].Rows)
          {
    
            // Create the new node.
            TreeNode NewNode = new TreeNode(row["PRJNAME"].ToString());
    
            // Set the PopulateOnDemand property to false, because these are leaf nodes and
            // do not need to be populated.
            NewNode.PopulateOnDemand = true;
    
            // Set additional properties for the node.
            NewNode.SelectAction = TreeNodeSelectAction.Expand;
    
            // Add the new node to the ChildNodes collection of the parent node.
            node.ChildNodes.Add(NewNode);
    
          }
    
        
    
      }
      void PopulateDetails(TreeNode node)
      {
      
    
        // Query for the products of the current category. These are the values
        // for the forth-level nodes.
        DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category='Paris'");
    
       
    
          // Iterate through and create a new node for each row in the query results.
          // Notice that the query results are stored in the table of the DataSet.
          foreach (DataRow row in ResultSet.Tables[0].Rows)
          {
    
            // Create the new node.
            TreeNode NewNode = new TreeNode(row["PRJOBJECTIVES"].ToString());
    
            // Set the PopulateOnDemand property to false, because these are leaf nodes and
            // do not need to be populated.
            NewNode.PopulateOnDemand = true;
    
            // Set additional properties for the node.
            NewNode.SelectAction = TreeNodeSelectAction.Expand;
    
            // Add the new node to the ChildNodes collection of the parent node.
            node.ChildNodes.Add(NewNode);
    
          }
    
        
    
      }
    
      DataSet RunQuery(String QueryString)
      {
    
        // Declare the connection string. This example uses Microsoft SQL Server 
        // and connects to the EWORK.
        String ConnectionString = "*******"; 
    
        SqlConnection DBConnection = new SqlConnection(ConnectionString);
        SqlDataAdapter DBAdapter;
        DataSet ResultsDataSet = new DataSet();
    
        try
        {
    
          // Run the query and create a DataSet.
          DBAdapter = new SqlDataAdapter(QueryString, DBConnection);
          DBAdapter.Fill(ResultsDataSet);
    
          // Close the database connection.
          DBConnection.Close();
    
        }
        catch(Exception ex)
        {
    
          // Close the database connection if it is still open.
          if(DBConnection.State == ConnectionState.Open)
          {
            DBConnection.Close();
          }
    
          Message.Text = "Unable to connect to the database.";
    
        }
    
        return ResultsDataSet;
    
      }
    
    </script>
    
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        
    </head>
    
    <body>
        <form id="form1" runat="server">
            
        <asp:TreeView id="LinksTreeView"
            Font-Names= "Arial"
            EnableClientScript="true"
            PopulateNodesFromClient="true"  
            OnTreeNodePopulate="PopulateNode"
            ImageSet="XPFileExplorer"
            runat="server">
    
            <Nodes>
    
              <asp:TreeNode SelectAction="Expand" PopulateOnDemand="true"/>
    
            </Nodes>
    
          </asp:TreeView>
    
          <br /><br />
    
          <asp:Label id="Message" runat="server"/>
    
        </form>
    </body>
    </html>
    Last edited by damienharrison; 27th January 2009 at 03:53 PM. Reason: Addition

  2. #2

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,239
    Thank Post
    882
    Thanked 2,742 Times in 2,316 Posts
    Blog Entries
    11
    Rep Power
    784
    Just a quick thought but don't you need to terminate that query string before concatinating the node name?

    try changing
    Code:
    DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category=+node.Text");
    to
    Code:
    DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category=" + node.Text);

  3. Thanks to SYNACK from:

    damienharrison (27th January 2009)

  4. #3

    Join Date
    Oct 2005
    Posts
    62
    Thank Post
    7
    Thanked 20 Times in 17 Posts
    Rep Power
    22
    OK, well you don't say exactly what problem or error message you're seeing, but looking at it purely from an SQL point of view, shouldn't the line:

    Code:
    DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category=+node.Text");
    Actually be:

    Code:
    DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category='" & node.Text & "'");
    It's usual to wrap single-quotes (') around alphanumeric values in a WHERE clause.

    What value are you getting in ResultSet.Tables[0].Rows.Count?

    Peter

  5. Thanks to PeterH from:

    damienharrison (27th January 2009)

  6. #4

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,239
    Thank Post
    882
    Thanked 2,742 Times in 2,316 Posts
    Blog Entries
    11
    Rep Power
    784
    Quote Originally Posted by PeterH View Post
    It's usual to wrap single-quotes (') around alphanumeric values in a WHERE clause.
    Good catch I missed that, can't remember if C# will concatinate with & characters though he may need to use + characters instead

  7. Thanks to SYNACK from:

    damienharrison (27th January 2009)

  8. #5

    Join Date
    Jun 2007
    Location
    Middlesbrough
    Posts
    143
    Thank Post
    17
    Thanked 10 Times in 10 Posts
    Rep Power
    16
    Hi Peter

    That seems to work except asp.net throws an error re ampersands replaced them with +

    DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category='" + node.Text + "'");

    Now i appear to be getting another level.

    Thank you both for your extremely quick responses


  9. #6

    Join Date
    Feb 2012
    Location
    dsf df
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    asp.net treeview

    check this asp.net treeview , in C# and vb.net

    asp.net-informations.com/webcontrols/asp-net-treeview.htm

    delm

SHARE:
+ Post New Thread

Similar Threads

  1. Allow ASP .Net 2 in IIS Server 2003 R2
    By FN-GM in forum Windows
    Replies: 8
    Last Post: 17th January 2009, 08:23 PM
  2. ASP .Net rewritepath
    By apeo in forum Web Development
    Replies: 2
    Last Post: 17th October 2008, 08:35 AM
  3. ASP.NET in event log
    By kerrymoralee9280 in forum Windows
    Replies: 4
    Last Post: 6th May 2008, 12:49 PM
  4. Accessing SIMS.Net database through ASP?
    By gavcradd in forum MIS Systems
    Replies: 9
    Last Post: 12th June 2007, 12:20 AM
  5. PHP or ASP.NET
    By plugged_in in forum Web Development
    Replies: 38
    Last Post: 8th March 2007, 11:08 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •