Jacob Latham Jacob Latham - 6 months ago 73
Vb.net Question

Filter databound combobox based on value in another column from the same dataset

I am attempting to create a process scheduler as a project in school this summer and am quite new to using VB. Currently I have a combobox bound to the "EmployeeBindingSource" which holds information for every single employee in a company. Each employee is assigned a skill level for each work station in the building. What I would like to do is if an employee is listed as having "0" skill in say, the assembly station, when selecting an employee to assign to the assembly station, that particular employee will not appear in the combobox.

From doing some research I believe that rather than binding the combobox to the data source in the designer, I will have to assign values to the each combo box in the code on a form load.

This is the access table that holds all the employee information

In the table we can see that Steve has a skill level of "0" in the AS_Level category (assembly).

However, here we can see that he still appears as an option for the assembly area when creating a new project

Currently all the data binding happens in the designer for each combo box and therefore no code has been written for the data binding. At the moment each combo box is bound to the "Full_Name" column in the access table.

Again, I am quite new to VB so I apologize if this is too vague. Let me know if I can give any more helpful information. Thank you all in advance.

After adding the code suggested here is what I have

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
datafile = "C:\Users\Jacob\Desktop\Halton\HaltonProject.accdb"
connString = provider & datafile
myConnection.ConnectionString = connString
myConnection.Open()

Dim str As String
str = "SELECT * FROM [Employee] WHERE (SP_Level <> '" & 0 & "')"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
Dim dr As OleDbDataReader = cmd.ExecuteReader

Dim userfound As Boolean = False
Dim Full_Name As String

While dr.Read
userfound = True
Full_Name = dr("Full_Name").ToString
SP_Emp1.Items.Add(Full_Name)
SP_Emp2.Items.Add(Full_Name)
SP_Emp3.Items.Add(Full_Name)
SP_Emp4.Items.Add(Full_Name)
End While

myConnection.Close()


Works completely now!

Answer

Based on your comments and post, I assume, the checkboxes on the left enable/disable the comboboxes on that row, and since you're binding them individually if you change your query so it looks like this:

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Employee] WHERE AS_Level <> 0 , myConnection) Dim dr As OleDbDataReader = cmd.ExecuteReader

Note that the where clause will vary depending on your needs, so it could be WHERE AS_Level <> 0 or WHERE SP_Level <> 0 and so on.

I have never used an Ole database so I'm not quite sure about the syntax, hope this helps.