Filling a combobox in a toolbar from a value in another combobox

Scott Stewart

I ahev a combobox in the toolbar for States. It is loaded when the MDI Form loads. When the user selects the state another combobox in the Toolbar should be loaded with Counties in the selected state. I added two table adapters and edited the Counties SQL so I could find it in code. But no luck. I am not sure what the relationship is. I tired MSDN Reference and did several searches, but I didn't find an explanation (I still need to find a good book). So here is the code I have;
[code]
       private void tblStatesComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Need to get the value of the StateID and use it in the SQL for this Combobox
            private long lngStateID;
            private string strSQL;
            lngStateID=this.tblStatesComboBox.SelectedValue;
            //strSQL="SELECT CountyName, CountyID FROM Counties WHERE StateID=" + lngStateID + " ORDER BY CountyName ASC";
            //Stuff the sql into the right place for the combobox and execute the fill.
            this.tblCountiesTableAdapter.Fill(this.newLandmanDataSet.tblCounties);
        }
[/code]

That's what I want to do, but I can't find trhe right properties in code and can't figure out how to execute the SQL on the second combobox.

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
What you have looks good. It is unfortunate that you had to change your database to integers instead of longs for the keys but otherwise I hope you have learned a lot. Abandoning a project and starting from scratch is normal; I do it. I will do that for something I have been learning.

My body is on a sleep cycle of its choosing, not my choice, and now I am not concentrating so that is all I will say for now.
Scott Stewart14y ago
The solution to the problem was to start all over. I deleted the solution. Got rid of it completely. Then I started from scratch. Here is what I ended up with and it works.

       private void frmParent_Load(object sender, EventArgs e)
        {
            this.tblStatesTableAdapter.Fill(this.newLandmanDataSet.tblStates);
            tblCountiesComboBox.Enabled = false;
        }

        private void tblStatesComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBox cboState = sender as ComboBox;
            string v = cboState.SelectedValue.ToString();
            Int16 myState = Convert.ToInt16(v);
            tblCountiesComboBox.Enabled = true;
            // Now fill the Counties ComboBox with the counties associated with the selected state index.
            this.tblCountiesTableAdapter.FillByState(this.newLandmanDataSet.tblCounties, myState);
        }

Now all I have to do is put "Select State" as the first item in the combo box. I think I saw an article about that somewhere around here.

I just want to say thanks, Sam, for all your help.
Scott Stewart14y ago
Hey Sam,

I have Office 2007 and Visual Studio 2010 Professional. As far as I know I do not have SQL server installed. The last error would be thrown from an add because it is an add of a record to the ComboBox. I just don't know where the constraint is defined. It is buried deep somewhere in the the code and the wizards aren't revealing where that is. That's probably because the wizards are from Washington and I am from Texas. You know how they can get.

I changed my database to integers instead of longs for the keys. I have 24 tables. Almost all of them have a key field and all of the key fields are long integers. In reading up on all of this I found an article about the names of the types. Integers are not just integers. They are Int16, Int32, or Int64. I also found the method Convert. I know that C# is strongly typed, but to change the name of something we have been using for 20 years is rotten. Since Fortran 4 an integer was an integer.

As I said in my post, System.SelectedValue is a string. So every time we deal with the selected value of a listbox (from which a ComboBox is derived) we are dealing with a string. I don't know why MS would do that. That's why all the examples use a string. So to adapt that to my DB design I have to convert that string to the proper integer. So now all I have to do is figure out where the Constraint is that is causing the run time error. Either that, or I have to derive my own ComboBox and make changes in the code that will allow an integer in that place. I don't know if I am up to the task with the little knowledge that I have now.
Sam Hobbs14y ago
I think Microsoft has provided Texans with a different version of Visual Studio and other software. We need to find the documentation of the Texan .Net.

In your earlier post with "long stateID" and the error says "cannot convert from 'string' to 'int?'", as best as I understand what is happening, you have a different version of software.

In your last post you show the error message "Value '43' is already present.". I do not understand that; I do not understand why it is trying to add data.

I assume your data is not personal or proprietary or anything such as that. So you can send your data if you wish. I do not know what version of SQL Server and Office you have but I think I have the express edition of SQL Server 2008 and I have Office 2010. I can use SQL Server Import and Export Data to export SQL Server tables to Access. You can then zip the Access file and post that here. I will create a form with the two comboboxes and then I can at least tell you what works for me.
Scott Stewart14y ago
All the examples I have been able to find use a string for the parameter in the Fill. According to documentation, the "SelectedValue" is whatever the ValueMember is set to. The ValueMember in cboState and cboCounties is StateID which is long. This problem is nearly identical to the MSDN ComboBox example. Except their example uses "WA" as the State. If I go back to the database and change the type of the StateID it will break all of my code in the VB program that is currently using it. I suppose I can do that and fix the resulting bugs, but surely the call to a ComboBox can allow a long. The only methods available are Equals, GetHashCode, GetType, and ToString(). I don't want to convert the value to a string when I need to use it as a long. I suppose "Equals" is so I can set the SelectedValue, but I haven't looked that up. I know what the type is, unless the Data Connection changed it.

