?
Solved

filter values in a combo box based upon selection in another combo box

Posted on 2009-12-22
10
Medium Priority
?
808 Views
Last Modified: 2012-05-08
Alright, I am transitioning over to Visual Studios from VBA and I'm still working though some of the differences.

I have a data driven application connected to an MSSQL database.  I have a form that I am using to allow the end user to post to it.

What I am trying to do right now is I have one combo box with a list of abbriviated State names (abStateComboBox) and another combo box that has a list of all US Counties (countyComboBox), both are bound to tables in the SQL.  (tblState and tblCounties respectively)  the counties table also includes the corresponding abbreviated state names associated with the county.

All I need to do is simply filter the counties based upon the selected text in the stateComboBox so that only the counties applying to that abbreviated state will apply.  It's a very simple SQL query, but I am not sure how to translate it into VB.Net properly.

Thanks for the help!
0
Comment
Question by:gwarcher
  • 5
  • 4
10 Comments
 
LVL 7

Expert Comment

by:technofile
ID: 26107703
What you need to do is create a new query on the event selectedvaluechanged on the state drop box and repopulate your county box. In the properties window there is a lightingbolt button that shows all the events, double click on the selectedvaluechanged place and it will create a subrouting for you to program in.
0
 

Author Comment

by:gwarcher
ID: 26108078
How do I in VB Syntax set the variable from the first stateComboBox in the select statement?
0
 
LVL 5

Expert Comment

by:bhagwantsingh
ID: 26111134
If your abStateComboBox is bounded with the datatable and you assigned ValueMember property with CountryCode of the state than you can easily search the country without firing SQL query to the MSSQL server. You can filter your countyComboBox using its DefaultView property.

e.g.
countyComboBox.DataSrouce.DefaultView = CountryID = COUNTRYID

This statement will filter your country combobox according the state you selected. You can do this thing with counties table also. You must bound your countryComboBox with the datatable/dataset.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 7

Expert Comment

by:technofile
ID: 26113869
str = cboState.SelectedText

will give you the value that you see in the drop down box.
0
 

Author Comment

by:gwarcher
ID: 26113947
I'm still not 100% clear. What would be the most efficient means of taking the value of the selected text from cboState and then filtering my dataset (table adapter?) to only show counties with the related state variable?

I apologize for not fully getting it just yet.
0
 
LVL 7

Expert Comment

by:technofile
ID: 26114215
How are you filling your state combobox?
0
 

Author Comment

by:gwarcher
ID: 26114303
Attached is the picture and the code from the Main Form Load Event
Me.TblStatesTableAdapter.Fill(Me.MartinezContributionsDataSet.tblStates)
        Me.AbStateComboBox.Text = "NM"

Open in new window

stateComboBox.png
0
 
LVL 7

Accepted Solution

by:
technofile earned 2000 total points
ID: 26114545
Ok, that is what I thought, what you need to do is modify your TblCountyBindingSource, or whatever you call it in the statechange even that I first mentioned there should be a query property or sqlquery property or something like that that you can change to something like "Select countycode, county from county where state = '" & cboState.SelectedText & "'". Right now if you read it while your program is running it should say something like above without the where clause. I personally don't use databinding because it is very limited and many times can cause a whole lot of problems.

I would take the time to learn about the sqlclient, the sqlclient.dataadapter, and datatables
http://www.startvbdotnet.com/ado/sqlserver.aspx
http://vb.net-informations.com/ado.net-dataproviders/ado.net-sqldataadapter.htm
http://functionx.com/vbnet/databases/datatable.htm

Once you understand these few things you will be able to do whatever you want with vb and databases much easier and if it can be done you will be able to do it.
0
 

Author Comment

by:gwarcher
ID: 26114714
Thanks technofile.  Those articles I assume are to replace the binding source with the sql classes mentioned?
0
 
LVL 7

Expert Comment

by:technofile
ID: 26115174
Yes, binding allows you to simply view data but once you start manipulating it, or even something like filtering you have to start manipulating the bindings. The sql classes mentioned in the last post allows you, in my opinion a more flexible solution to dealing with databases.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question