Solved

Populate a ListView Control using ADO?

Posted on 2000-04-25
11
430 Views
Last Modified: 2010-05-02
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

0
Comment
Question by:ADawn
  • 4
  • 4
  • 3
11 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2748098
0
 
LVL 6

Expert Comment

by:Marine
ID: 2748208
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
 

Author Comment

by:ADawn
ID: 2750929
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2750987
cnAcct is your connection object. You said that you are using ADO, you surely open a connection!
0
 
LVL 6

Expert Comment

by:Marine
ID: 2751411
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:ADawn
ID: 2752921
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2753607
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
 
LVL 6

Expert Comment

by:Marine
ID: 2753632
emoreau it does same thing as mine.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2753683
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
 

Author Comment

by:ADawn
ID: 2755042
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
 
LVL 6

Accepted Solution

by:
Marine earned 20 total points
ID: 2755159
No one stupid here :). I am no Pro and ask more questions then i ask :) still learning. With programming learning is non stop :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
change vba from autofit to 13.5 width? 4 28
How to compare ms sql hashbytes results within vb6 5 55
Hide vba in gp 7 83
Excel Automation VBA 19 37
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now