OK. The documentation says that the SelectedValue is type System.String. So I have to convert it to type long. What function can do that "Int64.TryParse(cboState.SelectedValue.Tostring());" Throws an exception. If I have to use it as a string how can I say "SELECT * FROM tblCounties WHERE StateID=/" + ? + /" in real code? The real ID (primary key) is a number from 1 to 50. I guess when I built the DB I could have told access it was short int, but I didn't.

Alright! I got through a build without an error. Got an error when run though, but that's better! This is the code now.
        private void tblStatesComboBox_SelectedValueChanged(object sender, EventArgs e)
        {
            ComboBox cboStates = sender as ComboBox;
            string stateID = cboStates.SelectedValue.ToString();
            Int16 myState = Convert.ToInt16(stateID);
            this.tblCountiesTableAdapter.FillByState(this.absrtactDataSet.tblCounties, myState);
        }

The error is
An unhandled exception of type 'System.Data.ConstraintException' occurred in System.Data.dll
Additional information: Column 'StateID' is constrained to be unique. Value '43' is already present.
I am looking for the place where this "constraint" is defined. Certainly not in the DB as there are 257 counties in Texas.
Scott Stewart14y ago
        private void tblStatesComboBox_SelectedValueChanged(object sender, EventArgs e)
        {
            ComboBox cboStates = sender as ComboBox;
            long stateID = cboStates.SelectedValue;
            this.tblCountiesTableAdapter.FillByState(this.absrtactDataSet.tblCounties, stateID);

        }

Errors:
Error    1    The best overloaded method match for 'Plant.AbsrtactDataSetTableAdapters.tblCountiesTableAdapter.FillByState(Plant.AbsrtactDataSet.tblCountiesDataTable, int?)' has some invalid arguments    C:\Users\Scot\Documents\Visual Studio 2010\Projects\Landman\Plant\MDIParent1.cs    131
Error    2    Argument 2: cannot convert from 'string' to 'int?'    C:\Users\Scot\Documents\Visual Studio 2010\Projects\Landman\Plant\MDIParent1.cs    131

The StateID is a long in ALL cases. It was defined that way when the DB was created.
Sam Hobbs14y ago
First, note that the second Fill method takes two parameters. When we created the TableAdapter, the call to the "Fill" method has just one parameter; a dataset to be filled. Then we created a second query and the fill method for it ("FillByState"?) takes two parameters; the first one is the same as the one in the original Fill. The second parameter for the second fill is the data for the SQL: parameter (the
state). I think that is causing a problem for you. To determine what the data (its data type) is for cb.SelectedValue, try using the following line for diagnostic purposes:
MessageBox .Show(cb.SelectedValue.GetType().ToString());
That should, during execution, show what type the data is.
Scott Stewart14y ago
I am using a table adapter. It has the SQL statement in a new method called FillByState. So all I have to do is call FillByState and pass the StateID.
Sam Hobbs14y ago
I assume you are enterying the SQL into your code and trying to execue it directly without using a TableAdapter. If you do not use a table adapter then you can ignore most or all of what I said and instead you can use the code in an article in this websie.
Scott Stewart14y ago
What I am running into is that cb.SelectedItem is not returning a long which is the ValueMember type. That was the way it was designed anyway. So let me break this down and you can tell me where I am making my mistake.

private void tblStatesComboBox_SelectedIndexChanged(object sender, EventArgs e) //The event that is fired when a state is selected
        {

            ComboBox cb = sender as ComboBox; //The sender is the object that raised the event and this creates a variable of Type ComboBox to provide access to the sender.

            if (cb == null) return;

            long v = Int64.TryParse(cb.SelectedValue); //The offending line where the SelectedItem should be hiding

            this.tblCountiesTableAdapter.FillByState(v); //I didn't understand the call in your example to this method. The way I have it is the method is supposed to accept the long value and execute the query. The actual finished query is "SELECT * FROM tblCounties WHERE StateID=43" 43 is the StateID for Texas (and the only state in my DB with Counties).
       }

The State Table "tblStates";
StateID long
StateName String
StateAbbreviation String

The County Table "tblCounties"
CountyID long
CountyName string
StateID long

