VBNET2008 Using DataReader to Fill TreeView at Runtime

Lennie · February 9th, 2011, 05:49 AM

Hullo Good Guys,

I am developing Window Application using VBNET2008, TreeView and DataReader.

Please help me. I need your help.

I am trying to use DataReader to retrieve the record to fill the TREEVIEW control at RunTime and it's not working as I have not done it before.

Listed beow are the SQL String and the result of it. And also the graphic drawing of TreeView display

SQL String :
SELECT OrderID, OrderDate ProductID
FROM Invoices WHERE (CustomerID = 'Chops') Order by OrderID, Orderdate

Result of SQL String :
OrderId OrderDate ProdID
10254 11/07/1996 74
10254 11/07/1996 24
10254 11/07/1996 55
10254 13/07/1996 203
10254 13/07/1996 112
10370 03/12/1996 74
10370 03/12/1996 1
10370 03/12/1996 64
10370 03/12/1996 56
10519 28/04/1997 10
10519 28/04/1997 60

TreeView Display Format
Graphic drawing of how it should look like

10254 => Parent
11/07/1996 =>Child
74 =======> GrandChild
24
55
13/07/1996 =>Child
203 ====> GrandChild
112

10370 => Parent
03/12/1996 =>Child
74 ===> GrandChild
1
64
56

Listed below are the overall coding that is not working..ouch...
Private Sub btnDisplayTreeView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayTreeView.Click

' --variable to determine NEW NODE ---
Dim intOrderID As Integer = 0
Dim DteOrderDate As Date = Nothing
Dim intNode As Integer = 0
Dim intSubNode As Integer = 1

Dim bolRoot As Boolean = True

Dim strsql As String = Nothing

strsql &= "Select OrderID, Convert(varchar(10), OrderDate, 103) as [OrderDate], ProductID "
strsql &= " From testInvoices "
strsql &= " Where (CustomerID = N'" + strCustId + "' )"
strsql &= " And ( OrderDate between @sDate and @eDate) "
strsql &= " Order by OrderId, OrderDate "

'define data object
sqlconn = New SqlConnection(connstr)
sqlcmd = New SqlCommand(strsql, sqlconn)
sqlcmd.Connection.Open()

'using sqlcmd parameter
With sqlcmd
.CommandText = strsql
.Parameters.Add("@sDate", SqlDbType.DateTime).Value = strFromDate 'Date.Parse(strFromDate)
.Parameters.Add("@eDate", SqlDbType.DateTime).Value = strToDate 'Date.Parse(strToDate)
End With
DR = sqlcmd.ExecuteReader()

With Me.TreeViewInvoice
.BeginUpdate()

While (DR.Read())

'---- Parent Level ----OrderID
If (DR.Item("OrderID") <> intOrderID) Then
' new parent

intOrderID = DR.Item("OrderID")
bolRoot = True

' -- initial Root Parent = 0
If bolRoot = True Then
intNode += 0
bolRoot = False
Else
'---next parent
intNode += 1

End If

' ---- Parent ---OrderID
.Nodes.Add(DR.Item("OrderID")) ' parent
End If

' ---- Child Level ----Order Date
If (DR.Item("OrderDate").ToString <> DteOrderDate.ToShortDateString) Then
DteOrderDate = (DR.Item("OrderDate").ToString)

'--- Child ---
.Nodes(intNode).Nodes.Add(DR.Item("OrderDate")) ' child
End If

' ---- GrandChild Level ----'ProductId
.Nodes(intNode).Nodes(intSubNode).Nodes.Add(DR.Ite m("ProductID"))

End While
Me.TreeViewInvoice.EndUpdate()
Me.TreeViewInvoice.Visible = True
End With
DR.Close()
sqlconn.Close()
sqlcmd.Dispose()
End Sub

Thank you for helping me.

Cheers,
Lennie

melvik · February 16th, 2011, 09:02 AM

Dear friend

I didnt read ur code but I did it for u with C# I hope its ok & u'll get the point
First I collect the data with LINQ as below (I believe it has much more performance to group before )

Parents
MidChild
child

then I just simple add in 3 parts

Code:

            var Parents = (from p in ds.Tables[0].AsEnumerable()
                           select p["OrderId"]).Distinct();

            var MidChild = (from c in ds.Tables[0].AsEnumerable()
                            select new
                            {
                                OrderId = c["OrderId"],
                                OrderDate = c["OrderDate"]
                            }).Distinct();

            var child = from c in ds.Tables[0].AsEnumerable()
                        select new
                        {
                            OrderDate = c["OrderDate"],
                            ProductID = c["ProductID"]
                        };

            foreach (var p in Parents)
            {
                addTreeNode(null, p, 0);
            }

            foreach (var md in MidChild)
            {
                addTreeNode(md.OrderId, md.OrderDate, 1);
            }

            foreach (var ch in child)
            {
                addTreeNode(ch.OrderDate, ch.ProductID, 2);
            }

            treeView1.ExpandAll();

& I its the function to add nodes

Code:

private void addTreeNode(Object ParentNd, Object ChildNd, int LevelNd)
        {
            switch (LevelNd)
            {
                case 0:
                    treeView1.Nodes.Add(ChildNd.ToString());
                    break;
                case 1:
                    foreach (TreeNode tn in treeView1.Nodes)
                        if (tn.Text == ParentNd.ToString())
                            tn.Nodes.Add(ChildNd.ToString());
                    break;
                case 2:
                    foreach (TreeNode tn in treeView1.Nodes)
                    {
                        foreach (TreeNode tnn in tn.Nodes)
                            if (tnn.Text == ParentNd.ToString())
                                tnn.Nodes.Add(ChildNd.ToString());
                    }
                    break;
            }//switch
        }

Hth