Question SQL query from WinForm input

dfenton21

Member
Joined
Apr 26, 2011
Messages
20
Programming Experience
Beginner
I'm learning VB.NET and I am developing an application with a WinForms front end and an Access back end.

In the SQL below, I have 4 SELECT subqueries. Each query checks if a person attended a training session, and returns TRUE or FALSE. Obviously, each subquery will be displayed as a column in a DGV.

As you can see, I've hardcoded that I want to get data for 4 types of training (Selling Skills, Manual Handling, EPOS and Induction), i.e. 4 columns. However, I need the user be to able to select the training types they want data for. I'll use a multiselect list box. So, if the user only selects Induction and EPOS, the SQL will only need 2 SELECT subqueries.

How do I accomplish this? I could create the SQL using loops and string concatenation, but I doubt that is best practice. I have done SQL queries from WinForm inputs before using parameters, but I can't see how I could apply that methodology here.

Any advice would be greatly appreciated.
Thanks

VB.NET:
[COLOR=Black]
[COLOR=Blue]SELECT[/COLOR]
Employees.StaffID,
Employees.StaffName,
[COLOR=Blue]DATEDIFF[/COLOR]([COLOR=#a31515]'d'[/COLOR], Employees.StaffStartDate, now()) \ 7 [COLOR=Blue]AS[/COLOR] ServiceLength,
Areas.AreaDescription,
 
IIF(([COLOR=Blue]SELECT[/COLOR] SessionDate [COLOR=Blue]FROM[/COLOR] Sessions [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Bookings [COLOR=Blue]ON[/COLOR] Sessions.SessionID = Bookings.SessionID
[COLOR=Blue]WHERE[/COLOR] Bookings.StaffID = Employees.StaffID [COLOR=Blue]AND[/COLOR] Sessions.SessionType = [COLOR=#a31515]'Selling Skills'[/COLOR] [COLOR=Blue]AND[/COLOR] Bookings.Attendence = [COLOR=#a31515]'Attended'[/COLOR]) <> [COLOR=Blue]Null[/COLOR], [COLOR=Blue]TRUE[/COLOR], [COLOR=Blue]FALSE[/COLOR]) [COLOR=Blue]AS[/COLOR] SellingSkillsAttendence,
 
IIF(([COLOR=Blue]SELECT[/COLOR] SessionDate [COLOR=Blue]FROM[/COLOR] Sessions [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Bookings [COLOR=Blue]ON[/COLOR] Sessions.SessionID = Bookings.SessionID
[COLOR=Blue]WHERE[/COLOR] Bookings.StaffID = Employees.StaffID [COLOR=Blue]AND[/COLOR] Sessions.SessionType = [COLOR=#a31515]'Manual Handling'[/COLOR] [COLOR=Blue]AND[/COLOR] Bookings.Attendence = [COLOR=#a31515]'Attended'[/COLOR]) <> [COLOR=Blue]Null[/COLOR], [COLOR=Blue]TRUE[/COLOR], [COLOR=Blue]FALSE[/COLOR]) [COLOR=Blue]AS[/COLOR] ManualHandlingAttendence,
 
IIF(([COLOR=Blue]SELECT[/COLOR] SessionDate [COLOR=Blue]FROM[/COLOR] Sessions [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Bookings [COLOR=Blue]ON[/COLOR] Sessions.SessionID = Bookings.SessionID
[COLOR=Blue]WHERE[/COLOR] Bookings.StaffID = Employees.StaffID [COLOR=Blue]AND[/COLOR] Sessions.SessionType = [COLOR=#a31515]'EPOS'[/COLOR] [COLOR=Blue]AND[/COLOR] Bookings.Attendence = [COLOR=#a31515]'Attended'[/COLOR]) <> [COLOR=Blue]Null[/COLOR], [COLOR=Blue]TRUE[/COLOR], [COLOR=Blue]FALSE[/COLOR]) [COLOR=Blue]AS[/COLOR] EPOSAttendence,
 
IIF(([COLOR=Blue]SELECT[/COLOR] SessionDate [COLOR=Blue]FROM[/COLOR] Sessions [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Bookings [COLOR=Blue]ON[/COLOR] Sessions.SessionID = Bookings.SessionID
[COLOR=Blue]WHERE[/COLOR] Bookings.StaffID = Employees.StaffID [COLOR=Blue]AND[/COLOR] (Sessions.SessionType = [COLOR=#a31515]'One Day Induction'[/COLOR] [COLOR=Blue]OR[/COLOR] Sessions.SessionType = [COLOR=#a31515]'Induction - Second Day'[/COLOR] [COLOR=Blue]OR[/COLOR] Sessions.SessionType = [COLOR=#a31515]'Induction - First Day'[/COLOR]) [COLOR=Blue]AND[/COLOR] Bookings.Attendence = [COLOR=#a31515]'Attended'[/COLOR]) <> [COLOR=Blue]Null[/COLOR], [COLOR=Blue]TRUE[/COLOR], [COLOR=Blue]FALSE[/COLOR]) [COLOR=Blue]AS[/COLOR] InductionAttendence,
 
IIF(([COLOR=Magenta]ABS[/COLOR](SellingSkillsAttendence) + [COLOR=Magenta]ABS[/COLOR](ManualHandlingAttendence) + [COLOR=Magenta]ABS[/COLOR](EPOSAttendence) + [COLOR=Magenta]ABS[/COLOR](InductionAttendence)) = 4, [COLOR=Blue]TRUE[/COLOR], [COLOR=Blue]FALSE[/COLOR]) [COLOR=Blue]AS[/COLOR] InductionProgrammeAttendence
 
[COLOR=Blue]FROM[/COLOR] ((Employees [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Departments [COLOR=Blue]ON[/COLOR] Employees.StaffDept = Departments.DeptID) [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Bookings [COLOR=Blue]ON[/COLOR] Employees.StaffID = Bookings.StaffID) [COLOR=Blue]LEFT[/COLOR] [COLOR=Blue]JOIN[/COLOR] Areas [COLOR=Blue]ON[/COLOR] Departments.DeptArea = Areas.ID
[/COLOR]
 
Last edited:
Back
Top