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

 

[lsdev]

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 ?

Thanks for that I will give it that another go. Thanks again

 

[lsdev]

Right I'm sorry if I'm not totally understanding this. I have read the walkthrough (which I used to create my Master detail form) but I don't see it's exact relavence to this particular problem.
I have created a datatable called "MainSkill" which has a query that gets the distinct mainskills from the Skill datatable. I have now created a datatable "SubSkill" which has both mainskill and subskill columns. I have a relation between them with the parent set to the "MainSkill" datatable and the MainSkill column and the child table set to the "SubSkill" table and the MainSkill column. I have set the binding sources as you suggest but the bit that confuses me is:
"...a combo bound to mainsubbindingsource
Mainsubbindingsource is bound to the datarelation object of mainbindingsource"
What do you mean by this I really don't understand. I understand the process in principle but not how to actually inplement it, I have spent hours reading up on links and files given to me by others but all seems to either not touch what I need to do or it is confusing me with abundance of code.
Just to add I have found these videos very useful so if there are any for explaing something to help me tackle this problem that would be great:

http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx

 

[cjard]

In the displaying related data article it mentions that because a datarelationexists between the two datatables there will be two renditions in the DataSources window

To illustrate this, open your datasources window and expand EVERY NODE in the tree (no really.. do it!)

Now, you should be able to see TWO subskill representations, one at the main level and one a child of mainskill.

If you drag the top level one onto the form, you get:

A datagrid --> bound to a bindingsource --> bound to the datatable



If you drag the leaf node one (under main) onto the form you get:

a datagrid --> bound to a binding source called subskill bindingsource --> bound to a DATARELATION which is exposed on the mainskill bindingsource


youll have to click on the bindingsources and read the DataSource an DataMember properties


I know youre binding combos here but ht eimportant bit of thje link is the bindsource --> otherbindsource rather than bindsource --> table
-

now the cool thing is in the latter case, whatever the main bindingsource is looking at, the child binding source will filter to only show child records that have that main skill that is chosen. note that both tables still have to be filled with data.. choosing a main skill wont automatically make the child pull that data from the db. If there are thousands of child skills then pulling them as they are chosen (and just not clearing the table) is better.. but if there are only a few hundred, pull all the main and all the child skills and let the datarelation do the filtering

 

[lsdev]

Thanks for taking the time to explain this to me, I now understand what you were getting at. I have implemented the combo boxes as you pointed out, using the data relation and it filters great, so thanks alot for pointing me in the right direction
I do have a slight problem with the way it is working though.....
Basically after firstly running the form I got a "Data error" which I solved by simply put " ' nothing " in the on dataerror method of the data grid.... Now I have pin pointed this problem to be something to do with the filter filtering the wrong skills on-loading the form?? I don't really understand why but basically when the form loads the first record to display only displays it's mainskills in the data grid not its subskills, I have pin pointed this to be because when I click on the subskill combo this skills displayed are not of the IT main skill but of the Admin main skill.... which just so happens to be the main skill of the next record??
It's like the JobSkillLink table values are being overwritten? But when I click the MainSkill drop down for this first record (not even reselecting the mainskill) the correct subskill is displayed?!

I hope I have explained this adequately, any Ideas why this is happening?

 

[cjard]

I cant quite understand what you mean, sorry. Can you go to here:

http://www.microsoft.com/windows/windowsmedia/9series/encoder/default.aspx

download WME9 and run it
Choose screen capture
Capture a region of the screen, 800x600 at low quality
Start recording and record reproducing the issue
Also include some shots of the code in the form load, and the properties of the combos and bindsources.. anything I can look at to understand what is going on

Or, you can request Remote Assistance from me in messenger and I can see you do it live.. the only problem with this is that I'm rarely on msn due to work/personal time

 

[cjard]

or you can upload your project in a passworded zip file and PM me the passowrd. I can look at your project but please:

remove teh BIN and OBJ folders from the zip
dont use custom controls I dont have
include any database file i'll need, bearing in mind that I dont have SQLServer or Oracle installed locally (so make an access database, or just remvoe the DB entirely and add some lines of code that will manually add valid data to the dataset)

 

[lsdev]

Ok well I will either try to get a very low res. video of what is happening or Ill get some screen shots up, thanks for taking your time to look into this

 

[cjard]

forgot to say also - zip the video, it gets smaller and it is allowed for upload.. .wmv is banned here

 

