Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 User input with sql...

Author  Topic 

leighrogers
Starting Member

11 Posts

Posted - 2006-11-01 : 07:24:01
Afternoon...
I have a database for a pretend dvd hire company and need to create a query that uses user input as part of the query.

Ie, select * from dvd where actor = 'Tom Cruise';

The 'Tom Cruise' part of the query needs to be user input every time the query is ran... Can you help?

Many thanks in advance!?

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-01 : 07:26:10
User input is part of your front end, what front end are you using?

-------
Moo. :)
Go to Top of Page

leighrogers
Starting Member

11 Posts

Posted - 2006-11-01 : 08:06:16
Well, I'm not as such...

I'm doing this as part of my university course. We havn't yet used a front end - I tell a lie, I played with part of the report writer yesterday but its not a formal part of the course. The question as it is listed in the document is:

1)List all of the DVDs for a user entered actor e.g Willis (interactive sql)


My tutor is very unhelpful and hasn't been around for weeks to help!
From what I've found I'm looking for something that involves the && ??

Sorry if this is vague, I really do appreciate your fast response!

Thanks again
Leigh
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-01 : 08:12:34
Are you looking for how to build the query in the front end and send to back end
what front end you are using ?

is the Sample of C#

String strQry;
strQry = "Select * From dvd Where Actor = '" + ActorName + "'"

and then using ADO.Net Datareader class execute the procedure.

here the ActorName is the Variable Name.


Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

leighrogers
Starting Member

11 Posts

Posted - 2006-11-01 : 08:17:25
I dont really know what you are talking about...

There is no C# yet.

I guess what I could do is run it through a web page connection - as I need to do this anyway. So yes... What I guess would be ideal is having a text box in a html page that passes the search criteria to the query.

Apologies for being dumb! - I am clearly new to this!


Go to Top of Page

leighrogers
Starting Member

11 Posts

Posted - 2006-11-01 : 08:20:20
quote:
Originally posted by leighrogers

I dont really know what you are talking about...

There is no C# yet.

I guess what I could do is run it through a web page connection - as I need to do this anyway. So yes... What I guess would be ideal is having a text box in a html page that passes the search criteria to the query.

Apologies for being dumb! - I am clearly new to this!






NOTE: There is no front end yet. that is for me to do next :-)
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-01 : 08:23:15
dude, you need to learn any front end language like ASP.Net or ASP for that matter.. since you require a middle teir to communicate with your database. if you are using ASP then you need to learn ADO and if you are doing ASP.Net then you have to learn Ado.net. so that you fetch the records from the database and can diplay them in either HTML tables or grid.. etc..

Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

leighrogers
Starting Member

11 Posts

Posted - 2006-11-01 : 08:29:12
I have a knowledge of VB.net, C, and Java.

Are the following statements correct?

The lower tier is the db itself, the asp is the middle tier to connect the db to the web page.

For the scenario I gave you I need to create a web page that uses a asp to connect to my db, that passes search criteria to the queries and in turn returns search results.

If so, are you aware of any tutorials or guide's, self teaching or anything you can tell me further? So that I can learn how?

Thanks again for your help and quick responses! (And for your patience!)
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-01 : 08:54:44
have a look at this .. i guess its good for kicking off with your project..

http://www.developerfusion.co.uk/show/4278/

Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-01 : 09:19:04
quote:
Originally posted by leighrogers

Afternoon...
I have a database for a pretend dvd hire company and need to create a query that uses user input as part of the query.

Ie, select * from dvd where actor = 'Tom Cruise';

The 'Tom Cruise' part of the query needs to be user input every time the query is ran... Can you help?

Many thanks in advance!?


It is correct time to say
Do this in front end application


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

leighrogers
Starting Member

11 Posts

Posted - 2006-11-01 : 09:19:16
I have just ran it through my other tutor.

They have told me NOT to create a front end for this yet as that is the next task...

They don't truly understand it as its not their subject but they have suggested I look for the code strip "&&" - does this mean anything to you?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-01 : 09:21:03
>>They have told me NOT to create a front end for this yet as that is the next task...
Then you need to wait until Front end is developed

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 09:56:24
in the mean time look into stored procedures and variables

create procedure GetDVDs
@actor varchar(500)
as
select * from dvd where actor = @actor
go

exec GetDVDs 'Tom Cruise'





Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

leighrogers
Starting Member

11 Posts

Posted - 2006-11-02 : 07:31:22
The final query was apparantly:

select * from dvd where actors like '&& Actor';

Thats it :-)

Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-02 : 07:37:36
Well that must be some vendor-specific parameter substition then, because it's not standard ANSI sql. In normal SQL that would just cause the parser to look for [literal] fields that contain "&& Actor".

-------
Moo. :)
Go to Top of Page
   

- Advertisement -