Geek Answers Handbook

SQL - user input to the query

I have it. "Details of all films shown by the club between any two dates entered by the user. For example, a member of the club should be able to enter a start date and an end date for query parameters."

Now, how will I do user input? The only way I can think of is to use php or something with an html form, get the values, and then pass them as variables in the request. However, this is not what you need. So how to do this so that the query asks for values? Or can't you?

My request looks like this.

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 work with user input? Is the query true also? I do not have the ability to test, I have only a design, not an implementation.

thanks a lot

Edit: Using Windows MS SQL Server.

+4
source share
3 answers

Here's the stored procedure code:

 CREATE PROCEDURE SomeName(@UserStart DATETIME, @UserEnd DATETIME) AS BEGIN SELECT somestuff FROM sometable WHERE somedate BETWEEN @UserStart AND @UserEnd END
+3
source

@ Kyle93 - Looking at your WHERE clause:

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

This will only give you movies that had sdate (Start date?) Equal to the entered date value. You might want to use larger than less operators ...

For instance:

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

(Note: comparing sdate with UserEnd Date to make sure the movie started EARLIER, than UserEnd date ... etc.)

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

+1
source

Do you know LINQ? This is a way to make db calls from C #.

- I would handle this using HTML, as you thought, and use Javascript / Ajax to link to C # code that uses LINQ for db calls. Maybe a little harder if you are not familiar with this. I can recommend good study guides if you are interested.

-2
source

More articles:


All Articles