Fill a ListView control with items from a database

[Dr_Michael]

I have connected to my database and I want to fill a listview control on the form with only three fields from the Database: 1)ID, 2)CompanyName, 3)BusinessName
How can I achieve that? What is the appropriate code for that? Keep in mind that my listview control is in report mode.
The code must be something like this:

Do Until rsAllCustomers.EOF
set mItem = lsvCustomers.ListItems.Add()
'the rating is here!!! :-)
'....
Loop



Thanx and Merry Christmas!

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

I had this block of code. Edit it for 3 columns and so on

private adoPrimaryRS as Recordset
private Sub Command1_Click()
Dim db as Connection
Dim lngRow as Long
set db = new Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb;"

set adoPrimaryRS = new Recordset
adoPrimaryRS.Open "select PubID,[Company Name] from Publishers", db, adOpenStatic, adLockOptimistic
lngRow = adoPrimaryRS.AbsolutePosition
Do While Not adoPrimaryRS.EOF
ListView1.ListItems.Add lngRow, , adoPrimaryRS!PubID & " " & adoPrimaryRS![Company Name]
adoPrimaryRS.MoveNext
lngRow = adoPrimaryRS.AbsolutePosition
Loop
End Sub



Merry Cristmas
Vlad

[Dr_Michael]

Merry Xmas too!
I want to have one item (ID) and two subitems (CompanyName and BusinessName)
How can I achieve it?
Thanx Vlad...

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Crazy D @ Work]

Use something like this:

Do While Not rs.EOF
With lv.AddItem(,,, rs!ID)
.SubItems(1) = Iif(IsNull(rs!CompanyName), "", rs!CompanyName)
.SubItems(2) = Iif(IsNull(rs!BusinessName), "", rs!BusinessName)
End With
Loop



(ok there might be a syntax error but you get the idea... :-)

Crazy D @ Work :-)

[Dr_Michael]

And the right code is:

set rsAllCustomers = new ADODB.Recordset
rsAllCustomers.Open "SELECT * FROM SGD_Customer ORDER BY CompanyName", DBConn, adOpenForwardOnly, adLockBatchOptimistic, adCmdText
rsAllCustomers.MoveFirst
temp = 1
Do Until rsAllCustomers.EOF
With lsvCustomers.ListItems.Add(temp, , rsAllCustomers!ID)
.SubItems(1) = IIf(IsNull(rsAllCustomers!CompanyName), "", rsAllCustomers!CompanyName)
.SubItems(2) = IIf(IsNull(rsAllCustomers!BusinessName), "", rsAllCustomers!BusinessName)
End With
rsAllCustomers.MoveNext
temp = temp + 1
Loop




Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

intDirCount = 0
strDirName = Dir(App.Path & "\", vbDirectory)
Do While strDirName <> ""
If strDirName <> "." And strDirName <> ".." Then
If (GetAttr(App.Path & "\" & strDirName) And (vbDirectory)) = vbDirectory Then
Set strLstItem = ListView1.ListItems.Add(, , strDirName)
intDirCount = CInt(intDirCount) + 1
End If
End If
strDirName = Dir
Loop

the above code for a list view control in a report mode shows adding folder names from ur application path.. i think this may be helpful for u. please excuse me if not.
sudharshaan

[nilaish]

If you have more than 50000 records........do you know how long it takes to fill up the listview??

Thanks,
Nilaish

[Dr_Michael]

Any better solution, friend?

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Lothar Haensler]

in case of many records I'd use a third-party control like FarPoint's Spread control.
This control offers a similar look and feel like a listview, but it offers a "virtual mode", in which not all records are loaded, but the loading is done in blocks, and not all rows are kept in memory.
OTOH, what kind of user wants to scroll through a result set of 50000 records?