Filtering records

[lsdev]

I have a table called Skill that look like this:
SKILL(MainSkil, SubSkill)
primary key is both (MainSkill, SubSkill)
The data held looks like:
IT,Support
IT, IT Manager
HR, Assistant
HR, Manager

And a Job table like;
JOB(JobID, Title)

A job can have multiple skills so I have a JobSkill table:
JOBSKILL(JOBID, MainSkill, SubSkill)
primary key is (JobID, MainSkill, SubSkill)

Now I have set up a windows form in that I have a master-detail relationship set up and it all works ace. My problem is that in the grid view part (JOBSKILL) I want the a combo box with a look up to mainSkill and sub skill. This again works but I get duplicate records, so basically clicking on main skill gives me:
IT,
IT,
HR,
HR,

Also to add to this I am gettin all the subskills not just those for the main skill selected? Is there a way of using filters such that the combo boxes for main skill are just those that are unique/distinct, and then have the sub skill combo box populated with the sub skills that match the main skill?
Is there a way of using filters to do this?
Any help is much appreciated, and I hope this makes sense

Regards

 

[Jason_deBono]

HI,
I think you should make an other DataTable with the MainSkill with "Select MainSkill From Skill Group By MainSkill" and set the ComboBox of the MainSkill to it. (This Should show only IT and HR) Now OR filter the Other DataTable on the SubSkill OR with a Relationship between MainSkill DataTable to SubSkill DataTable

 

[lsdev]

Yeah I have been thinking about spliting the table but this would in effect mean maintaining more than one table, which I ideally do not want to do. I dont know of another way round this but surely there are methods in .net to fill data grid combo boxes with data from a query in some way? I just can't seem to find anything.

 

[Jason_deBono]

No, just make an other DataTable in the DataSet
You can also make it with Code

Dim queryString As String = _
"SELECT MainSkill From Skill Group By MainSkill"
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
queryString, connection)

Dim MainSkill As DataSet = New DataSet
adapter.Fill(MainSkill , "MainSkill")

**Add the Code to Set the Binding Properties of the ComboBox

 

[lsdev]

Ok ill give this a go thanks for your help

 

[lsdev]

Great I have now created a new data table for main skill and it works like a charm, thanks for your help. Now I just need to filter the combo box for sub skill so you can only select subskills for that particular mainskill. You mention using a filter? But this will change data (records in the data grid) already in place would it not?

 

[lsdev]

Right, I think I have come up with an idea for a soultion...
I have created another data table which has a column subskill, and I have a table adaptor which fills this table on a query as such:

SELECT SubSkill FROM Skill
WHERE MainSkill = :MSKILL

The idea is to get this table to be populated on changing/selecting mainskill in the datagrid view.... or something to this effect?! Does anyone know how to get the currently selected item/value in a column of a data grid? and even better how to get the value for the currently selected row?

Regards

 

[lsdev]

Im still unable to filter the sub skills in the subskill combo box. Any ideas?

 

[Jason_deBono]

Try to make 2 Data Tables (MainSkill, SubSkill) then relate these 2 table to JOBSKILL.
This is an Example.

Dim dsNorthwind1 As New NorthwindDataSet()

Dim CustomersOrders As New DataRelation("CustomersOrders", _
dsNorthwind1.Customers.Columns("CustomerID"), _
dsNorthwind1.Orders.Columns("CustomerID"))

dsNorthwind1.Relations.Add(CustomersOrders)

 

[lsdev]

Try to make 2 Data Tables (MainSkill, SubSkill) then relate these 2 table to JOBSKILL.
This is an Example.

Dim dsNorthwind1 As New NorthwindDataSet()

Dim CustomersOrders As New DataRelation("CustomersOrders", _
dsNorthwind1.Customers.Columns("CustomerID"), _
dsNorthwind1.Orders.Columns("CustomerID"))

dsNorthwind1.Relations.Add(CustomersOrders)

Not sure I understand what you are proposing? Could you elaborate please?

 

[lsdev]

Returing the selected value of a data grid cell

Is there a way to return the selected value of a data grid cell? My idea is to have the subskill combo source filtered by the value of the main skill next to it in the data grid. Would this work?

 

[cjard]

CLick DW2 in my signature and then read "displaying related data"

 

[lsdev]

CLick DW2 in my signature and then read "displaying related data"

Thanks ill give that a read

 

[lsdev]

CLick DW2 in my signature and then read "displaying related data"

I have looked at the walkthrough but don't understand how I can apply this to solve my issue. The relationship I have is as follows:

Job -< JobSkills >- Skill

Where job contains a JobID, title ....
so like:
1, Support Assistant role with db management

Where JobSkills contains JobID, MainSkill, SubSkill
1, IT, Support Assistant
1 IT, DBA
1, IT, Help Desk

Where Skill contains

MainSkill, SubSkill (pk is both)
IT, Support Assistant
IT, DBA
IT, Help Desk
HR, Manager
Admin, Receptionist

I have created a datatable that selects all distinct MainSkill so that the datagrid of JobSkills, now contains a dropdown for the mainskill selection. But I now want the subskill combo box to only contain those subskills that match the JobSkills.MainSkill?? I tried creating a datatable that: consists of

Select * Skill.Subskill
from skill, JobSkills
where Jobskills.mainSkill = skill.mainskill

But this caused an error on loading the form??

 

[cjard]

OK, briefly you'll have:

A datatable with MainSkill in (i'll call it main)
A datatable with mainskill/subskill in (i'll call it mainsub)
A datarelation between the main and the mainsub on ONLY the mainskill=mainskill columns

A combo bound to a mainbindingsource
Mainbindingsource bound to main
A combo bound to mainsubbindingsource
Mainsubbindingsource is bound to the datarelation object of mainbinsingsource


When you pick an item from main combo, it moves the mainbindingsource
This causes the datarelation to automatically filter and mainsubbindingsource shows only subs that apply to that main

This, with the walkthrough about displaying related data should help ?