John John - 1 year ago 64
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)

public void fill_Tree2()
DataSet PrSet = PDataset("Select DISTINCT level from learningobject");
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.Selected = true;
FillChild(tnParent, tnParent.Value);

public void FillChild(TreeNode parent, string ParentID)
DataSet ds = PDataset("Select subject,level from learningobject where level =" + ParentID.ToString());
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;

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();
return ds;


Answer Source

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