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).
Thanks.
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).
Thanks.
deighton
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?
ASKER
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.
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
http://www.codeproject.com/KB/cpp/autocomplete_combobox.aspx
http://www.codeproject.com/KB/combobox/csautocomplete.aspx
As a first step, try these examples and see if you can use these to start with
http://www.codeproject.com/KB/cpp/autocomplete_combobox.aspx
http://www.codeproject.com/KB/combobox/csautocomplete.aspx
ASKER
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....
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....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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+.
Code cruiser...thanks for hanging with this project - u get 100% A+.
ASKER
Complete understanding not only of the technical, but of the user vs. developer vs. management issues.
Gave the best, reasonable solution.
Gave the best, reasonable solution.
ASKER
Also, I will use the links you provided.
Glad to help :-)