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

Question

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.

Sam Hobbs · Answer

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 Stewart · Answer

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 Stewart · Answer

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 Hobbs · Answer

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 Stewart · Answer

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 Stewart · Answer

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 Hobbs · Answer

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 Stewart · Answer

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 Hobbs · Answer

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 Stewart · Answer

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 Hobbs · Answer

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 Stewart · Answer

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 Hobbs · Answer

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 Hobbs · Answer

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: For Where will the application get data from? select Database then click Next. For What type of database model do you want to use? select Dataset then click Next. For Which data connection should your application use to connect to the database? select the AdventureWorks database then click Next. For Do you want to save the connection string to the application configuration file? keep the default; just click Next. For Which database objects do you want in your dataset?expand the Tables node then select the tables you need (CountryRegion and StateProvince) then click Finish. 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 FillGetData(). 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); }