[lsdev]

forgot to say also - zip the video, it gets smaller and it is allowed for upload.. .wmv is banned here

^ Ill keep that in mind thanks.

Just in case this clears up the problem I am having I have found something on one of the MSDN posts thats sounds exactly the same as my issue. I don't understand the underlying code but perhaps this will give you a better understanding of my problem:

http://forums.microsoft.com/msdn/showpost.aspx?postid=60076&siteid=1

 

[lsdev]

I have attached some screen shots, I created a job with skills from two MainSkill types (Admin, IT) as you can see on loading only the first subskil is displayed and the filter for the other skills are fomr Admin not IT

 

[lsdev]

some more screen shots. I could not get the video capture to work as on reply is was just grey screening?

 

[cjard]

Err.. You have one grid and you are hoping to make it such that the grid has 2 columns of type combobox, right? I thought these combos were on the form.. Not a problem, i dont think, but the setup is slightlydifferent

I dont know what MainSkillSubSkillBindingSource is there for.. why is the child BS not just bound to the datarelation on the main BS?

To use the combos in a grid isnt a problem, you just have to set the column type to combo, and the property names are a little different.
Whatever table the grid is bound to (ultiamtely) will store the value from the valuemember of the combo. Meanwhile the combo is bound as a datasource, to the relevant skill table (main->mainBS->mainDT) and the display and value members to something relevant in that datasource. Whatever is found in the Value of the combo box will be written into the table the grid is bound to, into the column denoted by DataPropertyName, on the current row that the grid is looking at

Here is some pseudo code. Arrange your program to be this way in the designer:

Grid.DataSource = myJobsTableBindingSource
myJobsTableBindingSource.DataSource = myDataSet.MyJobsDataTable
Grid.Columns(0).Type = DataGridViewComboBoxColumn
Grid.Columns(0).DataSource = MainSkillBindingSource
Grid.Columns(0).DisplayMember = "Skill Name"
Grid.Columns(0).ValueMember = "Skill ID"
Grid.Columns(0).DataPropertyname = "JobSkillID" 'myJobsDataTable has a column called JobSkillID

Grid.Columns(1).Type = DataGridViewCombo..
Grid.Columns(1).DataSource = ChildSkillBindingSource
ChildSkillBindingSource.DataSource = MainSkillBindingSource
ChildSkillBindingSource.DataMember = "Main_to_Child_DataRelation"
Grid.Columns(1).DisplayMember = "SubSkill Name"
Grid.Columns(1).ValueMember = "SubSkill ID"
Grid.Columns(1).DataPropertyName = "JobSubSkillID" ' myjobsDataTable has a column called JobSubSKillID


So basically we have a main and a sub combo who take their data from one list (and changing the main list position causes the child list position to change) and write their values into another table.

Hope I uderstand you correctly now

 

[lsdev]

Right I'm not too sure why the MainSkillSubSkillBindingSource is there I have now got rid of it and have checked all the properties to those above (which is pretty much how I had mine) and it still seems to be giving me the same out put as that shown in "Img7"?! It's like the filtering is clearing the other skills. It just seems like because the filter is filtering jobs for admin the values are not there to show the IT skills?!

 

[cjard]

I found out why MainSubBindingSource exists.. DataGridViewCombo doesnt have a DataMember property and cannot reference a DataRelation directly, so a BS must be created that references the relation exposed by Main, and it is called MainSubBindingSource.. Its essential in this context. I'm doing you an example project now

 

[cjard]

I can see the problem now, and I think youre going to have to go about this in a different way. The problem comes when trying to use the combo as an editor and a view on the child. I recognise the flaw, but I'm not sure what can be done about it, so I have asked some other brains:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2837567&SiteID=1

One of my solutions would be:

Separate the model, view and controller properly. Have the grid perform jsut a view of the data, not to edit it.. The grid combos will be bound to the tables directly and therefore have all values available. Two other combos on the form, with a big ADD button next to them would work relationally. When Add is clicked, a new row is added to the jobs table taking all current values.

I dont think it will be easy here to have the combo perform as decoder of ID -> name and editor of table, and navigator (nav is implicit, and a nuisance sometimes. There is a logical disconnect in a combo in that its not possible to use it to nav a BS position and also edit a value; either you pick an entry fromt he lsit and it moves the BS to that entry, OR it edits the current entry to that chosen value. You cannot do both)