Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / Visual Basic

Display Database File using TreeView, ListView and ADO

Rate me:
Please Sign up or sign in to vote.
4.33/5 (7 votes)
18 Oct 2009CPOL1 min read 65.5K   3.2K   30   9
How to use ADO to display Tables, Fields and Records in TreeView and ListView
Image 1

Introduction

I have a question in my email, "How to use ADO to populate TreeView and ListView with data from *.mdb file?"
I try to present the answer in the following lines, I hope I succeed.
This article show how to:

  • Connect with database
  • Populate TreeView with Tables name and Fields name
  • Populate ListView with Records of selected Table

Background

The demonstration Project has one Form. I add the following controls to my Form (frmDataView):

  • Two Labels (lblDatabase) to display the file name and (lblTableName) to display the table name
  • Two Buttons, one (cmdLoadData) to connect with database file, other (cmdClose) to end show
  • ImageList (ImageList1) to load some icons
  • TreeView (tvData) and ListView (lvData).

Using the Code 

VB.NET
' Here is the code to get Tables name from the database file:
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim MyDataFile As String
Dim strCon As String
Dim tabName As String
Dim dbNode As Node
Dim tabNode As Node

   ' Don't use: On Error goto label, but use following:
   On Error Resume Next
   ' set root node of TreeView.
   Set dbNode = tvData.Nodes.Add(, , "RootDB", "Database", "DatabaseClose")
   dbNode.Tag = "RootDB"
   MyDataFile = App.Path & "\DataFile\" & "test.mdb"
   strCon = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDataFile & ";"
   Set cn = New ADODB.Connection
   cn.CursorLocation = adUseClient
   cn.Open strCon
   Set rst = cn.OpenSchema(adSchemaTables)
   rst.MoveFirst
   Do Until rst.EOF
      If rst("TABLE_TYPE").Value = "VIEW" Or rst("TABLE_TYPE").Value = "TABLE" Then
         tabName = rst("TABLE_NAME").Value
         Set tabNode = tvData.Nodes.Add("RootDB", tvwChild, "X" & tabName, _
		tabName, "TableClose")
         tabNode.Tag = "Tables"
      End If
      rst.MoveNext
   Loop
   rst.Close

' Here is the code to get Fields name from all Tables:
Dim rs As ADODB.Recordset
Dim fldName As String
Dim n As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer
   Set rs = New ADODB.Recordset
   c = tvData.Nodes("RootDB").Children
   ' first table
   n = tvData.Nodes("RootDB").Child.FirstSibling.Index
   For i = n To c + n
      tabName = tvData.Nodes(i).Text
      ' get field name
      Set rs = cn.Execute("SELECT * FROM [" & tabName & "]", 1, 1)
      For j = 0 To rs.Fields.Count - 1
         fldName = rs.Fields(j).Name
         Set fldNameNode = tvData.Nodes.Add("X" & tabName, tvwChild, _
		"X" & fldName, fldName, "imgField")
         fldNameNode.Tag = "Fields"
      Next j
      rs.Close
   Next i  

You can read the full code in the Form (frmDataView):

  • InitListView Sub: To initialize ListView
  • LoadListView Sub: To load ListView with data
  • cmdLoadData_Click Sub: To connect with database file
  • tvData_Collapse Sub: To change icon
  • tvData_Expand Sub: To change icon
  • tvData_NodeClick Sub: To see how to populate ListView with Records when clicking Table name in TreeView, you can also see how we change icon in ColumnHeader of ListView when clicking Field name in TreeView

Remarks

When you create a new project, you must add ListView, TreeView and ImageL<code>ist controls to ToolBox from Components dialog:
Microsoft Windows Common Controls 6.0
You must add the reference: "Microsoft ActiveX Data Objects 2.x".

Last Words

I hope this article is useful and helps you to display Tables, Fields and Records from a database file to TreeView and ListView. Please tell me if you have any ideas or if you find any problems. Thanks to CodeProject and thanks to all.

Mostafa Kaisoun
M_Kaisoun@hotmail.com

History

  • 18th October, 2009: Initial post 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Egypt Egypt
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionJust what I wad looking for..how about vb.net version? Pin
Winky558530-May-10 20:44
Winky558530-May-10 20:44 
AnswerRe: Just what I wad looking for..how about vb.net version? Pin
Mostafa Kaisoun1-Jun-10 1:34
Mostafa Kaisoun1-Jun-10 1:34 
GeneralRe: Just what I wad looking for..how about vb.net version? Pin
Winky55851-Jun-10 14:24
Winky55851-Jun-10 14:24 
GeneralRe: Just what I wad looking for..how about vb.net version? Pin
Winky558510-Jun-10 8:50
Winky558510-Jun-10 8:50 
GeneralRe: Just what I wad looking for..how about vb.net version? Pin
Mostafa Kaisoun11-Jun-10 17:29
Mostafa Kaisoun11-Jun-10 17:29 
GeneralDisplay Database File using TreeView, ListView and ADO Pin
elilancry16-Feb-10 23:02
elilancry16-Feb-10 23:02 
GeneralRe: Display Database File using TreeView, ListView and ADO Pin
Mostafa Kaisoun18-Feb-10 5:57
Mostafa Kaisoun18-Feb-10 5:57 
GeneralThank you Pin
lhartono2-Dec-09 20:58
lhartono2-Dec-09 20:58 
GeneralRe: Thank you Pin
Mostafa Kaisoun3-Dec-09 3:19
Mostafa Kaisoun3-Dec-09 3:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.