strSql = "SELECT * FROM COUNTRIES GROUP BY COUNTRY " --------------error cannot group selected with "*"
' or change it as select COUNTRY , MAX(CITY) AS CITY FROM COUNTRIES GROUP BY COUNTRY "
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open strCon
Set oCmd = New ADODB.Command
Set oCmd.ActiveConnection = cn
oCmd.CommandType = adCmdText
Set rs = New ADODB.Recordset
rs.Open strSql, cn, adOpenDynamic, adLockOptimistic
' --------------------------------------and problem with loop results , what should i change ?
Europe (root)
I
ı_____ Italy
I_____ Rome
ı_____ France
I_____ Paris
Last edited by coincollector; May 4th, 2021 at 11:19 AM.
First off... please use when posting code use [code][/code] tags around it... it preserves the formatting and makes it nicer to look at. You can also jsut paste the code in, select it, then click the # button in the toolbar above and it will put the tags around it for you.
Secondly, correct, you cannot use a GROUP BY with * and you cannot use it without an aggregate function (min, max, avg, etc) (we you can, but it's not recommended, and if you aren't using an aggregate funciton, then you have to ask why are you using group by).
Last, the reason everyhting ends up under hte root node is because that's where you add it.
Code:
Treeview.Nodes.Add
By default has no context, so it adds it as a root node.
What you should be doing is creating individual TreeViewNodes ... and adding them to the parent TreeViewNodes as appropriate... then adding that to your root node, then add that to your treeview....
Something like this (untested):
Code:
Dim rootNode as TreeViewNode
Dim countryNode as TreeViewNode
Dim cityNode as TreeViewNode
set rootNode = new TreeViewNode
rootNode.key = "RootDb"
rootNode.Text = "Europe"
set countryNode = new TreeViewNode
countryNode.key = "Italy"
countryNode.Text = "Italy"
set cityNode = new TreeViewNode
cityNode.key = "Rome"
cityNode.Text = "Rome"
countryNode.Nodes.Add cityNode
set cityNode = new TreeViewNode
cityNode.key = "Pisa"
cityNode.Text = "Pisa"
countryNode.Nodes.Add cityNode
rootNode.Nodes.Add countryNode
set countryNode = new TreeViewNode
countryNode.key = "France"
countryNode.Text = "France"
set cityNode = new TreeViewNode
cityNode.key = "Paris"
cityNode.Text = "Paris"
countryNode.Nodes.Add cityNode
set cityNode = new TreeViewNode
cityNode.key = "Nice"
cityNode.Text = "Nice"
countryNode.Nodes.Add cityNode
rootNode.Nodes.Add countryNode
TreeView1.Nodes.Add rootNode
Can't guarantee it'll work as is, but it should get you going in the right direction.
Thank you techgnome , yes you are right , i must use code tags .
Code:
Dim rootNode as TreeViewNode
user-defined type not defined error. I use "Dim rootNode as Node"
I need help with ado recordset code.
I hope you understood what i want to tell. I have a table and 2 fields . There are more records in this table and i want to group country names and cities as child nodes. . I save new records to recordset and I must write the code ADO connection
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim MyDataFile As String
Dim oCmd As ADODB.Command
Dim strCon As String
Dim strSql As String
Dim dbNode As Node
MyDataFile = App.Path & "\DataFile\" & "coins.mdb"
strCon = "Provider = Microsoft.Jet.OLEDB.4.0;data source=" & MyDataFile & ""
strSql = "SELECT * FROM TABLE "
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open strCon
Set rst = New ADODB.Recordset
rst.Open strSql, cn, adOpenDynamic, adLockOptimistic
TreeView1.Nodes.Clear
TreeView1.LineStyle = tvwRootLines
TreeView1.ImageList = ImageList1
Set dbNode = TreeView1.Nodes.Add(, , "RootDB", "COUNTRIES", 1)
' set root node of TreeView.
Dim ParentName As String
Dim FieldIndex As Integer
Dim NodeName As String
Dim NewNode As Node
With rst
Do Until .EOF
ParentName = vbNullString
For FieldIndex = 0 To .Fields.Count - 1
NodeName = ParentName & "|" & .Fields(FieldIndex).Value
On Error Resume Next
Set NewNode = TreeView1.Nodes.Item(NodeName)
If Err Then
On Error GoTo 0
If Len(ParentName) = 0 Then
Set NewNode = TreeView1.Nodes.Add(, _
tvwNext, _
NodeName, _
.Fields(FieldIndex).Value, 2)
Else
Set NewNode = TreeView1.Nodes.Add(ParentName, _
tvwChild, _
NodeName, _
.Fields(FieldIndex).Value, 3)
End If
NewNode.Expanded = True
NewNode.Sorted = True
Else
On Error GoTo 0
End If
ParentName = NodeName
Next
.MoveNext
Loop
.Close
End With
You can also use ADO's Data Shaping Service with a "grouping" SHAPE command in order to create hierarchical Recordsets for this sort of thing.
I'm not sure many people are even aware it exists but it has been covered in the documentation that comes with VB6 for quite a long time. It is certainly covered in the October 2001 MSDN Library CHM Help, the last edition supporting VB6.
Code:
Option Explicit
Private Sub Form_Load()
Dim RSEurope As ADODB.Recordset
Dim RSChapters As ADODB.Recordset
Dim FldCountry As ADODB.Field
Dim FldCity As ADODB.Field
Dim Europe As ComctlLib.Node
Dim Country As ComctlLib.Node
Set RSEurope = New ADODB.Recordset
With RSEurope
.CursorLocation = adUseClient
.Open "SHAPE{SELECT [Country], [City] FROM [Europe]} AS [Rows] " _
& "COMPUTE [Rows] AS [Chapters] BY [Country]", _
Module1.Connection, _
adOpenStatic, _
adLockReadOnly, _
adCmdText
With MSHFlexGrid1
Set .DataSource = RSEurope
.ColWidth(0, 0) = 240
.ColWidth(0, 1) = 0
End With
Set FldCountry = .Fields("Country")
Set RSChapters = .Fields("Chapters").Value
Set FldCity = RSChapters.Fields("City")
Set Europe = TreeView1.Nodes.Add(, , , "Europe", "Europe")
Europe.Expanded = True
Do Until .EOF
Set Country = TreeView1.Nodes.Add(Europe, tvwChild, , FldCountry.Value, "Country")
Country.Expanded = True
With RSChapters
Do Until .EOF
TreeView1.Nodes.Add Country, tvwChild, , FldCity.Value, "City"
.MoveNext
Loop
End With
.MoveNext
Loop
.Close
End With
End Sub
Private Sub Form_Resize()
If WindowState <> vbMinimized Then
With MSHFlexGrid1
.Move 0, 0, ScaleWidth, 2100
TreeView1.Move 0, .Height, ScaleWidth, ScaleHeight - .Height
End With
End If
End Sub
Displayed automatically within an MSHFlexGrid
and manually within a TreeView control