Problem with checking fields in form against database

Question

Post reply

Problem with checking fields in form against database

Sandy K

Grasshopper

Points: 18
More actions
February 6, 2006 at 10:10 am

#90674
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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32515 More actions · Answer 1

CREATE PROCEDURE dbo.USP_FIND_FREE_ROOM

(@ReqCapacity tinyint

,@StartDate datetime

,@EndDate datetime

,@NeedOHP bit=NULL /*0=not needed,1=needed,NULL=don't check*/

,@NeedAV bit=NULL

)

AS

SET NOCOUNT ON

SELECT DISTINCT RoomName FROM dbo.Room Room

INNER JOIN dbo.RoomCalendar RoomCalendar

ON Room.RoomID = RoomCalendar.RoomID

AND Room.Capacity = @ReqCapacity

AND NOT (@EndDate<= roomStartDateTime OR @StartDate >= roomEndDateTime)

AND (OHP = @NeedOHP OR @NeedOHP IS NULL)

AND (AV = @NeedAV OR @NeedAV IS NULL )

GO

/*to call from ASP*/

EXECUTE PROCEDURE dbo.USP_FIND_FREE_ROOM

@ReqCapacity =5

,@Startdate='2006/02/01'

,@Enddate='2006/02/20'

,@NeedOHP=1

,@NeedAV =NULL (optional)

Sandy K Grasshopper Points: 18 More actions · Answer 2

Hi, thanks for your reply. Im quite new to SQL so my questions are going to sound a bit silly!...

So i use a stored procedure to accept the input parameters... can these still come from things like the drop down list or pop up calendar to get the datetime etc???

So i call the stored procedure in my asp page, which executes it? sorry, bit confused on how it knows what the user has entered?

thanks

Jo Pattyn SSC-Dedicated Points: 32515 More actions · Answer 3

Sandy,

There are many reasons to use stored procedures instead of concatenating strings to create the command. One is performance (by caching query plans), second is security (see sql injection attacks), shielding the actual data tables,...

*how to call a stored procedure from asp

http://support.microsoft.com/kb/q164485/

http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

They explain how you can pass your parameters to the stored procedure.

Haven't done any asp myself but the second link seems to be more comprehensible.