show 100 000 records in listview

[sveta]

Anybody help, please!!!
We need our listview or grid(we haven't chosen yet) to show 50 -200 000 records from our Collections. But it does very slow. And when listview have 20000-40000 records it starts to work with errors. Maybe there are some algoritms or libraries for part loading, reloading...?
Thanks,
Svetlana

 

[ahhuang]

it is not advisble to load so many records at 1 time in the grid.If i am not wrong,u are looping each record then display into listview. Listview can contain about 37000 records only. I beleive users will not also like to see so many records at 1 time,i have a suggestion.
Load abt 20-50 records at a time. add 2 command buttons next,previous.in this way,if user press 'next',load the following 20-50 records or 'previous' to load last 20-50 records.

 

[sveta]

Yeah, I agree. It's not the best idea to load a lot of records on poor listview. The problem is - the boss...
We are going to load records by parts. But it's hard work (scroll,sorting). And we are thinking of sorting in collections(not in the listview)
Maybe somebody had such problems? Maybe exist some libraries which can help?

 

[Snaggs]

I agree with the other contributors to this post. 50-100 is about the max items you'd want in a listbox. Can you break it down into groups? Assign each item a group and then show only that group? How about a search mechanism that allows them to show only the records they're interested in. Studies have shown that if you can't find something in the first 5-10 seconds of looking for it, you'll move on. If the interface is clumsy or hard to navigate the user will give it less time than that.

Now you have to ask yourself, how important is it that the user find what they're looking for. If it means a sale then I'd say it's pretty important and I'd rethink having so many items in once place, sorted or not. Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[sveta]

Our boss sais that all records should be loaded and it isn't discussed. And we think how to do it less sickly.
I aggry it's not too smart to have listview with thousand records in it, but we have to relise it. We think about having records in the hash and loading listview by parts
But i think maybe exist libraries which can do what we are going to do ourself.

 

[CajunCenturion]

Since its not possible to show all of these records at the same time, perhaps you could use the listview with say 25 or 30 records, whatever is visible, as a window into the recordset. When scrolling occurs, repopulate the listview with the 25 visible records. This may alleviate both performance and memory problems.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Snaggs]

sveta,

E-Mail me by clicking on the address below my signature so I have your e-mail address. I'll send you some sample code.

Dosvedanya Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[IFELSETHEN]

Listbox wont' do it. Get a good grid that will load only a screen's worth of records at one time.

 

[Snaggs]

Here's the code I was going to send you. You need to add the DataGrid to the project. Select Project -> Components. Then select "Microsoft DataGrid Control 6.0 (SP4)(OLEDB)". Then place one in the top left corner of the form. Then open the code window for the form and remove all code in it and replace it with this code. You might have to adjust where the database is located.

Option Explicit

Private s_cnnDB As ADODB.Connection
Private s_rsData As ADODB.Recordset

Private Const s_strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO.MDB;Persist Security Info=False"

Private Sub Form_Load()
    Dim objField As ADODB.Field
    Dim intIndex As Integer
    Dim strSQL As String
    
    Set s_cnnDB = New ADODB.Connection
    
    'Connect to the database.  You might have to change the connect string to point to the BIBLO.MDB file.
    s_cnnDB.ConnectionString = s_strConnectString
    s_cnnDB.Open
    
    'Instantiate the recordset.
    Set s_rsData = New ADODB.Recordset
    
    'The query has an order by clause in it.  If you want a different sort order or if
    'you want the fields in a different order, move the fields around or sort by a differnt
    'column.
    strSQL = "SELECT * FROM [All Titles]"
    
    s_rsData.PageSize = 100
    
    'Open the Query in Access.  This is not a table, but a query.
    s_rsData.CursorLocation = adUseClient   'Must be a client side cursor to use this grid.
    s_rsData.Open strSQL, s_cnnDB
    
    'Add columns to the grid.
    For Each objField In s_rsData.Fields
        'The grid already has 2 columns defined.
        If intIndex < 2 Then
            DataGrid1.Columns(intIndex).Caption = objField.Name     'Col Name.
            DataGrid1.Columns(intIndex).DataField = objField.Name   'rsFileld name to bind to.
        Else
            DataGrid1.Columns.Add(intIndex).Caption = objField.Name 'Col Name.
            DataGrid1.Columns(intIndex).DataField = objField.Name   'rsFileld name to bind to.
        End If
        intIndex = intIndex + 1
    Next objField
    
    'Bind the control to the recordset.
    Set DataGrid1.DataSource = s_rsData
    
    'Change these to allow the user to do what you want them to do.
    DataGrid1.AllowAddNew = False
    DataGrid1.AllowDelete = False
    DataGrid1.AllowUpdate = False
End Sub

Private Sub Form_Resize()
    'Adjust the grid if the form is resized.
    DataGrid1.Width = Me.ScaleWidth - 250
    DataGrid1.Height = Me.ScaleHeight - 250
End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Tidy up before we leave.
    s_rsData.Close
    Set s_rsData = Nothing
    
    s_cnnDB.Close
    Set s_cnnDB = Nothing
End Sub

Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[Snaggs]

Oh, and set a reference to the "Microsoft Active Data Objects Library".
Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[MichaelRed]

On the theme of " ... the boss ... ... ... ". Show him the contents of this thread. Let him read it. Let him know the aggregate experience here is at least ten years of Visual ... Note that he is simply asking for a soloution that isnt going to happen (at least not in the current releases). Start looking for a place of employment which includes 'bosses' who know their limitations and respect their employees.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[sveta]

It's the first really silly idea of my boss. So i think it's early to change work
About DataGrid. The problem is we can't use connected recordsets.We can keep data in disconnected recordsets, but i'm not sure that it would be a good idea. We work by client-server architecture. And we can't jeer at connection. We have our datas in collections. And we load listview from collections. So now we are thinking about "smart" collections which will work with grid(listview).
I thougt maybe somebody had the same problem)

 

[sveta]

I heard XpressGrid can help. Anybody used it?

Sveta, forseb@mail.ru

 

[Snaggs]

The Microsoft Data Grid will work in the disconnected mode also. You can populate the recordset from the collection. However I wouldn't recommend that approach if you're loading the collection from a recordset already. Then you'd have something like this.

DB -> Recordset -> Collection -> Recordset -> Grid

Why not just go:

DB -> Recordset -> Grid

and cut out the collection in the middle and the extra recordset? Just load the data in a recordset instead of a collection then bind the grid to the recordset.

One other comment I'd like to make is that Michael is very correct about "The Boss thing". Why hire experts if you're not going to take their advice? Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.