VB.NET 2008 Need help with algorithm - multiple combo box searches and populations from SQL

Hello there,

I have a Microsoft SQL Server database with five fields:  ConsRowID (int primary key identity), ConsID (int unique not null), LastName (varchar(64) not null), FirstName (varchar(64) not null) and Birthdate (date null).  I want to allow the user to search for different users by first name, last name, or Consumer ID (ConsID), and finally by birthdate given same names.  It is expected that the ConsID will be unique, but I'm going to use ConsRowID as my identifier for now.

This is a table of Consumers.  A consumer has a unique Consumer ID (ConsID), but we frequently have many with the same name.  I want four combo boxes:  Consumer ID, Last Name, First Name, and Birthdate.  If a person searches by Consumer ID, that will simply populate the remaining  combo boxes with the one person that matches this.

But if want to search by last name, I would want the combobox to be populated with all soundex values from the table that equal that input.  So if I start typing in Smith, it will also populate with Smyth.  The consumer ID combo would populate with all consumer ID's that match anyone with last name of Smith or Smyth (or any other Soundex matching Smith), and the First Name field would populate with a unique list of first names of these individuals.

Likewise, I could do the same thing with First Name: assuming that there is no selection in the Consumer ID or Last Name field, I could type in "Francisco" and that would populate the Consumer ID and Last Name fields with every individual whose first name is "Francisco".

Same with Date of Birth.

If existing values have been selected in any of the combo boxes, then they would serve as filters.  E.g., If I type in Smith in the last name, and select "Smyth" then the resulting values available to choose from First Name and Birthdate would only apply to anyone with the last name of Smyth.  

We have to assume that there may be two John Smith's born on the same exact day given worst case scenario, but generally, First Name, Last Name, and Birthdate would boil it down to a single individual.

I'm really having a hard time, for some reason, putting the logic together on how to approach this.  It involves some appropriate stored procedures in SQL, some appropriate event handlers for each of the combo-boxes, including dynamically populating them based on other selections in other combo boxes; and some autocomplete processes data sources for each combo box.

It would be so much easier if the user just used Consumer ID's, but they are adamantly against this.

I'm not looking for exact code...just an algorithm (although if you provide code, so much the better).  

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

look into the SOUNDEX function in SQL server
What part of this you need help with? Do you have textboxes against each combobox or do you want to type in the comboboxes?
kmoloneyAuthor Commented:
I want to type into combobox and have it give filtered possible solutions.  If separate text box is required for simpler solution, so be it, but my ultimate desire is to create a custom control due to how often this array of controls will be necessary...but not asking for custom control code at this time.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

The ability to filter based on what's being typed in the combobox can be embedded into a control but many things such as one combobox only listing values after filtering based on another combobox (DOB combobox only showing people with Smyth lastnames) would require work on the container control/form.

As a first step, try these examples and see if you can use these to start with



kmoloneyAuthor Commented:
Thank you for the suggestion.  I apologize for being so slow in responding; I'm afraid I'm only able to get to this on weekends, lately.  To put it into a practical sense, lets say the users are fairly lazy, well-payed, and well-backed in their sympathy.  As a rule, entering the unique ConsID identifier would instantly identify the person uniquely (from the department of the redundancy department).  That would be our choice.

In the field I am in, people choose not to identify people by numbers -- it is abhorrent to them -- and want to identify individuals by name.  This was fine when our customer base was a couple hundred, but now we exceed several thousand.  We have individuals that go by aliases, and lots of individuals who are known as Jack's ("Johnathan"), Chucks ("Charlie's), and lots of O'Neals, Neals, O'Neils, Smith's, Smyth's, etc.  The users are willing to double-check user birthdates, and Consumer ID's as a last resort, but they'd prefer just to put in Jack Rigley (when the person's name might be Johnathan Wrigley).  Not even a SOUNDEX can match "Jack" with "Johnathan".

It would be so much simpler if they could just type the Consumer ID (ConsID).  It's an uphill battle between Information Systems and those who feel they should not need to "look up" the ID.  So it's political as well as technical.  

Sorry for the rant....
I understand your pain. Ask your management if they can justify the time, effort, and money being spent just because people like to call people by different names.

You may want to use a tags field and try searching through it. So a customer may be Johnathan but a tag of jack can identify the customer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kmoloneyAuthor Commented:
OK, I'm closign this thing, and requiring the entry of ConsID to uniquely identify an individual.  I may make a search application, based on a dumbed-down search using regex, to find a consumer, but that will be a totally separate lookup app, and will in no way guarantee that they are using the correct client ID.  So it will be an add-on "utility," but not a guaranteed lookup.

Code cruiser...thanks for hanging with this project - u get 100% A+.
kmoloneyAuthor Commented:
Complete understanding not only of the technical, but of the user vs. developer vs. management issues.

Gave the best, reasonable solution.
kmoloneyAuthor Commented:
Also, I will use the links you provided.
Glad to help :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.