Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SQL WHERE command and IF

Reply
Thread Tools

SQL WHERE command and IF

 
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-24-2006
Hello

I have a two menus where the user chooses the make and model of a car.
After submitting, a results page shows depending on their inputs. My
SQL is currently:

SELECT *
FROM MainTable
WHERE [model] = '" & chosenmodel & "'
ORDER BY Price DESC

I now want to introduce an option where they can search for any model
of a selected make, whilst still giving the option of searching by a
specific model. What i need is something like:

IF chosenmodel = "Any"
SELECT *
FROM MainTable
WHERE [make] = '" & chosenmake & "'
ORDER BY Price DESC

ELSE
SELECT *
FROM MainTable
WHERE [model] = '" & chosenmodel & "'
ORDER BY Price DESC

Unfortunately I have no idea how i can put this into SQL code as i am
new to the language. My searching for an answer so far has only lead to
me to using AND and OR as part of the WHERE command. However, this will
not work for what i want to do. Please can anyone help me?!

Thanks for your time,
Gareth

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-24-2006
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
> Hello
>
> I have a two menus where the user chooses the make and model of a car.
> After submitting, a results page shows depending on their inputs. My
> SQL is currently:
>
> SELECT *


Nothing to do with your problem, but:
http://www.aspfaq.com/show.asp?id=2096

> FROM MainTable
> WHERE [model] = '" & chosenmodel & "'
> ORDER BY Price DESC


Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e



>
> I now want to introduce an option where they can search for any model
> of a selected make, whilst still giving the option of searching by a
> specific model. What i need is something like:
>
> IF chosenmodel = "Any"
> SELECT *
> FROM MainTable
> WHERE [make] = '" & chosenmake & "'
> ORDER BY Price DESC
>
> ELSE
> SELECT *
> FROM MainTable
> WHERE [model] = '" & chosenmodel & "'
> ORDER BY Price DESC
>
> Unfortunately I have no idea how i can put this into SQL code as i am
> new to the language. My searching for an answer so far has only lead
> to me to using AND and OR as part of the WHERE command. However, this
> will not work for what i want to do. Please can anyone help me?!
>

What database (type and version, please) are you using? In the future,
please provide this information upfront: it is almost always relevant.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
 
gjoneshtfc@volcanomail.com
Guest
Posts: n/a
 
      06-24-2006
Thanks Bob,

Whilst waiting for a reply i have been messing around and have managed
to solve my problem using ASP IF and Else to set the recordset. Thanks
for the reply and i will be sure to look into your suggestions to make
my coding more secure.

Thanks again,
Gareth


Bob Barrows [MVP] wrote:

> (E-Mail Removed) wrote:
> > Hello
> >
> > I have a two menus where the user chooses the make and model of a car.
> > After submitting, a results page shows depending on their inputs. My
> > SQL is currently:
> >
> > SELECT *

>
> Nothing to do with your problem, but:
> http://www.aspfaq.com/show.asp?id=2096
>
> > FROM MainTable
> > WHERE [model] = '" & chosenmodel & "'
> > ORDER BY Price DESC

>
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:
> http://groups-beta.google.com/group/...e36562fee7804e
>
>
>
> >
> > I now want to introduce an option where they can search for any model
> > of a selected make, whilst still giving the option of searching by a
> > specific model. What i need is something like:
> >
> > IF chosenmodel = "Any"
> > SELECT *
> > FROM MainTable
> > WHERE [make] = '" & chosenmake & "'
> > ORDER BY Price DESC
> >
> > ELSE
> > SELECT *
> > FROM MainTable
> > WHERE [model] = '" & chosenmodel & "'
> > ORDER BY Price DESC
> >
> > Unfortunately I have no idea how i can put this into SQL code as i am
> > new to the language. My searching for an answer so far has only lead
> > to me to using AND and OR as part of the WHERE command. However, this
> > will not work for what i want to do. Please can anyone help me?!
> >

> What database (type and version, please) are you using? In the future,
> please provide this information upfront: it is almost always relevant.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Paging and Sql command Franz ASP .Net 5 04-18-2006 07:53 AM
NCQ (Native Command Queuing) and TCQ (Tagged Command Queuing) Explained Silverstrand Front Page News 0 04-17-2006 05:49 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
SQLConnection SQL Command SQL DataAdapter Biggie ASP .Net 1 02-06-2004 04:32 AM



Advertisments