Listbox 25,767 record limit strategies

[Amesville]

Hi Folks

I'm being assigned to a project at work that has an issue displaying data returned from a recordset. I have not seen the code yet, but they have described it to me - the recordset is very large, well over the 25,767 item limit to a listbox, and I think that is the problem - the listbox can't display anything because there are too many records.

I know from a google search that this is not an uncommon problem, but I'm trying to find solutions so that I can try and hit the ground running when I get the code. I'm not sure how much I'll be allowed to change the code, and I've been told that asking for changes to the SQL statement that fills the recordset may not be greeted with a lot of acceptance.

Can anyone suggest some strategies I can investigate on how to deal with this? I've seen some mention of paging methods but not much in terms of how to set that up. Can someone direct me to a resource? Any other ideas I should look at?

Thanks for your time

Craig

 

[strongm]

What 25,767 item limit?

There used to be a 32767 item limit on 16bit systems, based on the fact that a Listbox index is an integer that can have a maximum value of 32767. No such limit that I am aware of exists on 32 and 64bit systems

Having said that, trying to jimmy so may rows into a Listbox suggest that you are using thhe wrong control. DO you have other options you could use, such as a ListView?

 

[Andrzejek]

It would be good to know what data is displayed in the listbox. If you have, for example, names of people in the list, you may introduce another way of searching for the name.

It is hard to imagine users want to have 25,000+ items in one control to go thru.


Have fun.

---- Andy

 

[Amesville]

I agree completely with your assessments, one of the things I intend to do is try to find out why the list is so large and why does it need to be crammed into a listbox if it's so huge? There has to be a better faster way to allow them to zero in on the item they are looking for.

I have not seen the data yet but I believe it is product names. I don't know yet if there is some sort of category value I could filter the list on, reducing the full list to something more manageable.

I don't yet know how much leeway I have to make changes, but I'm figuring some way to cut down on the list with a filter (maybe have them start typing the name of what they are looking for into a text or combo box) until the limit is under the threshold, but what it they don't know how to spell it?

I'll investigate the listview idea, thanks.

Craig

 

[Amesville]

Well I still haven't seen the code yet, but I did find out the listbox is a third-party control. A control called "Spread" by Farpoint. No version information yet, but it seems like the problem is still as previously described.

One possible strategy I thought about was creating a clone of the recordset made by the Data returned from SQL, and populating it with a subset of the data and connecting that to the listbox. Of course the idea would then be to 'page' the data so when the first set was run through it would go get another set and drop the first one, and so forth. But I don't know how to do that - any suggestions on where I can learn how to do that?

Thanks

Craig

 

[strongm]

So it's a third party Excel-compatible spreadsheet control, not a listbox of any sort. So the problem is not a VB problem, but a Spread COM problem. You probably need to talk to Farpoint Technologies, but here's one thought - Spread has a virtual mode it can be switched into which is specifically designed to improve performance with large datasets.

(and I'm not quite sure how you determined that there was a common problem when you didn't actually know what the control was ...)

 

[Amesville]

Thanks Strongm, I will check into that. I will also try to contact Farpoint as soon as I can actually get my hands on the source code (still have not been given access yet).

As far as determining the "common" problem, when I was first told about the issue I was being assigned I was only given some basic information - Description of the problem, "Listbox has stopped working, may be related to number of records. In recent times the size has grown very large as more and more products have been added to the database." From there I started searching around on the nets and uncovered references to listbox controls having trouble with record counts over 25,767... So I assumed that would be the most likely culprit. When I get the source code, if that is the problem I'll already have a leg up on a solution - but my conclusions could be wrong.

I very much appreciate the advice and hope I have not wasted your time.

Craig

 

[strongm]

>From there I started searching around on the nets and uncovered references to listbox controls having trouble with record counts over 25,767... So I assumed that would be the most likely culprit

Which takes us back to my first question: what trouble with record counts over 25767? I am unaware of such a limit with the VB listbox control.

Secondly, even if there were to be such a limit for a listbox control why do you believe it might be the culprit for a completely unrelated control?

 

[strongm]

Hello?

 

[Amesville]

It probably isn't related. When I asked the question originally I didn't know it was a third party control. I found references out on the web that indicated the VB6 listbox had issues when the number of records in a recordset it was attached to exceeded 25,767 records, exhibiting broken behavior similar to the described problem in the application. I made an assumption that this might be the cause of the problem and since I had no better data at the time, decided to start preparing as though that would be the issue when I got ahold of the code and could test it for myself. I later learned it was the Spread control from Farpoint, though I still do not have access to the code or control yet. I was just trying to be proactive with a potential solution to a problem I had very little information about.

 

[strongm]

>the VB6 listbox had issues when the number of records in a recordset it was attached to exceeded 25,767

And I repeat, where did you find such info? Can you provide a relevant link?

 

[gmmastros]

I have experience with the farpoint spread control. I've been using it for approximately 10 years now. I have never experienced a row limit with it. Since you don't have the code yet, I would encourage you to ask what version of the control is being used.

I started off using spread 3 a very long time ago. Then upgraded to spread 7 about 5 years ago (ish). I think there is a version 8 now, but I'm not certain about that. Regardless, it could be that the simple fix for the problem is to upgrade to a newer version of spread. (just a thought).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom