Using Combo Boxes in VB.NET

[vbamateur]

I have two Combo Boxes on my form. I sucessfully set the datasource for the first ComboBox using the below code :
"Dim strSQL As String = "Select Employer From Employer"
Dim DA As New SqlDataAdapter(strSQL, objConn)

Dim DS As New DataSet

DA.Fill(DS, "Employer")

Dim dt As New DataTable

dt.Columns.Add("Employer", GetType(System.String))

Dim drDSRow As DataRow

Dim drNewRow As DataRow

For Each drDSRow In DS.Tables("Employer").Rows()

drNewRow = dt.NewRow()

drNewRow("Employer") = drDSRow("Employer")

dt.Rows.Add(drNewRow)

Next

UpdateEmployeeCombo1.DropDownStyle = ComboBoxStyle.DropDownList

With UpdateEmployeeCombo1

.DataSource = dt

..DisplayMember = "Employer"

.ValueMember = "Employer"

.SelectedIndex = 0

End With"
--------------------------
This is done when the form is loaded and it works fine. The problem is I would like to set the datasource for my second ComboBox using the value select by in ComboBox1. That is, it displays a list of Employer Numbers and I would like to use the value chosen in the select statement when setting the datasource for the other. Im also unclear as to where to put whatever code I eventually come with. Any suggestions?

 

[jmcilhinney]

You may have a good reason for this but my guess is not. Why are you creating a new DataTable to bind to the ComboBox instead of using the one you have just filled. Also, are you saying that the Employer field is a number that identifies the employer or is it the name? I would suggest not giving a table and a column in that table the same name. I'd use column names like ID, Name, EmployerID and EmployerName. If the table is called Employer then Employer should describe the entire entity, not just one field. Also, what do you want to display in the other ComboBox and how exactly is it linked to the Employer table?

 

[vbamateur]

Thanks for your response. Sorry if my thread wasnt well written....Im new to forums. I have changed the name of my column so that it doesnt conflict with the entity name. The field Im selecting in the first ComboBox is the Employer ID. With this id I want my second ComboBox to display only the departments headed by this employer. This is stored in another another table. Hope Im making sense. Any suggestions?

 

[jmcilhinney]

You are making sense and I do have a suggestion. Retrieve your data into two DataTables, which can be stand-alone or part of a DataSet. First you should bind the Department DataTable to the appropriate ComboBox using the DataSource property, then bind the Employer DataTable to the other ComboBox. I would suggest assigning the Name column to the DisplayMember property and the ID column to the ValueMember property. That way, the user can select from a list of friendly names while your code can use the ID. Now, to relate the two, you would set the DefaultView.RowFilter property of the Category DataTable whenever the user selects an Employer:

Private Sub employerCombo_SelectedIndexChanged(...) Handles employerCombo.SelectedIndexChanged
	Me.categoryTable.DefaultView.RowFilter = "EmployerID = " & Me.employerCombo.SelectedValue.ToString()
End Sub

Basically, the data in a control bound to a DataTable reflects that tables DefaultView property, which is a DataView. DataViews are designed to expose filtered and sorted view of the contents of a DataTable without actually affecting the table itself. The RowFilter property essentially takes an SQL WHERE clause. The code above will expose only those rows in the Category table that have an EmployerID that corresponds to the currently selected Employer. For more info, I suggest you read the help topics for the DataTable.DefaultView and DataView.RowFilter properties.

 

[vbamateur]

I have the following code but it appears Im stuck in a loop when the form is called. Any suggestions:

[size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] UpdateEmployeeCombo1_SelectedIndexChanged([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] UpdateEmployeeCombo1.SelectedIndexChanged

 

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] strSQL1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select Department, Employer From Department"
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DA1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlDataAdapter(strSQL1, objConn)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DS1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataSet

DA1.Fill(DS1, "Department")

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Classdt [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataTable

Classdt.Columns.Add("Department", [/size][size=2][color=#0000ff]GetType[/color][/size][size=2](System.String))

Classdt.Columns.Add("Employer", [/size][size=2][color=#0000ff]GetType[/color][/size][size=2](System.String))

 

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] drDSRow1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] DataRow

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] drNewRow1 [/size][size=2][color=#0000ff]As[/color][/size][size=2] DataRow

[/size][size=2][color=#0000ff]For[/color][/size][size=2] [/size][size=2][color=#0000ff]Each[/color][/size][size=2] drDSRow1 [/size][size=2][color=#0000ff]In[/color][/size][size=2] DS1.Tables("Department").Rows()

drNewRow1 = Classdt.NewRow()

drNewRow1("Department") = drDSRow1("Class")

drNewRow1("Employer") = drDSRow1("Employer")

Classdt.Rows.Add(drNewRow1)

[/size][size=2][color=#0000ff]Next

[/color][/size][size=2][color=#008000] 

[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] a [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataView(Classdt)[/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] pr [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Integer
[/color][/size][size=2][/size][size=2][color=#008000]'[/color][/size][size=2]Classdt.DefaultView.RowFilter = "Employer = '" & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].UpdateEmployeeCombo1.SelectedValue.ToString() & " ' "

UpdateEmployeeCombo1.DropDownStyle = ComboBoxStyle.DropDownList

[/size][size=2][color=#0000ff]With[/color][/size][size=2] UpdateEmployeeCombo1

.DataSource = a

.DisplayMember = "Class"

.ValueMember = "Class"

.SelectedIndex = 0

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]With

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size]
[size=2][color=#0000ff][/color][/size] 
[size=2][color=#0000ff]

[/color][/size]

 

[jmcilhinney]

You are not doing what I suggested because you are retrieving new data from the database each time an Employer is selected. The idea is that you only retrieve the data once and then you only need that one line of code that I posted previously whenever an Employer is selected.