Results 1 to 8 of 8

Thread: [RESOLVED] fast write of database recordset to listview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2015
    Posts
    93

    Resolved [RESOLVED] fast write of database recordset to listview

    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:
    Code:
    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

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: fast write of database recordset to listview

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2015
    Posts
    93

    Re: fast write of database recordset to listview

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

    Code:
    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:

    Code:
    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
    ...

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: fast write of database recordset to listview

    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()

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Oct 2015
    Posts
    93

    Re: fast write of database recordset to listview

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

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] fast write of database recordset to listview

    From the sidelines: Shouldn't his code cause an Out-of-Bounds?
    Code:
    Dim Column(27) As String
    .
    .
    .
    Column(27) = ...
    Group = Column(27)
    That is, except if he uses Option Base 1
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Oct 2015
    Posts
    93

    Re: [RESOLVED] fast write of database recordset to listview

    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.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] fast write of database recordset to listview

    Argghh. i always forget that, since i declare arrays always with explicit bounderies MyArray(1 To 27) As String
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width