5

I have this. "Detail all films shown by the club between any two given dates, inputted by the user. For example a club member must be able to input a start date and an end date for the parameters for the query"

Now, how would I go about doing the user input? Only way I can think of would to be using php or something with a html form getting the values and then submitting them as variables in the query. However, that's not what is needed. So, how is this done so the query would ask for values? Or can't you?

My query so far looks like so.

SELECT film.title, film.desc, show.sdate, show.fdate
FROM film
INNER JOIN show
ON film.ID=show.filmID
WHERE sdate = '&userstart' AND fdate = '&userend'

How do I go about with the user input? Also, is the query correct? I have no way of testing, I only have a design not an implementation.

Thanks a lot

Edit: Using Windows system, MS SQL Server.

Improve this question
7
  • 1
    you cannot prompt from sql to user for inputs if you are using web applications. Your web application should handle all user inputs, check for sql injections and send values to sql query as parameter. Commented Aug 15, 2012 at 18:26
  • 2
    Depends on your SQL DBMS. Some (like Oracle) have their own UI facilities, others (like MS SQL Server) do not and require you to add some client code such as c#, php, etc. Commented Aug 15, 2012 at 18:27
  • yes that is oracle forms but they are not truly sql prompting for user inputs, you still need to design UI for that. Commented Aug 15, 2012 at 18:28
  • @rs. Thanks, that was what I was thinking. So there is not a way to get user input from the query? Also, apart from that how does the query itself look? Commented Aug 15, 2012 at 18:28
  • 1
    I think you need to clarify with your instructor as to what they're looking for. It could be that they just want you to write a stored procedure that accepts two parameters. Commented Aug 15, 2012 at 18:46

3 Answers 3

Reset to default
3

Here's the code for a stored procedure:

CREATE PROCEDURE SomeName(@UserStart DATETIME, @UserEnd DATETIME) 
AS BEGIN

SELECT somestuff
FROM sometable
WHERE somedate BETWEEN @UserStart AND @UserEnd

END
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

So, would I be able to use it like such. CREATE PROCEDURE BetweenDates(@UserStart DATETIME, @UserEnd DATETIME) AS BEGIN SELECT film.title, film.desc, show.sdate, show.fdate FROM film INNER JOIN show ON film.ID=show.filmID WHERE sdate <= @UserStart AND fdate >= @UserEnd
Like so? CREATE PROCEDURE BetweenDates(@UserStart DATETIME, @UserEnd DATETIME) AS BEGIN SELECT film.title, film.desc, show.sdate, show.fdate FROM film INNER JOIN show ON film.ID=show.filmID WHERE sdate <= @UserStart AND fdate >= @UserEnd END Thanks again
1

@Kyle93 - Looking at your WHERE Clause:

WHERE sdate = '&userstart' AND fdate = '&userend'

This will only get you Films that had a sdate(Start Date?) equal to the date value entered. You might want to use Greater than, Less Than operators....

Such as:

WHERE sdate <= '&userend' AND fdate >= '&userstart'

(Note comparing sdate to UserEnd Date to make sure Film Showing started EARLIER than the UserEnd Date...etc)

This way - when a show starts OR ends within the date range - it will be selected.

Improve this answer

1 Comment

Thanks! I completely forgot about doing between.
-2

Are you familiar at all with LINQ? It's a way to make db calls from c#.

-- I would handle this with HTML as you thought, and use Javascript/Ajax to reference c# code that uses LINQ for db calls. Might be a bit complicated however, if you're not familiar with much of that. I can recommend good tutorials if interested tho.

Improve this answer

3 Comments

Oh just saw your comments above. Bleh. Not enough info originally. I prefer Microsoft platforms :P
@jos No I am not. However, as I don't need to implement it you don't have to. However if you have some to hand I would not say no to learning a bit more about other ways to do things :)
It's very misleading to describe LINQ as "a way to make db calls from c#". LINQ provides "general-purpose query facilities". 'LINQ to SQL' translates between LINQ and SQL databases – maybe that's what you were thinking of or referring to as "LINQ". But LINQ isn't needed to access a DB with C# – using the .NET System.Data or System.Data.SqlClient namespace objects would probably be the simplest way to do so.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.