populate columns in MSSQL table into Treeview

[ssfftt]

[solved]populate columns in MSSQL table into Treeview

I have MSSQL tables "category" and "test", on category table, i have column "CID", "CName", on test table, i have column "TID", "TName" and "CID" (fk, refer to CID in category table).

now I want to populate the category name and test names into treeview, where category name is parentnode, and test name is childnode. can anyone help me on coding plz?

 

[ssfftt]

Ok, done, after half day exploring, eventually got it done, thx for ppl who came in and trying to help~ let me know if u have same problem, i will tell u how i did it~

 

[sridhar_l]

Can u tell me how u did

Hi,
Can u help me to regarding this. Iam doing the same, but unable to. Please help me.

 

[Cheetah]

This is exactly what i am after, i didnt see this thread otherwise i wouldnt have created one myself about this.

Could you post your code please.

 

[ssfftt]

hi sridhar and cheetah, its been a year since i solved this problem. i am @ work atm, i will try to dig it up for you soon as i get home tonite
be aware that it was a .net 2003 project

 

[Cheetah]

I think i have figured how to do this, but could you please post your code anyways so i can compare.

Thanks again!

 

[ssfftt]

here u go

Private Sub getEntireList()
Try
ModuleUser.frmTestList1.TVTests.Nodes.Clear()
If ModuleUser.frmTestList1.SqlConnectionTest.State = ConnectionState.Closed Then
ModuleUser.frmTestList1.SqlConnectionTest.Open()
End If
 
[COLOR=darkgreen]'retrieve all categories from table CATEGORY and store into dataset[/COLOR]
ModuleUser.frmTestList1.DSCatList.Clear()
ModuleUser.frmTestList1.SqlDataAdapterCatList.Fill(ModuleUser.frmTestList1.DSCatList)
 
Dim i, j, curCatID As Integer
Dim curCategory, curTest As String
 
For i = 0 To ModuleUser.frmTestList1.DSCatList.Tables("CATEGORY").Rows.Count - 1
 
curCategory = ModuleUser.frmTestList1.DSCatList.Tables("CATEGORY").Rows(i).Item("CNAME").ToString
curCatID = ModuleUser.frmTestList1.DSCatList.Tables("CATEGORY").Rows(i).Item("CID").ToString
[COLOR=darkgreen]'add current category name as a node[/COLOR]
ModuleUser.frmTestList1.TVTests.Nodes.Add(curCategory)
 
[COLOR=darkgreen]'loop through test, add test under current category where CATEGORY.CID = TEST.CID[/COLOR]
ModuleUser.frmTestList1.SqlSelectCommand2.Parameters("@CID").Value = curCatID
ModuleUser.frmTestList1.DsTestList.Clear()
ModuleUser.frmTestList1.SqlDataAdapterTestList.Fill(ModuleUser.frmTestList1.DsTestList)
 
For j = 0 To ModuleUser.frmTestList1.DsTestList.Tables("TEST").Rows.Count - 1
 
[COLOR=darkgreen]'once we have the records we fill the respective child node in the control[/COLOR]
curTest = ModuleUser.frmTestList1.DsTestList.Tables("TEST").Rows(j).Item("TNAME").ToString
Dim TestNode As TreeNode
TestNode = ModuleUser.frmTestList1.TVTests.Nodes(i)
 
[COLOR=darkgreen]'add current test name as a node[/COLOR]
TestNode.Nodes.Add(curTest)
 
Next
 
Next
 
ModuleUser.frmTestList1.SqlConnectionTest.Close()
 
Catch Exp As SqlClient.SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
 
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
 
End Try
 
End Sub

query for SqlSelectCommand2 is
"SELECT TID, TNAME, SHORTDESC, FULLMARKS, UID, CID FROM TEST WHERE (TNAME = @TNAME)"
query for SqlDataAdapterTestList.Fill is
"SELECT TID, TNAME, CID FROM TEST WHERE (CID = @CID)"