November 8, 2008 at 11:25 pm
I am using this code to produce a report of all appointments for tests on a given day for an instructor but It brings back no data and there are 2 rows that should be coming back. It oly outputs the field name with now errors when I enter the staff id and the date. Any advice?
SELECT [Lesson_Schedule].[Lesson_Date], [Lesson_Time].[Lesson_Time], [Student].[Student_Id], [Student].[First_Name], [Student].[Last_Name], [Lesson_Type].[Duration_Time]
FROM Student INNER JOIN (Staff INNER JOIN (Lesson_Time INNER JOIN (((Booking INNER JOIN Session_Information ON [Booking].[Booking_Id]=[Session_Information].[Booking_Id]) INNER JOIN Lesson_Schedule ON [Session_Information].[Lesson_TypeId]=[Lesson_Schedule].[Lesson_TypeId]) INNER JOIN Lesson_Type ON [Session_Information].[Lesson_TypeId]=[Lesson_Type].[Lesson_TypeId]) ON [Lesson_Time].[LessonTime_Id]=[Booking].[LessonTime_Id]) ON [Staff].[Staff_Id]=[Booking].[Staff_Id]) ON [Student].[Student_Id]=[Booking].[Student_Id]
WHERE [Staff].[Staff_Id]=[Enter Staff ID] And [Lesson_Schedule].[Lesson_Date] Like "*" & [Enter the Date] & "*";
November 9, 2008 at 1:35 am
cindy_sinath (11/8/2008)
WHERE [Staff].[Staff_Id]=[Enter Staff ID] And [Lesson_Schedule].[Lesson_Date] Like "*" & [Enter the Date] & "*";
What's the intention with this line? SQL, unlike Access, doesn't prompt for data entry. This is saying that the Staff ID must be equal to a column whose name is "Enter Staff ID" and same with the date.
What are you trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2008 at 4:03 am
I was trying to pass the Staff_Id and the Lesson_Date as a parameter to get all the appointments for that instructor for that day.
November 9, 2008 at 12:08 pm
For parameters it'll look more like this.
SELECT [Lesson_Schedule].[Lesson_Date], [Lesson_Time].[Lesson_Time], [Student].[Student_Id],
[Student].[First_Name], [Student].[Last_Name], [Lesson_Type].[Duration_Time]
FROM Student
INNER JOIN Booking ON [Student].[Student_Id]=[Booking].[Student_Id]
INNER JOIN Staff ON [Staff].[Staff_Id]=[Booking].[Staff_Id]
INNER JOIN Lesson_Time ON [Lesson_Time].[LessonTime_Id]=[Booking].[LessonTime_Id]
INNER JOIN Session_Information ON [Booking].[Booking_Id]=[Session_Information].[Booking_Id]
INNER JOIN Lesson_Schedule ON [Session_Information].[Lesson_TypeId]=[Lesson_Schedule].[Lesson_TypeId]
INNER JOIN Lesson_Type ON [Session_Information].[Lesson_TypeId]=[Lesson_Type].[Lesson_TypeId]
WHERE [Staff].[Staff_Id]=@StaffID And [Lesson_Schedule].[Lesson_Date] = @LessonDate;
If it's in a stored proc, then StaffID and LessonDate will be parameters to the proc. If this is adhoc from some front end, then they will be parameters added to the ADO command object.
What's the idea with the like on the date?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply