John John - 5 months ago 23
SQL Question

How to load data from database to TreeView when parent and child in the same table

I have LearningObject table in Database which contains (ID, level, subject, type).
The Level field contains (One, Two, Three). For each level there are many subjects.
I need to load data from this database to TreeView: each level represents as a parent, and each subject represents as a child under the suitable level.
The following code bellow did not work with me to implement my idea.
I have this error
( An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code
Additional information: Unknown column 'one' in 'where clause')

protected void Page_Load(object sender, EventArgs e)
{
fill_Tree2();
}

public void fill_Tree2()
{
DataSet PrSet = PDataset("Select DISTINCT level from learningobject");
TreeView1.Nodes.Clear();
foreach (DataRow dr in PrSet.Tables[0].Rows)
{
TreeNode tnParent = new TreeNode();
tnParent.Text = dr["level"].ToString();
tnParent.Value = dr["level"].ToString();
tnParent.PopulateOnDemand = true;
tnParent.ToolTip = "Click to get Child";
tnParent.SelectAction = TreeNodeSelectAction.SelectExpand;
tnParent.Expand();
tnParent.Selected = true;
TreeView1.Nodes.Add(tnParent);
FillChild(tnParent, tnParent.Value);
}
}

public void FillChild(TreeNode parent, string ParentID)
{
DataSet ds = PDataset("Select subject,level from learningobject where level =" + ParentID.ToString());
parent.ChildNodes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
TreeNode child = new TreeNode();
child.Text = dr["subject"].ToString().Trim();
child.Value = dr["level"].ToString().Trim();
if (child.ChildNodes.Count == 0)
{
child.PopulateOnDemand = true;
}
child.ToolTip = "Click to get Child";
child.SelectAction = TreeNodeSelectAction.SelectExpand;
child.CollapseAll();
parent.ChildNodes.Add(child);
}
}

protected DataSet PDataset(string Select_Statement)
{
string connStr = @"Data Source=localhost;Database=ahsschema;User Id=webuser;Password=";
using (MySqlConnection SqlCon = new MySqlConnection(connStr))
{
MySqlDataAdapter ad = new MySqlDataAdapter(Select_Statement, SqlCon);
DataSet ds = new DataSet();
ad.Fill(ds);
return ds;

}
}

Answer

Try this statement

 DataSet ds = PDataset("Select subject, level FROM learningobject WHERE level ='"+ ParentID.ToString())+"';

Can't comment so had to put it here sorry if it did not help

Comments