Tree structure on a datagrid

    I am doing a windows application using . I am using oracle database, but in order to explain I am using the "Pubs" database.
    When the form is loaded , the  datagrid is filled with the info about the authors . When selecting  a row, the info about the books written by that author should be displayed like branches just under that author . when selecting another row, the already eapanded row must collapse and the branches should appear under the now selected author.
    The rows of books (branches)  should be either displaced a little to the right compared to the  rows of the authors,   or atleast the rows of books and authors should be of different color.

    Can you help me??
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
I don't believe that this would be a simple solution with the DataGrid.  Is there a reason for not using a TreeView?

sgs1970Author Commented:
Hi Bob and everyone,
  Infact, I have never used treeview.  If I can use treeview with same way I can have multiple coulumns in a datagrid, probably that is good enough. But very essentialy, I need to have hidden columns- that is the columns that are not shown on the grid , but whose values are used for processing, when a user click on a row.
   Thanking you,
Bob LearnedCommented:
What version of .NET do you have?

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

sgs1970Author Commented:
I am using .Net 2003 . But if it is possible in VS 2005 , please let me know. I will start using that version anyway
Bob LearnedCommented:
The ComponentOne VsFlexGrid supports having a tree structure in a grid:

sgs1970Author Commented:

 Hi Bob and everyone,
   Do we get something for free ? Treeview is ok.  But very essentialy, I need to have hidden columns- that is the columns that are not shown on the grid , but whose values are used for processing, when a user click on a row.
Bob LearnedCommented:
In 2003, you apply table styles to the DataGrid with column width set to 0.  For 2005, you set the column visible property a little differently.

sgs1970Author Commented:

 Hi Bob,
   The real issue is ,Clicking on an author, the info about the book from the author shouls be displayed  .  But very essentialy, I need to have hidden columns- that is the columns that are not shown , but whose values are used for processing, when a user click on a row.  It can be treeview, datagrid or any other control in any version of .net  or out of the box but with no additional cost.

Bob LearnedCommented:
With the 2003 DataGrid, this is the best that you can do:

Imports System.Data.SqlClient

Public Class frmPubs
  Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

  Public Sub New()

    'This call is required by the Windows Form Designer.

    'Add any initialization after the InitializeComponent() call

  End Sub

  'Form overrides dispose to clean up the component list.
  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    If disposing Then
      If Not (components Is Nothing) Then
      End If
    End If
  End Sub

  'Required by the Windows Form Designer
  Private components As System.ComponentModel.IContainer

  'NOTE: The following procedure is required by the Windows Form Designer
  'It can be modified using the Windows Form Designer.  
  'Do not modify it using the code editor.
  Friend WithEvents gridPubs As System.Windows.Forms.DataGrid
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    Me.gridPubs = New System.Windows.Forms.DataGrid
    CType(Me.gridPubs, System.ComponentModel.ISupportInitialize).BeginInit()
    Me.gridPubs.DataMember = ""
    Me.gridPubs.HeaderForeColor = System.Drawing.SystemColors.ControlText
    Me.gridPubs.Location = New System.Drawing.Point(12, 20)
    Me.gridPubs.Name = "gridPubs"
    Me.gridPubs.Size = New System.Drawing.Size(468, 296)
    Me.gridPubs.TabIndex = 0
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.ClientSize = New System.Drawing.Size(492, 326)
    Me.Name = "frmPubs"
    Me.Text = "Pubs Example"
    CType(Me.gridPubs, System.ComponentModel.ISupportInitialize).EndInit()

  End Sub

#End Region

  Private m_adapterAuthors As SqlDataAdapter
  Private m_adapterTitles As SqlDataAdapter
  Private m_dataAuthors As New DataSet
  Private m_tableAuthors As DataTable
  Private m_tableTitles As DataTable

  Private Sub frmPubs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    Me.gridPubs.DataSource = m_dataAuthors
    Me.gridPubs.DataMember = "authors"
    Me.gridPubs.AlternatingBackColor = Color.AliceBlue

  End Sub

  Private Sub LoadAuthorTitles()

    Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;"

    Dim query As String = _
      "SELECT dbo.authors.au_id, dbo.titleauthor.title_id, dbo.authors.au_lname, dbo.authors.au_fname" & _
      "  FROM dbo.authors INNER JOIN" & _
      "       dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id"

    m_adapterAuthors = New SqlDataAdapter(query, connectionString)

    query = _
      "SELECT dbo.titleauthor.au_id, dbo.titleauthor.title_id, dbo.titles.title" & _
      "  FROM dbo.titleauthor INNER JOIN" & _
      "       dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id"

    m_adapterTitles = New SqlDataAdapter(query, connectionString)

    m_adapterAuthors.Fill(m_dataAuthors, "authors")
    m_adapterTitles.Fill(m_dataAuthors, "titles")

    m_tableAuthors = m_dataAuthors.Tables("authors")
    m_tableTitles = m_dataAuthors.Tables("titles")

    Dim authorID As DataColumn = m_tableAuthors.Columns("au_id")
    Dim authorTitle As DataColumn = m_tableAuthors.Columns("title_id")
    Dim titleAuthor As DataColumn = m_tableTitles.Columns("au_id")
    Dim titleID As DataColumn = m_tableTitles.Columns("title_id")

    m_dataAuthors.Relations.Add("authors_titles", New DataColumn() {authorID, authorTitle}, _
      New DataColumn() {titleAuthor, titleID})

  End Sub

End Class

This is a sample form that highlights showing parent/child relationships hierarchical.  According to this article, the DataGrid doesn't support showing parent and child rows on the same form:

Using the Windows Forms DataGrid in Visual Basic .NET

Host: Scott (Microsoft)
Q: How can I show hierarchical data in the grid using a relation without requiring my users to click on the relation hyperlink?

A: Bind directly to the data you want to show. Set DataSource = the DataSet, DataMember = "ParentTableName.RelationName"

Host: Scott (Microsoft)
Q: I just tried that and it only shows the child data.

A: Sorry -- I misunderstood the question. It sounds like you want to show the parent row together with their children: the DataGrid doesn't support this, I'm afraid.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.