Populate a ListView Control using ADO?

I am using an Access 97 database and would like to populate a Listview control (not List Box)in VB6 using ADO.

Database Name = iBase_data.mdb

Table Name = tblDiscp

Field1 = FName
Field2 = LName
Field3 = Street
Field4 = City

Also, I would like to sort each column by clicking the column header.

Can anyone help me with this? Thanks

ADawnAsked:
Who is Participating?
 
MarineConnect With a Mentor Commented:
No one stupid here :). I am no Pro and ask more questions then i ask :) still learning. With programming learning is non stop :)
0
 
MarineCommented:
Here is my example of this.
This populates the Listview from Recordset.


Dim rs As New ADODB.Recordset, lstItem As ListItem, I As Integer
On Error GoTo err_Rec
sSql = "SELECT tblBooks.ISBN, tblBooks.Title, tblAuthors.AuthorName, tblPublishers.Publisher, tblBooks.Year, tblBooks.Quantity FROM tblPublishers INNER JOIN (tblCategories INNER JOIN (tblAuthors INNER JOIN tblBooks ON tblAuthors.AuthorID = tblBooks.Author) ON tblCategories.CategoryID = tblBooks.Category) ON tblPublishers.PublisherID = tblBooks.Publisher WHERE CategoryDesc='Horror';"
MsgBox sSql

rs.Open sSql, cnAcct, adOpenStatic, adLockPessimistic
If rs.RecordCount = 0 Then
    MsgBox "NO RECORDS HAVE BEEN FOUND ON THIS TOPIC" & Chr(13) & Chr(10) & _
           "            PLEASE SELECT ANOTHER TOPIC", vbInformation, "Search Found 0 Records"
    Exit Sub
End If
rs.MoveFirst
Dim J As Integer
Do While Not rs.EOF
        Set lstItem = lst.ListItems. _
        Add(, , CStr(rs!ISBN))
        For I = 1 To lst.ColumnHeaders.Count - 1
            lstItem.SubItems(I) = IIf(Not IsNull(rs(I)), rs(I), "")
        Next
        rs.MoveNext
Loop
rs.Close
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ADawnAuthor Commented:
marine - what is cnAcct in your caode. I can't seem to get this code to work. I'm new at this codede stuff. Anything I need to know? Thanks
0
 
Éric MoreauSenior .Net ConsultantCommented:
cnAcct is your connection object. You said that you are using ADO, you surely open a connection!
0
 
MarineCommented:
Yes cnAcct is a connection i assume that you already have it opend. If y ou want i post all teh code with connection. Although this should work if you have it already opened. Substitude your own SQL string.
0
 
ADawnAuthor Commented:
Hello,

shoot me - this is greek to me. I have a form with a listview control. When I open the form I want the listview control to display all the records from the fields in my original question.

HELP!!!!
0
 
Éric MoreauSenior .Net ConsultantCommented:
This is the code taken from one of the link I already gave to you that I did adapted a bit for you particular case.

Private Sub Command1_Click()
   Open_RS
End Sub

Private Sub Open_RS()
Dim rs As ADODB.Recordset
    Set rs = new ADODB.Recordset
    rs.Open "tblDiscp", "c:\path\iBase_data.mdb", _
        adOpenKeyset, adLockOptimistic, adCmdTable

    'fill listview
    Dim clmX As ColumnHeader
    Dim itmX As ListItem

ListViewCtrl1.View = lvwReport 'use reportview no icons
    Set clmX = ListViewCtrl1.ColumnHeaders.Add(, , "First name", _
               ListViewCtrl1.Width / 4)
    Set clmX = ListViewCtrl1.ColumnHeaders.Add(, , "Last Name", _
               ListViewCtrl1.Width / 4)
    Set clmX = ListViewCtrl1.ColumnHeaders.Add(, , "Street", _
               ListViewCtrl1.Width / 4)
    Set clmX = ListViewCtrl1.ColumnHeaders.Add(, , "City", _
               ListViewCtrl1.Width / 4)


    While Not rs.EOF
        Set itmX = ListViewCtrl1.ListItems.Add(, , rs.Fields("FName"))
        itmX.SubItems(1) = rs.Fields("LName")
        itmX.SubItems(2) = rs.Fields("Street")
        itmX.SubItems(3) = rs.Fields("City")
        rs.MoveNext
    Wend
End Sub
0
 
MarineCommented:
emoreau it does same thing as mine.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Sure. I just use its table name and columns name since ADawn seems to want a "customized" solution! Except that instead of stealing your code, I used the one provided in the first link I already gave.
0
 
ADawnAuthor Commented:
Thanks guys - I will check this code tonight. Sorry - I'm trying to learn my way around Visual Basic. I don't mean to be 'Stupid', but please remember your first days at this stuff. I hope to give help to others some time down the road - God help us all. Thanks for the tips - your the pros.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.