I got the database and Visual Studio 2010 choked on it when I tried to connect to it. I'll guess mess with that another time. Again, thanks for your time and knowledge.
Sam Hobbs14y ago
Okay well let me guess. I think what is happening is that the StateID is converted to a string for the ComboBox. So let us assume you need to convert the string back to a long. I assume that the long is a 64-bit integer. So you could convert the string to a long using the Int64.TryParse Method. Look at the documentation for samples of using it. I prefer TryParse since it will protect the program if the data is bad but for this the data is not likely to be bad (unless you make a programming mistake and we know you won't do that).

So yes you do not need to download the sample database if you are able to figure out what fields of your data to use.

Good to hear from you and keep up the good work.
Scott Stewart14y ago
Thanks again Sam. I am still trying to download the database for the example. I stay in a motel during the week at work and they are having trouble with the wi-fi. In the mean time I tried using your sample code for the SelectedIndexedChanged event. I also looked it up on MSDN. Both sample use the ToString() method to return the selected value. The problem is that my selected value is the StateID and it is a long. I tried long v=cboState.SelectedValue; But that is an error. The documentation says that the value returned is the value as defined in the ValueMember which is a long. I would like to continue to try this with my DB while I continue to try downloading the sample DB.
Sam Hobbs14y ago
As I said, those procedures assume you are starting with a fresh new project. Once you do that and have the sample working, you hopefully can apply it to your main program. I am not sure how well it will work for a toolbar but otherwise you should not have much of a problem.

One more note; not all countries have states. So when testing, don't panic if the combobox for states is empty; it might be that there are no states for the country.
Sam Hobbs14y ago
I think what you are trying to do is often called Cascading, as in Cascading ComboBoxes. You should look for articles about that. Another term that might apply is Master/Detail. You probably encountered those terms while doing Access. The concepts are relevant here too.

I assume you are using Windows Forms. Most developers around here are using WPF and Silverlight but I only know Windows Forms. I did not find an article that shows Cascading ComboBoxes using TableAdapters so I started writing one. The following is most of it; I did not show all the details but I hope it is enough for you. Note that this uses the Adventure Works sample database. I hope you have it installed or you can install it. I suggest you do the following using a new project so you can use the same tables and fields. For this data, I am creating two ComboBoxes; one for countries and one for states. I think that is the same as what you need in the concepts. Upon completion of the following, you will have a working demonstration of what you need (I hope).
- Use the Data Sources window. If necessary, go to the form in design view then "Data | Show Data Sources".
- Add a new data source.
- In the "Data Source Configuration Wizard" window:
- Then in the Data Sources window (and in design view for the form) expand the node for the CountryRegion table.
- Click on the Name field and a dropdown box will appear; click on "ComboBox". If ComboBox is not an available option, then click on "Customize" then under "Associated controls:" select "ComboBox" then click on "OK".
- Click on the StateProvince table and select "ComboBox" as above.
- Click on the table (CountryRegion) and in the dropdown box and select "ComboBox".
- Click on the table (CountryRegion) and drag it to the form.
- Build and test the project to ensure it works as expected so far.
- In the Data Sources window, right-click on the DataSet (AdventureWorksDataSet) at the top and then select "Edit Dataset with Designer".
- In the designer, at the bottom of the StateProvince table is "Fill,GetData()". Fill and GetData are queries for the table. First, click on that row (the bottom row) to select it, then right-click on it then select "Add Query".
- Then select "Use SQL statements" (the default), Next, then "SELECT which returns rows" (the default), Next. Edit the SQL select to include a "where" to select just a CountryRegionCode. You can click the "Query Builder..." to get an interactive tool to help you alter the SQL. Then click Next.
- There will be two textboxes saying "Method name:". Those are for giving meaningful names to the two new functions. You can keep the default or you can change them; I will change "FillBy" to "FillByCountry" and change "GetDataBy" to "GetDataByCountry". Click Next, then Click Finish.
- Go back to the forms designer; double-click on the combobox and a handler for the SelectedIndexChanged event for the ComboBox will be added to the form.
- Then in the Data Sources window do the same for the StateProvince table as for the CountryRegion table except use the Name and StateProvinceCode fields for the ComboBox.
Almost done. Go to the form's code view; look in the form's load event handler (Form1_Load). There will be two lines (with associated comments) that call the "Fill" method for each of the two TableAdapters for each of the two ComboBoxes. We need to move the one for the stateProvinceTableAdapter to the SelectedIndexChanged event for the CountryRegion ComboBox (the event handler previously added). Then instead of calling "Fill" call "FillByCountry". We will also add the parameter to the method call. This will refresh the ComboBox for the StateProvince table when the selection in the CountryRegion ComboBox changes. So the countryRegionComboBox_SelectedIndexChanged method will look something like:
private void countryRegionComboBox_SelectedIndexChanged(object sender, EventArgs e)

{

ComboBox cb = sender as ComboBox ;

if (cb == null )

return ;

string v = cb.SelectedValue.ToString();

this .stateProvinceTableAdapter.FillByCountry(this .adventureWorksDataSet.StateProvince, v);

}