ASP.net c# treeview from SQL

[damienharrison]

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.

 

 

 

<%@ Page Language="C#" Debug="true" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="ICCMeSD_KnowledgeCP_Default" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>


<br />
<br />
  void PopulateNode(Object sender, TreeNodeEventArgs e)<br />
  {<br />
  <br />
 <br />
<br />
    // Call the appropriate method to populate a node at a particular level.<br />
    switch(e.Node.Depth)<br />
    {<br />
    <br />
      case 0:<br />
        // Populate the first-level nodes.<br />
        PopulateCategories(e.Node);<br />
        <br />
        break;<br />
      case 1:<br />
        // Populate the second-level nodes.<br />
        PopulateProducts(e.Node);<br />
        break;<br />
       case 2:<br />
       PopulateDetails (e.Node);<br />
       break;<br />
      default:<br />
        // Do nothing.<br />
        break;<br />
    }<br />
<br />
  }<br />
<br />
  void PopulateCategories(TreeNode node)<br />
  {<br />
  <br />
<br />
    // Query for the product categories. These are the values<br />
    // for the second-level nodes.<br />
    DataSet ResultSet = RunQuery("SELECT DISTINCT Category FROM Knowledge_Request WHERE Category !='' ");<br />
    <br />
    // Create the second-level nodes.<br />
    if(ResultSet.Tables.Count > 0)<br />
    {<br />
<br />
      // Iterate through and create a new node for each row in the query results.<br />
      // Notice that the query results are stored in the table of the DataSet.<br />
      foreach (DataRow row in ResultSet.Tables[0].Rows)<br />
      {<br />
<br />
        // Create the new node. Notice that the CategoryId is stored in the Value property <br />
        // of the node. This will make querying for items in a specific category easier when<br />
        // the third-level nodes are created. <br />
        <br />
        TreeNode newNode = new TreeNode();<br />
        newNode.Text = row["Category"].ToString();<br />
        newNode.Value = row["Category"].ToString();<br />
        // Set the PopulateOnDemand property to true so that the child nodes can be <br />
        // dynamically populated.<br />
        newNode.PopulateOnDemand = true;<br />
<br />
        // Set additional properties for the node.<br />
        newNode.SelectAction = TreeNodeSelectAction.Expand;<br />
<br />
        // Add the new node to the ChildNodes collection of the parent node.<br />
        node.ChildNodes.Add(newNode);<br />
<br />
      }<br />
<br />
    }<br />
<br />
  }<br />
<br />
  void PopulateProducts(TreeNode node)<br />
  {<br />
  if(!IsPostBack){   LinksTreeView.Attributes.Add("onclick", "return OnTreeClick(event)");}<br />
    // Query for the products of the current category. These are the values<br />
    // for the third-level nodes.<br />
 <br />
[color="Red"]*1[/color]<br />
   DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category=" + node.Text);<br />
<br />
   <br />
<br />
      // Iterate through and create a new node for each row in the query results.<br />
      // Notice that the query results are stored in the table of the DataSet.<br />
      foreach (DataRow row in ResultSet.Tables[0].Rows)<br />
      {<br />
<br />
        // Create the new node.<br />
        TreeNode NewNode = new TreeNode(row["PRJNAME"].ToString());<br />
<br />
        // Set the PopulateOnDemand property to false, because these are leaf nodes and<br />
        // do not need to be populated.<br />
        NewNode.PopulateOnDemand = true;<br />
<br />
        // Set additional properties for the node.<br />
        NewNode.SelectAction = TreeNodeSelectAction.Expand;<br />
<br />
        // Add the new node to the ChildNodes collection of the parent node.<br />
        node.ChildNodes.Add(NewNode);<br />
<br />
      }<br />
<br />
    <br />
<br />
  }<br />
  void PopulateDetails(TreeNode node)<br />
  {<br />
  <br />
<br />
    // Query for the products of the current category. These are the values<br />
    // for the forth-level nodes.<br />
    DataSet ResultSet = RunQuery("Select * From Knowledge_Request Where Category='Paris'");<br />
<br />
   <br />
<br />
      // Iterate through and create a new node for each row in the query results.<br />
      // Notice that the query results are stored in the table of the DataSet.<br />
      foreach (DataRow row in ResultSet.Tables[0].Rows)<br />
      {<br />
<br />
        // Create the new node.<br />
        TreeNode NewNode = new TreeNode(row["PRJOBJECTIVES"].ToString());<br />
<br />
        // Set the PopulateOnDemand property to false, because these are leaf nodes and<br />
        // do not need to be populated.<br />
        NewNode.PopulateOnDemand = true;<br />
<br />
        // Set additional properties for the node.<br />
        NewNode.SelectAction = TreeNodeSelectAction.Expand;<br />
<br />
        // Add the new node to the ChildNodes collection of the parent node.<br />
        node.ChildNodes.Add(NewNode);<br />
<br />
      }<br />
<br />
    <br />
<br />
  }<br />
<br />
  DataSet RunQuery(String QueryString)<br />
  {<br />
<br />
    // Declare the connection string. This example uses Microsoft SQL Server <br />
    // and connects to the EWORK.<br />
    String ConnectionString = "*******"; <br />
<br />
    SqlConnection DBConnection = new SqlConnection(ConnectionString);<br />
    SqlDataAdapter DBAdapter;<br />
    DataSet ResultsDataSet = new DataSet();<br />
<br />
    try<br />
    {<br />
<br />
      // Run the query and create a DataSet.<br />
      DBAdapter = new SqlDataAdapter(QueryString, DBConnection);<br />
      DBAdapter.Fill(ResultsDataSet);<br />
<br />
      // Close the database connection.<br />
      DBConnection.Close();<br />
<br />
    }<br />
    catch(Exception ex)<br />
    {<br />
<br />
      // Close the database connection if it is still open.<br />
      if(DBConnection.State == ConnectionState.Open)<br />
      {<br />
        DBConnection.Close();<br />
      }<br />
<br />
      Message.Text = "Unable to connect to the database.";<br />
<br />
    }<br />
<br />
    return ResultsDataSet;<br />
<br />
  }<br />
<br />





   
   



   </pre><form id="form1" runat="server">
       
           Font-Names= "Arial"
       EnableClientScript="true"
       PopulateNodesFromClient="true"  
       OnTreeNodePopulate="PopulateNode"
       ImageSet="XPFileExplorer"
       runat="server">

       

         

       

     

     



     

   </form><br><b

Edited January 27, 2009 by damienharrison
Addition

[SYNACK]

Just a quick thought but don't you need to terminate that query string before concatinating the node name?

 

try changing

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

 

to

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

[PeterH]

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:

 

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

 

Actually be:

 

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]

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]

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]

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

 

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

 

delm