Combobox relies on previous combobox

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
I have an app where I want to have the user select a choice from a combobox. Such as "CompanyA". Then, some textboxes are filled in with company information from the company table. I have one dataset and multiple data adapters that populate the dataset tables. Here's the part I think I'm messing up.

When the user selects, say, "CompanyA", I want the choices for the next combobox to only show "items" for that company from the item table. But, if I try to use selectedindex on the first combobox and try to check the combobox.text, it is always blank. Any ideas? I can post some code, but if you tell me in general the process, I can try that first. By process, I mean, "first you bind this, then set that, then do this". Then I can see if I'm doing it correctly. I'm pretty new, so I don't want to give you some cheezy newbie code. Thanks!
 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
----------------------------------
Here I am filling the first combobox
with the companies:
----------------------------------
Private Sub frmMain_Load
SqlCommand.Connection = SqlConn
SqlCommand.CommandText = "SELECT * FROM COMPANY"
SqlDA_COMPANY.SelectCommand = SqlCommand
SqlConn.Open()
SqlDA_COMPANY.Fill(dsDataSet, "COMPANY")
SqlConn.Close()
cboCompanyKey.DataSource = dsDataSet.Tables("COMPANY")
cboCompanyKey.DisplayMember = "CompanyKey"
txtbxCompanyName.DataBindings.Add("text", dsDataSet, "Company.CompanyName")
bmbCompany = Me.BindingContext(dsDataSet, "Company")
End Sub
----------------------------------
Here I want to fill the choices
for the second combobox
with the plans available for
only the chosen company:
----------------------------------
Private Sub cboCompanyKey_SelectedIndexChanged
Dim strCompany As String
strCompany = "SELECT * FROM [PLAN] WHERE CompanyKey = @CompanyKeyVar"
Dim SqlDA_PLAN As New SqlDataAdapter(strCompany, SqlConn)
Dim companySQLParam As New SqlParameter
companySQLParam = SqlDA_PLAN.SelectCommand.Parameters.Add("@CompanyKeyVar", SqlDbType.VarChar, 5)
SqlConn.Open()
SqlDA_PLAN.Fill(dsDataSet, "PLAN")
SqlConn.Close()
cboPlanKey.DataSource = dsDataSet.Tables("PLAN")
cboPlanKey.DisplayMember = "PlanKey"
txtbxDescription.DataBindings.Clear()
txtbxWaitingPeriod.DataBindings.Clear()
txtbxDescription.DataBindings.Add("text", dsDataSet, "Plan.PlanDescription")
txtbxWaitingPeriod.DataBindings.Add("text", dsDataSet, "Plan.PlanWaitingPeriod")
bmbPlan = Me.BindingContext(dsDataSet, "Plan")
If cboCompanyKey.SelectedIndex <> -1 Then
bmbCompany.Position = cboCompanyKey.SelectedIndex
End If
End Sub
----------------------------------
Thanks for any help you can give!
 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
I got this error:
----------------
An unhandled exception of type 'System.InvalidCastException' occurred in system.data.dll
Additional information: Object must implement IConvertible.
----------------
On this line:
----------------
SqlDA_PLAN.Fill(dsDataSet, "PLAN")
----------------

Any ideas? Does the rest of my code look ok?
 

Atanas Vanchev

Active member
Joined
Nov 7, 2006
Messages
33
Programming Experience
5-10
Why dont you try debugging it? Is the selected combobox value length > 5 characters? Or is it empty? Try hard coding a value and see if this works
 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
If I msgbox(companySQLParam.Value), I get this:
------------
An unhandled exception of type 'System.ArgumentException' occurred in microsoft.visualbasic.dll
Additional information: Argument 'Prompt' cannot be converted to type 'String'.
------------
If I do this it works, but only gives me values for IBM no matter which company I select:
------------
companySQLParam.Value = "IBM".
------------
 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
I think it's blank, if I do this:
companySQLParam.Value = cboCompanyKey.Text

I get an empty msgbox eventhough a value is displayed in the combobox. I feel like I'm missing something obvious.
 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
I put it in the frmLoad and I can messagebox it and see the text "PlanKey". Then, in the selectedindex I messagebox it and see "PlanKey". Is this what I'm supposed to see?

---oh wait, in selectedindexchanged, I have it listed down below. See my code I posted previously.
 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
OK, now I see each value as I select it. Thanks!
----------
companySQLParam.Value = cboCompanyKey.Text
MsgBox(companySQLParam.Value)
----------

Now I have to figure out how to clear out the Plan combobox in my code so that it doesn't keep adding values on. I use this code to see how many rows are returned and the plan combobox keeps incrementing. If you have any ideas I'd appreciate it! Anyway, thanks for getting me that value to show up.

-------------
Dim iRowCount As Integer 'see how many rows are returned
Dim dt As New DataTable
iRowCount = dsDataSet.Tables.Count()
'how many tables are there
MessageBox.Show(iRowCount, "# of tables")
For Each dt In dsDataSet.Tables 'what does each table contain
MessageBox.Show(dt.TableName, " has " & dt.Rows.Count & " rows")
Next
-------------

 

frankm9639

Active member
Joined
Aug 24, 2006
Messages
25
Programming Experience
1-3
I have to clear the dataset table in the selectedindexchanged. But, if it doesn't initially exist, it bombs out on me when I try to clear it. Therefore, I do this check:
-------------
If Not dsDataSet.Tables("PLAN") Is Nothing Then
dsDataSet.Tables("PLAN").Clear()
End If
-------------

Thanks for your help, Gramps!
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Hard work, this data access, isnt it?


You can use the dataset designer to build 3 relates tables, fill them all with relevant data and let the relationships manage the combos. I would post a project but your VS2003 wouldnt be able to open my VS2005 project.. you can download the free version of VB2005 if you like.

In 2.0 (2005) you would/could do:

Make a dataset with 3 tables:

States
Towns
Companies

have one database table that has data like:

State, Town, Company
S1, T1, C1
S1, T1, C2
S1, T2, C1
S1, T2, C2
S2, T1, C1
S2, T1, C2
S2, T2, C1
S2, T2, C2


Fill your States datatable with:
SELECT DISTINCT state FROM table

Fill your Towns datatable with
SELECT DISTINCT state, town FROM table

Fill your Companies with:
SELECT DISTINCT town, company FROM table


Establish relationships between state-state and town-town


From the data sources window, expand States, States_Towns, and Towns_Companies.

Drop a combo on from each. Binding sources are created to manage the relationships (1.1 doesnt have binding sources, you would have to manage the relationships yourself in code, with GetChildRows, I suspect..)

Change the DropDownStyle to DropDownList for each. Remove the binding on .Text = (whatever it is bound to)

Make the States combo have a DataSource = StatesBindingSource, Towns combo DataSource = Towns BindingSource and Companies combo DataSource = Companies BindingSource.
Set the relevant display members on all combos.

Run the project.


This takes about 10 minutes, including creating the database table (the longest op for me because I dont have access so i must create it manually with SQL statements in VS2005) and not a line of code is written by me, other than the SQLs..

Here are some pictures, and an example project. You wont be able to open the project in 2003, but you can run the exe:
 

Attachments

  • Image1.png
    Image1.png
    113.6 KB · Views: 25
  • Image2.png
    Image2.png
    13.4 KB · Views: 24
  • CascadingComboExample.zip
    95.6 KB · Views: 26
Top Bottom