sql query not getting correct results from db...

[sandy123]

Hello.

I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.

String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID
WHERE Capacity = '" + reqCapacity + "' " + "
AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime)
AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";

This is my SQL string... what it is trying to do is:

find the room
where the capacity is the reqcapacity entered by user
and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity
and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values.
The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.

Not sure whether i should be using AND, or OR? or a combination.

Any ideas gratefully appreciated....

Sandy

[Krzemo]

I don't understand what U are trying to do....

but maybe that could help:

AND '" + newRoomEnd + "' NOT BETWEEN roomStartDateTime AND roomEndDateTime AND '" + newRoomStart + "' NOT BETWEEN roomStartDateTime AND roomEndDateTime
Anyway - better use stored procedure - that way U can pass params to it for ec\xecution (and avoid nasty sqlinjection security problem)