Selecting to run a querey only if ID and Password match 1

[demchak]

I trying to set up a web site using ASP to run queries from a linked SQL datasource. I can get all of them to run right but I would like to have one query run only if the user# matches the password. I have a user# and password field in one of the tables but I can not figure out the syntax to get it to run only if they match.
The rest of query works fine, this is the end where I select the fields user# and password. The query works if I remove the last part and use just the W-number but not when I AND them together. I know this is a simple approach but the information is not critical is just a little sensitive for some employees.
Thanks for the help,

WHERE (tblEmployees.[W-Number]=%%wnumber%% 
   AND tblEmployees.[Password]=%%pass%%);

******this is the total statement***********

SELECT tblEmployees.[W-Number], 
[tblRollcall/Evaluation].SessionID, 
[tblScheduled Sessions].Date, 
tblSubjects.Subject, 
tblCourses.CourseName, 
tblEmployees.[Password]
FROM tblSubjects 
INNER JOIN (tblCourses 
   INNER JOIN (tblEmployees 
      INNER JOIN ([tblScheduled Sessions]
         INNER JOIN [tblRollcall/Evaluation] 
         ON [tblScheduled Sessions].SessionID = [tblRollcall/Evaluation].SessionID) 
      ON tblEmployees.[W-Number] = [tblRollcall/Evaluation].[W-Number])
   ON tblCourses.CourseID = [tblScheduled Sessions].CourseID)
 ON tblSubjects.SubjectID = tblCourses.SubjectID
WHERE (tblEmployees.[W-Number]=%%wnumber%% 
   AND tblEmployees.[Password]=%%pass%%);

 

[tlbroadbent]

You can create a stored procedure and pass the ID and password to the procedure which would return a record set or and error.

Create Procedure ShowEmployeeCourseRecs @ID int, @psw varchar(20) As

IF Exists (Select * From tblEmployees Where W-Number=@ID And [Password]=@psw

BEGIN

SELECT
tblEmployees.[W-Number],
[tblRollcall/Evaluation].SessionID,
[tblScheduled Sessions].Date,
tblSubjects.Subject,
tblCourses.CourseName,
tblEmployees.[Password]
FROM tblSubjects
INNER JOIN (tblCourses
INNER JOIN (tblEmployees
INNER JOIN ([tblScheduled Sessions]
INNER JOIN [tblRollcall/Evaluation]
ON [tblScheduled Sessions].SessionID = [tblRollcall/Evaluation].SessionID)
ON tblEmployees.[W-Number] = [tblRollcall/Evaluation].[W-Number])
ON tblCourses.CourseID = [tblScheduled Sessions].CourseID)
ON tblSubjects.SubjectID = tblCourses.SubjectID
WHERE tblEmployees.[W-Number]=@ID

END

ELSE

BEGIN

<what to do if employee has no priveleges>
PRINT 'You are not authorized to view that report.'

END

To execute the SP run EXEC ShowEmployeeCourseRecs 1234,'mypassword' Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views.&quot; - Abraham Lincoln