[RESOLVED] fast write of database recordset to listview

[Krzysztof#]

hi, my question is: is possible to create DataSource for listview just like it is possible for dataGrid control?

in example with ado data control i have:

Set gridData.DataSource = adoData.Recordset

and this puts lots of data in datagrid instantly

however if i want to put it in listview i read and write record by record and when i have 8000 rows it takes very long time

[DataMiser]

You really should not use a listview to display 8000 rows of data.

There are ways to add the data pretty quickly and ways to load it very slowly. Since we do not know what method you are currently using then it is hard to say what may be done to speed it up other than not loading so many rows.

[Krzysztof#]

ok, i see. so in frmlist form i have something like this:

Public Sub LoadList()

Dim Group As String
Dim Column(27) As String
Dim li As ListItem

Call Class.SetStart() 'set first record
Do While Not Class.EOF
        
            Column(1) = Class.pobSymbol 'get from variables
            Column(2) = Class.pobNazwa
            Column(3) = Class.pobAdres
            Column(4) = ...
...
            Column(18) = ...
            Column(19) = ...
            Column(20) = ...
            Column(21) = ...
            Column(22) = ...
            Column(23) = ...
            Column(24) = ...
            Column(25) = ...
            Column(26) = ...
            Column(27) = ...
Group = Column(27)
name = Column(2)
                        Set li = lstList.ListItems.Add(, , CStr(name))
                        li.SubItems(1) = LTrim(Column(3)) 
                        li.SubItems(2) = LTrim(Column(5)) 
                        li.SubItems(3) = LTrim(Column(4)) 
                        li.SubItems(4) = LTrim(Column(8))
Symbol = Column(1)
                        li.SubItems(5) = Symbol
                        If UBound(Column) > 16 Then
                            If Column(17) <> "" Or Column(18) <> "" Or Column(19) <> "" Or Column(20) <> "" Then
                                li.SubItems(6) = Column(17) & "," & Column(18) & "," & Column(19) & " " & Column(20)
                            Else
                                li.SubItems(6) = ""
                            End If
                        End If



            Class.SelectNext' next record
        Loop
End Sub

then in class module:

Public Sub SetStart() 'set first record

    frmlist.adoData.RecordSource = ""
    frmlist.adoData.ConnectionString = ""
    frmlist.adoData.CommandType = adCmdText
    frmlist.adoData.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileLocation & ";"

    frmlist.adoData.RecordSource =  "SELECT * FROM " & tablename
    frmlist.adoData.Refresh
Set adoRecSet = frmList.adoData.Recordset
If (adoRecSet .EOF And adoRecSet .BOF) Then
bolEOF = True
Else
Call adoRecSet .MoveFirst
Call WpiszDoZmiennych(adoRecSet )'put into variables
bolEOF = adoRecSet .EOF
End If
End Sub



Private Sub WpiszDoZmiennych(Dane As ADODB.Recordset)'put into variables
        If Dane(field1).ActualSize > 0 Then strSymbol = Dane(field1)
        If Dane(field2).ActualSize > 0 Then strGrupa = Dane(field2)
        If Dane(field3).ActualSize > 0 Then strNazwa = Dane(field3)
        If Dane(field4).ActualSize > 0 Then strAdres = Dane(field4)
...
End Sub




Public Sub SelectNext()' select next record
On Error Resume Next

        Call adoRecSet .MoveNext
        If (adoRecSet .EOF Or adoRecSet .BOF) Then
            bolEOF = True
        Else
            Call WpiszDoZmiennych(adoRecSet )'put into variables
            bolEOF = adoRecSet .EOF
        End If

    
End Sub



    Public Function pobSymbol() As String
        pobSymbol = strSymbol
    End Function

    Public Function pobNazwa() As String
            pobNazwa = strNazwa
    End Function

    Public Function pobAdres() As String
        pobAdres = strAdres
    End Function
...

[DataMiser]

Well It looks like you are reading the data from the recordset into a variable then reading the data from that variable into another variable then reading from that variable into the list view so that is adding time to the process how much I can't say but there is no need for the 2 extra assignments. You can read directly from the recordset into the listview saving steps, lines of code and time. You also can make the listview not visible while adding item which will speed it up a bit, not sure if listview has an option to turn off auto redraw or not. If that option exists that also will speed it up, a lot if you are adding a lot of data as it does not have to keep redrawing itself.

I also notice that you are calling LTrim. You really should not have leading spaces in your data that need to be trimmed off but if you do then make sure to only use trim on the fields that may need it and use the proper string version LTrim$() rather than the slower variant version LTrim()

[Krzysztof#]

Thank you very much for tips, it saved about half of time when filling the list now

[Zvoni]

From the sidelines: Shouldn't his code cause an Out-of-Bounds?

Dim Column(27) As String
.
.
.
Column(27) = ...
Group = Column(27)

That is, except if he uses Option Base 1

[Krzysztof#]

Zvoni no. i think not. if there is 'Dim Column(27) As String' it creates arrays from 0 to 27. so 'Column(27) = ...' is ok just i do not use Column(0) variable here.

[Zvoni]

Argghh. i always forget that, since i declare arrays always with explicit bounderies MyArray(1 To 27) As String