IF in a database selection?

[chrismartz]

I have the following code connecting to my database:SELECT * FROM homeroom WHERE student_homeroomnum = " & request.querystring("id") & " OR srt_lab_room = '" & request.querystring("id") & "' ORDER BY student_lastname ASC", objDC, 0 How can I have it not select from student_homeroomnum if srt_lab_room = request.querystring("id")

[lmf232s]

im not sure if this will work. you might even have to swap the values

select * from homeroom
where srt_labA_room = " & request.querystring("id") & "
and student_homeroomnum not in
(
select * from homeroom
where student_homeroomnum = " & request.querystring("id") & "
)

[Bullschmidt]

Or how about this:

strSQL = "SELECT * FROM homeroom WHERE ((student_homeroomnum = " & request.querystring("id") & ") AND (srt_lab_room <> '" & request.querystring("id") & "')) OR (srt_lab_room = '" & request.querystring("id") & "') ORDER BY student_lastname ASC"

And in keeping with how you did things above I put the single quotes around the request.querystring("id") only for srt_lab_room. This assumes that in the underlying database the student_homeroomnum field is a number field and the srt_lab_room is a text field.

If both fields are actually number fields, then don't use the single quotes:

strSQL = "SELECT * FROM homeroom WHERE ((student_homeroomnum = " & request.querystring("id") & ") AND (srt_lab_room <> " & request.querystring("id") & ")) OR (srt_lab_room = " & request.querystring("id") & ") ORDER BY student_lastname ASC"

[chrismartz]

I have tried all these suggestions and nothing seems to be working. Any other ideas? I have the following and it shows if they are part of the srt_lab_room based on if the number in that field matches the querystring number.

SELECT * FROM homeroom WHERE student_homeroomnum = " & request.querystring("id") & " OR srt_lab_room = '" & request.querystring("id") & "' ORDER BY student_lastname ASC How can I get the kid to not show if they have something other than "none" for the srt_lab_room. So if their student_homeroomnum is 115 and the srt_lab_room is set to 1, they don't show up for the 115. Hopefully this makes sense. Hard to describe through text!

[chrismartz]

Maybe I should try to describe it more. What I have is a database with the each students homeroom teacher (student_homeroom). The teachers are able to assign srt labs for students. when they are assigned, srt_lab_rooms is set from "none" (which it starts out set) to whatever the srt room teacher's id number is. this is where i am having troubles. I need to have it select from student_homeroom, but if srt_lab_rooms is changed from none to something else they shouldn't show up in the list. but they should show up in the teacher that is srt_lab _rooms. Very complicated. anyone understand?

[buntine]

What about:

SELECT * FROM homeroom WHERE student_homeroomnum = " & request.querystring("id") & " AND srt_lab_rooms NOT NULL OR srt_lab_room = '" & request.querystring("id") & "' ORDER BY student_lastname ASC

Regards.

[buntine]

SELECT * FROM homeroom WHERE student_homeroomnum = " & request.querystring("id") & " AND srt_lab_rooms = 'none' OR srt_lab_room = '" & request.querystring("id") & "' ORDER BY student_lastname ASC

[chrismartz]

The last post by buntine has fixed the problem. the one with 'none' in it that is! This has brought about a new problem though. I allow the teachers to report absences if the student_homeroomnum is equal to the querystring id or if srt_lab_room is equal to the querystring id. When they are reported absent, dateof_absence is set to date(). I then go to a page that shows who was reported absent for only the teacher that submitted it this way...rs.open "SELECT * FROM homeroom WHERE dateof_absence = true And student_homeroomnum = " & request.querystring("id") & " OR

srt_lab_room = '" & request.querystring("id") & "' ORDER BY student_lastname ASC", objDC, 0
%>
<% If rs.eof Then %>
<p>Error...</p>
<% else %>
<ul>
<% Do While Not rs.EOF %>
<li><%= rs.fields("student_lastname") %>,&nbsp;<%= rs.fields("student_firstname") %></li>
<%rs.MoveNext
Loop
%> the problem is that the code i just showed outputs only those whose srt_lab_room is equal to request.querystring and they don't have date set in dateof_absence. I cannot figure this out. any more help would be greatly appreciated!

[chrismartz]

SELECT * FROM homeroom WHERE dateof_absence = true And student_homeroomnum = " & request.querystring("id") & " OR srt_lab_room = '" & request.querystring("id") & "' ORDER BY student_lastname ASC I'm guessing it is something to do with the Select above but I cannot figure why it would show those in srt_lab_room when i'm looking for those that are true for dateof_absence in srt_lab_room or student_homeroom when their number matches that of the querystring!

[russell]

if i understand correctly, u just need some parentheses

so the query looks like this (where xxx is the querystring value)

SELECT *
FROM HomeRoom
WHERE dateof_absence = true
AND (
HomeRoomNum = xxx
OR
srt_lab_room = xxx
)
ORDER BY student_lastname ASC
The ASP to generate this becomes
Dim hr
hr = Request.QueryString("id")
hr = Replace(hr, "'", "''") '' this is critical!!!

sql = "SELECT * FROM HomeRoom WHERE dateof_absence = true " &_
"AND (HomeRoomNum = " & hr & " " &_
"OR srt_lab_room = '" & hr & "') " &_
"ORDER BY student_lastname ASC"
Remember to ALWAYS check for single quotes and escape them. Failing to do so will expose your network to every attack imaginable. Imagine what would happen if I entered this into the query string:

yourPage.asp?id=1' or 1=1--

or if i did this:

yourPage.asp?id=1';delete * from HomeRoom--

[chrismartz]

Thanks russell