CODEWITHSUNDEEP
mysqlsqlvb.nettreeview
Riples
Riples

Reputation: 1157

How To Populate A Treeview With All Columns And Values From SQL Database

Not sure if this can be done, but I am trying to generate a MySQL query that gives me each column name within a table and the values associated to those columns whilst at the same time, removing duplicate entries for the values.

In a nutshell, I am trying to populate a treeview control with the column names from a table in my database as the nodes. Under each node, I want to list all of the non duplicated and not null entries within each column as sub nodes. Basically creating a filter like control for my application.

I am trying to simulate something like the filter option in Excel where the user can tick on all of the filter options to include/exclude data.

I have tried building a query, but can only manage the column names at this stage. I have also tried building the treeview control from my dataset but can only populate the nodes, not the sub nodes.

Would appreciate any help as I am still learning SQL within VB.net

Upvotes: 0

Views: 2478

Answers (2)

Riples
Riples

Reputation: 1157

using your advice and help, I have managed to convert it to datatables and have got the desired result. Thanks for putting me on the right track.

Here's my end result. Could probably achieve the same result in a little more tidier code, but this is working for me. Thanks again.

            MySQLConn.Open()

            Dim ColumnQry As New MySqlCommand("SELECT * FROM Stores WHERE 1=2", MySQLConn)
            ColumnQry.CommandType = CommandType.Text
            Dim da1 As New MySqlDataAdapter(ColumnQry)
            dasColumns.Tables.Clear()

            da1.Fill(dasColumns, "Columns")
            Dim dt1 As DataTable = dasColumns.Tables("Columns")

            For Each f As DataColumn In dt1.Columns
                trvFilter.Nodes.Add(f.ColumnName, f.ColumnName)

                Dim ValuesQry As New MySqlCommand("SELECT DISTINCT " & f.ColumnName & " FROM Stores ORDER BY " & f.ColumnName, MySQLConn)
                ValuesQry.CommandType = CommandType.Text
                Dim da2 As New MySqlDataAdapter(ValuesQry)
                dasValues.Tables.Clear()

                da2.Fill(dasValues, "Values")
                Dim dt2 As DataTable = dasValues.Tables("Values")

                For x = 0 To (dasValues.Tables("Values").Rows.Count - 1)
                    trvFilter.Nodes(f.ColumnName).Nodes.Add(dt2.Rows(x)(f.ColumnName).ToString)
                Next
            Next

            MySQLConn.Close()

Upvotes: 0

user2169391
user2169391

Reputation:

I don't think you can do that with one query. Some pseudo code...

'get empty recordset, just to get all the field names
query = "SELECT * FROM mytable WHERE 1=2"
rs = conn.open(query)
'loop over the fields and add them to the treeview while storing a reference
'of the created node in variable n
foreach f in rs.fields
  set n = treeview.addnode(f.name)
  'for the created node get the unique values occuring in that column
  query = "SELECT DISTINCT " & f.name & " FROM mytable ORDER BY " & f.name
  rs2 = conn.open(query)
  'loop over the found values
  while not rs2.EOF
    'add each value to the treeview as a child of node n
    treeview.addnode(rs2.fields(f.name), n)
    rs2.MoveNext
  loop
next f

Upvotes: 0

Related Questions