Wrong skilltype in listbox

Question

On page_load, in listBoxDatabase it shows both Programming skills and Database skill, i just want it to provide the "Database" skill.

This is my table in SQL:

This is my method that provides it on page_load:

    private void SetDatabaseSkillList(EMPLOYEE theEmpl)
    {
        KnowItCvdbEntities db = new KnowItCvdbEntities();

        if (_emp != null)
        {
            var databaseList = from p in db.EMPLOYEES_TECHNICAL_SKILLS.AsEnumerable()
                                join m in db.TECHNICAL_SKILLS.AsEnumerable() on p.technical_skill_id equals m.technical_skill_id
                                join t in db.TECHNICAL_SKILL_TYPE.AsEnumerable() on m.technical_skill_id equals t.technical_skill_id
                                join l in db.TECHNICAL_SKILL_LEVEL.AsEnumerable() on p.technical_skill_level_id equals l.technical_skill_level_id
                                where p.employee_id == theEmpl.employee_id
                                select new EmployeeTechnicalSkillInfo
                                { 
                                    EmployeeId = theEmpl.employee_id,
                                    TechnicalSkillId = m.technical_skill_id,
                                    SkillName = m.skill_name,
                                    TechnicalSkillTypeId = t.technical_skill_type_id,
                                    SkillType = t.skill_type,
                                    TechnicalSkillLevelId = l.technical_skill_level_id,
                                    SkillLevel = l.skill_level
                                };

            foreach (var vData in databaseList)
            {
                    ListBoxDatabases.Items.Add(vData.SkillName + " - " + vData.SkillLevel);                                        
            }
        }
        else
        {
            LabelPleaseRegister.Visible = true;
            LabelPleaseRegister.Text = "Please register your personal information";
            PanelRegisterCv.Visible = false;
            PanelRegisterPersonalInfo.Visible = false;
        }
    }

The EmployeeTechnicalSkillInfo class:

public class EmployeeTechnicalSkillInfo
{
    public int EmployeeId { get; set; }
    public int TechnicalSkillId { get; set; }
    public int TechnicalSkillLevelId { get; set; }
    public int TechnicalSkillTypeId { get; set; }
    public string SkillName { get; set; }
    public string SkillLevel { get; set; }
    public string SkillType { get; set; }
}

Save:

                        var mySkillType = new TECHNICAL_SKILL_TYPE
                                          {
                                              skill_type = LabelDatabaseRes.Text,
                                              technical_skill_type_id = new Random().Next()
                                          };

                    var mySkill = new TECHNICAL_SKILLS
                                      {
                                          skill_name = DropDownListDB.SelectedItem.Value,
                                          TECHNICAL_SKILL_TYPE = new EntityCollection<TECHNICAL_SKILL_TYPE> {mySkillType},
                                          technical_skill_type_id = mySkillType.technical_skill_type_id,
                                          technical_skill_id = new Random().Next()
                                      };

                    var myLevel = new TECHNICAL_SKILL_LEVEL
                                          {
                                              skill_level = RadioButtonListDatabase.SelectedItem.Value,
                                              technical_skill_level_id = new Random().Next()
                                          };

                    var empSkill = new EMPLOYEES_TECHNICAL_SKILLS
                                          {
                                              TECHNICAL_SKILLS = mySkill,
                                              technical_skill_id = mySkill.technical_skill_id,
                                              TECHNICAL_SKILL_LEVEL = myLevel,
                                              technical_skill_level_id = myLevel.technical_skill_level_id,
                                              employee_id = _emp.employee_id
                                          };

                    _emp.EMPLOYEES_TECHNICAL_SKILLS.Add(empSkill);
                    db.SaveChanges();

Answer 1

You need a flag in your DB to identify what type the skill is, are you doing this with skill_type? If not you could use a lookup table containing all your Skill Categories and then each record gets assigned a category. This may not work if Skills require to be in multiple categories.

Based on the tables above yeah that looks right..when you query the TECHNICAL_SKILL_TYPE you could use

SELECT *
FROM TECHNICAL_SKILL_TYPE
WHERE technical_skill_id = 1

and your TECHNICAL_SKILLS table would look like this

1      Database
2      Programming
3      UI Design
4      SharePoint Admin

Make sense?

HTH

S