Faster way to select values in Visual Studio 2008

I have a report in Visual Studio which has a parameter of DrugID.  This table contains over 158,000 drug ID's.  Currently when the report is run the user selects the drop-down and types in say "W" to find Warfarin.  There are several different types of Warfarin based on dosage.  They have to scroll down the list FOREVER because the list is still loading ALL the DrugID's.   I would like them to be able to have a progressive look up.   So, say you type W-A-R and it takes you to all drugID's that start with WAR instead of having to scroll down to find the drug.

Is this something I create in Visual Studio or is this something I have to create in SQL?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Mike EghtebasDatabase and Application DeveloperCommented:
Also consider adding index to DrugName column (for Warfarin). This may be a better solution.

kvrogersAuthor Commented:
I have never done that.  How would I add an index?  

DrugID is part of a dataset used in the Visual Studio report.  I forgot to mention that, sorry.
Mike EghtebasDatabase and Application DeveloperCommented:
I suppose you can discuss with your database admin stating you need to search DrugName name and you want to know if s/he could add an index on this column?

This is not something you can do if you are not the database admin. Other than this, you may post a question for ajax solution where data gets updated in the controls incrementally the way you have described above.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

kvrogersAuthor Commented:
Here is how we got this to work:
1.  In SQL we created a new Stored Procedure called  spDLookupDrugSearch  .
2.  We created a parameter in our report called @Drug based on this Store Procedure
3.  In the where clause we added this:  Where xx.DrugID LIKE @Drug + '%'  
 In Visual
1.  We created a parameter call Drug Search. (this must be listed first in Parameters)
2.  Then we created another dataset and called it DataSet3 which used the field parameters in Query - EXEC spDLookupDrugSearch @DrugSearch
3.  Now we created another new parameter called Drug.  This used the DataSet3 as the value parameter with Drug as the value field.

4.  When we run the report it has a Drug Search box where we type in WAR.  
5   There is also a box Called Drug which when opened starts with all Drugs that begin with WAR.


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
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think this is a SQL Server "thing" but Visual Studio's.
You was loading 158000 rows into the client machine memory. If there's a way you could reduce the number of rows than it will be better and faster. For example, there's no category, group or type for the drugs? If so, you could one enable the drug field after the user select a category/group/type so you'll only need to bring back the correspondent drugs.
kvrogersAuthor Commented:
they like the way we created it now.  If there are several drug ID for Warfarin, they can type in WAR (we also fixed so if they type in lower care it will automatically look for upper case) it will show in Visual Studio as Drug ID: WAR (tab) Search:  all Warfarin drug ID's that start as WAR.

Thanks for the suggestion will keep it in case another department would prefer that search suggestion.

kvrogersAuthor Commented:
Please close this question .
We fixed this problem by adding a new stored procedure to our SQL report which we added to our Visual Studio report as listed above.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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
Visual Basic Classic

From novice to tech pro — start learning today.