SQL WHERE command and IF

Discussion in 'ASP General' started by gjoneshtfc@volcanomail.com, Jun 24, 2006.

  1. Guest

    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
     
    , Jun 24, 2006
    #1
    1. Advertisements

  2. 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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e



    >
    > 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"
     
    Bob Barrows [MVP], Jun 24, 2006
    #2
    1. Advertisements

  3. Guest

    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:

    > 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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
    >
    >
    >
    > >
    > > 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"
     
    , Jun 24, 2006
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Biggie
    Replies:
    1
    Views:
    3,770
    Alvin Bruney [MVP]
    Feb 6, 2004
  2. =?Utf-8?B?c2NvdHRybQ==?=

    sql session state for .net v1.1/.net v2.0 and sql server 2005

    =?Utf-8?B?c2NvdHRybQ==?=, Feb 13, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    2,982
    Steven Cheng[MSFT]
    Feb 16, 2006
  3. =?Utf-8?B?dmE=?=
    Replies:
    4
    Views:
    3,101
    =?Utf-8?B?dmE=?=
    Feb 22, 2006
  4. Edward
    Replies:
    4
    Views:
    4,859
    William \(Bill\) Vaughn
    Apr 10, 2006
  5. Franz

    Paging and Sql command

    Franz, Apr 15, 2006, in forum: ASP .Net
    Replies:
    5
    Views:
    741
    Franz
    Apr 18, 2006
  6. Harry Zoroc
    Replies:
    1
    Views:
    1,189
    Gregory Vaughan
    Jul 12, 2004
  7. ecoolone
    Replies:
    0
    Views:
    956
    ecoolone
    Jan 3, 2008
  8. Belinda
    Replies:
    4
    Views:
    628
    Bob Barrows [MVP]
    Jun 11, 2004
Loading...