ASP.net c# treeview from SQL

**damienharrison** · 27th January 2009, 03:27 PM

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>

**SYNACK** · 27th January 2009, 03:38 PM

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);

**PeterH** · 27th January 2009, 03:50 PM

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

**SYNACK** · 27th January 2009, 03:54 PM

Originally Posted by PeterH

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

**damienharrison** · 27th January 2009, 03:59 PM

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

**dellmerca** · 20th February 2012, 05:28 PM

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

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

delm

LinkBack

Thread Tools

Search Thread

ASP.net c# treeview from SQL

Thanks to SYNACK from:

Thanks to PeterH from:

Thanks to SYNACK from:

asp.net treeview

Similar Threads

Allow ASP .Net 2 in IIS Server 2003 R2

ASP .Net rewritepath

ASP.NET in event log

Accessing SIMS.Net database through ASP?

PHP or ASP.NET

Thread Information

Users Browsing this Thread

Posting Permissions