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 im glad it's not just me then, well the idea of having two single combo boxes and an add button seems like the only viable "usable" alternative. Then I gather I could just set the grid in a read and delete only mode?? Also is there any code specifically needed for this "Add" button to get it to add the values in the two combo boxes? or can I just use an insert on the table adaptor and then call some sort of fill to redisplay the new skill?
Although this seems like the only option at the moment if I can get the grid combo's to work in the way I need it would be great.

Just to add is there not a way to do some sort of FOR LOOP to get all the values to display? But I guess this concept would only work for the first job record....

 

[lsdev]

Just to add thanks for posting my query on the msdn forum . Hopefully I can find a soultion, as im sure this function is not only required by myself, as it's quite a common function (Or so it seems to me)

 

[cjard]

Then I gather I could just set the grid in a read and delete only mode??

Yeah.. i'd use the grid just for view purposes.. have another button that just deletes the current row, and the user changes the current row by clicking the grid..

Also is there any code specifically needed for this "Add" button to get it to add the values in the two combo boxes?

Same as you would add any row to any other table:

MyXXXDataTable.AddMyXXXRow("JobIDHere", mainCombo.SelectedValue, childCOmbo.SelectedValue)

or can I just use an insert on the table adaptor and then call some sort of fill to redisplay the new skill?

Nooo no no no.. we wouldnt uplaod a row to ad atabase and then download it again jsut to get it to show.. We would add add add lots of rows locally and then save them once later:

MyXXXDataTable.AddMyXXXRow("JobIDHere", mainCombo.SelectedValue, childCombo.SelectedValue)
'it shows in the grid. choose anotehr set, and add aggain and again

Me.Validate() 'the form
MyXXXBindingSource.EndEdit()
MyXXXTableAdapter.Update(MyXXXDataTable) ' save cahnges to db

Although this seems like the only option at the moment if I can get the grid combo's to work in the way I need it would be great.

I think you'll struggle, tbh.. its a lot of dicking around for not much value added. You'd be better making your own DataGridView column type that uses one combo for display and another combo for editing.. the display combo has access to the full child list, the editing combo only accesses the filtered list from the relation..

Just to add is there not a way to do some sort of FOR LOOP to get all the values to display?

Dont really understand this question.

 

[lsdev]

I have replied to the thread on the MSDN forum, just to reiterate the situation. I will respond to futher post's on this issue here and on the post in your link. I am not very experienced in vb.net as a front end application, but am a little from a general programming approach. So would be very greatful if you could help in explaining or getting the issue across to fellow developer's when or if they suggest any ideas.

Regards

 

[lsdev]

With regards to the looping, ignore that comment. I was thinking about having some kinda of loop that for each record viewed would filter each row thus displaying the appropriate subskill, alas this will not work.

Thanks again for your input ill get to work on implementing a single set of combo boxes and a readonly data grid.

Thanks again

 

[lsdev]

Yeah.. i'd use the grid just for view purposes.. have another button that just deletes the current row, and the user changes the current row by clicking the grid..



Same as you would add any row to any other table:

MyXXXDataTable.AddMyXXXRow("JobIDHere", mainCombo.SelectedValue, childCOmbo.SelectedValue)


Nooo no no no.. we wouldnt uplaod a row to ad atabase and then download it again jsut to get it to show.. We would add add add lots of rows locally and then save them once later:

MyXXXDataTable.AddMyXXXRow("JobIDHere", mainCombo.SelectedValue, childCombo.SelectedValue)
'it shows in the grid. choose anotehr set, and add aggain and again

Can you please explain how I would add a row to my data grid without having to pass it to the database and then retreiving the values back again?

I have tried:

Me.CandidatePlacementDataSet.JOBSKILLLINK.AddJOBSKILLLINKRow(cboxMainSkill.Text, cboxSubSkill.Text, txtJobID.Text)

but it is saying that the "txtJobID" is of the wrong type and should be of type row??

 

[cjard]

Me.CandidatePlacementDataSet.JOBSKILLLINK.AddJOBSKILLLINKRow(cboxMainSkill.Text, cboxSubSkill.Text, txtJobID.Text)

but it is saying that the "txtJobID" is of the wrong type and should be of type row??

Uhm.. i'd have expected it to be saying that the cboxMainSkill.Text is of the wrong type and expecting a row.. This message occurs because youre adding a row to a CHILD datatable, and you must specify which row is its parent.. this way the system can automatically set the necessary fields to ensure the relation works.

Dim p as ParentRow = MyParentTable.AddParentRow(123456, "Parent")
MyChildDataTable.AddChildRow(p, "Child") 'child will auto set ID column to 123456

 

[lsdev]

Uhm.. i'd have expected it to be saying that the cboxMainSkill.Text is of the wrong type and expecting a row.. This message occurs because youre adding a row to a CHILD datatable, and you must specify which row is its parent.. this way the system can automatically set the necessary fields to ensure the relation works.

Dim p as ParentRow = MyParentTable.AddParentRow(123456, "Parent")
MyChildDataTable.AddChildRow(p, "Child") 'child will auto set ID column to 123456

Just to update this thread I have now got my application to add a single row to the data table from two combo boxes using an add command button. This seems to be best way to tackle the problem. I have now set the data grid to read-only, but have it set to allow rows to be removed. Thanks for all the input received, I would hope that newer versions of .net will eventually have this feature added

 

[cjard]

Just to update this thread I have now got my application to add a single row to the data table from two combo boxes using an add command button. This seems to be best way to tackle the problem. I have now set the data grid to read-only, but have it set to allow rows to be removed. Thanks for all the input received, I would hope that newer versions of .net will eventually have this feature added

It's not really a case of "new version of .net" - it's about using a combo to both edit and decode a value it finds in a cell based on a shifting list underneath. You could do it in your version of .net but its a lot of faff. If you can think of a logical sensible solution to the problem, youre free to implement it - it's more of a "too hard" than a "bug"