General Search question, ADO, MS-SQL

[StormbringerX]

No problem here, just curiousity. We are using VB6 and MS-SQL 6.5 to access moderately large databases in a 2 tier app. Roughly 40 tables each with anywhere from 10,000 to 60,000 records. Do any of you have a particular method and/or controls you prefer to use for searches?

For example, we have an Account table that contains approx 60,000 records. We would like to incorporate a name lookup. The combobox is ill-suited to this (or at least we haven't figured a way to compensate for the slow process). We have tried MSFlexgrids, Listviews, etc... but the overhead is still quite large and the app runs slow. Any suggestions? Or any books/websites that may shed some light on this?

Thanks so much,
Dave

 

[VB400]

You're right Dave, I wouldn't load such a huge table into a combobox or any other control for that matter as it requires processing all records.

We have a table that has over 200,000 records and what we do is let the user type the first name and last name into a couple of textbox controls. Then they click a "Search" button and the program builds a SELECT statement based on the data entered. Additionally, if they enter an asterisk "*" in the name then we use the "LIKE" operator rather than the "=".

This is very quick and powerful and does not burden the server with reading all records. We later added a "soundex" feature that is also very helpful.

I would like to know how others do it though, so please keep on posting.


Tarek

The more I learn, the more I need to learn!

 

[StormbringerX]

Thanks Tarek!
I hate to beat a dead horse, but once you execute the SQL statement, do you load the results into any particular control? Ideally, I would like to have the results displayed and when the user doubleclicks a particular item, that info would be displayed in a standard form so that they can edit or delete.

Appreciate the help!
Dave