Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

String within sql statement

hi, nice easy one for first thing mornday morning :)  

I need to add "JDC" as an exception in my Where section, what am I doing wrong?

cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT Surname, FirstName, EmployeeSkills.EmployeeId, SkillsName, SkillLevel, " & _
        "Convert(varchar(10), ExpiryDate, 103) as ExpiryDateOnly, CardColour, AccDescription FROM EmployeeSkills " & _
        "LEFT JOIN SkillLevel ON (SkillLevel.SkillLevelId = EmployeeSkills.SkillLevelId) " & _
        "LEFT JOIN SkillsType ON (SkillsType.SkillId = EmployeeSkills.SkillId) " & _
        "LEFT JOIN Employees ON (Employees.Employeeid = EmployeeSkills.EmployeeId) " & _
        "LEFT JOIN AccRoute ON (AccRoute.AccRouteId = EmployeeSkills.AccRouteId) " & _
        "Where EmployeeSkills.EmployeeId = EmployeeSkills.EmployeeId and EmployeeSkills.SkillId = EmployeeSkills.SkillId and trainingtypeName <>  " JDC " 
0
Andrew Parker
Asked:
Andrew Parker
  • 3
  • 2
1 Solution
 
cubixSoftwareCommented:
Try single quotes

       "Where EmployeeSkills.EmployeeId = EmployeeSkills.EmployeeId and EmployeeSkills.SkillId = EmployeeSkills.SkillId and trainingtypeName <>  ' JDC '"

0
 
cubixSoftwareCommented:
or

"Where EmployeeSkills.EmployeeId = EmployeeSkills.EmployeeId and EmployeeSkills.SkillId = EmployeeSkills.SkillId and trainingtypeName <>  'JDC'"

depending if you want to check for embedded spaces as well
0
 
Andrew ParkerIT Field Systems Delivery OfficerAuthor Commented:
ok that works, but gives me too many rows,

Tables are

EmployeeSkills = employeeid, SkillId
SkillsType = SkillId, SkillsName, TrainingTypeId
TrainingType = TrainingTypeId, TrainingTypeName

What I need is a listview of everyone without TrainingType in JDC

Code so far

"SELECT Surname, FirstName, EmployeeSkills.EmployeeId, SkillsName, SkillLevel, " & _
        "Convert(varchar(10), ExpiryDate, 103) as ExpiryDateOnly, CardColour, AccDescription, Trainingtypename FROM EmployeeSkills " & _
        "LEFT JOIN SkillLevel ON (SkillLevel.SkillLevelId = EmployeeSkills.SkillLevelId) " & _
        "LEFT JOIN SkillsType ON (SkillsType.SkillId = EmployeeSkills.SkillId) " & _
        "LEFT JOIN Employees ON (Employees.Employeeid = EmployeeSkills.EmployeeId) " & _
        "LEFT JOIN TrainingType ON (SkillsType.SkillId = EmployeeSkills.SkillId) " & _
        "LEFT JOIN AccRoute ON (AccRoute.AccRouteId = EmployeeSkills.AccRouteId) " & _
       "Where EmployeeSkills.EmployeeId = EmployeeSkills.EmployeeId and EmployeeSkills.SkillId = EmployeeSkills.SkillId and trainingtypeName <> 'JDC' Order by Surname"

There should only be one trainingType to a skill, but this seems to give me multiple entries?

Hopefully you can help, but it does help understand the problem from my end explaining to others, all help is gratefully received :)
0
 
cubixSoftwareCommented:
TRY

"LEFT JOIN TrainingType ON (TrainingType.TrainingTypeId = SkillsType.TrainingTypeId) " & _


you hadn't joined the training type table to anything so therefore you will get all records where description <> 'JDC' and not jus those linked to the skill type you have selected


HTH :)
0
 
Andrew ParkerIT Field Systems Delivery OfficerAuthor Commented:
thanks, i noticed that after I asked, it really does help your thinking when you type it out trying to explain in english what you need help with :)

Lots of points for you today cubix, cheers
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now