ASP / SQL Query - Conditional SELECT Statement

Discussion in 'ASP General' started by Guy Hocking, Jan 21, 2004.

  1. Guy Hocking

    Guy Hocking Guest

    Hi there,

    I have a problem in my ASP/SQL Server application i am developing, i hope
    you guys can help.

    I have a ASP form with list boxes populated by SQL tables. When a user
    selects a value in a list box and submits the form the value is put into a
    session variable and the relevant page is displayed (in accordance to one of
    the list boxes).
    The page is then displayed with the relevant SQL data. So far i have got the
    data to be displayed without any filtering done in relation to the users
    input.

    What i am trying to do at the moment, and failing, is to issue a SELECT
    statement initally, stating what columns are needed from a specific view in
    SQL Server -

    ****
    Set RS = DataConnection.Execute("SELECT vchrSolution_Name, vchrChannel,
    intTotalSols FROM vw_SOLS_NationalCount_u ORDER BY vchrSolution_name,
    vchrchannel")
    ****

    For what i need to do, i need to now write an IF statement that only
    displays what is in the session variable list box.
    EG -

    The list box "lstOFFER" contains the value "ALL", so all the data is
    selected. Or it contains "OFFER" so all the records in the view column
    vchrChannel that have OFFER in the column are selected.

    And so on for several list boxes.

    I realise this is not the most efficient way of doing this but it is the way
    we have to do it :-(

    Basically i need to know how to filter a SELECT statement in relation to
    what is in the session variable list boxes, ideally a conditional SELECT
    statement to minimise the data in the RecordSet.

    I hope this makes sense....

    sorry if i come accross nieve , (I am quite new to ASP/SQL application
    development, thrown in at the deep end! best way to learn i guess!)

    --
    Guy


    www.bradflack.com

    Please remove ANTI and SPAM from my
    email address before sending me an email.
    Guy Hocking, Jan 21, 2004
    #1
    1. Advertising

  2. Guy Hocking

    Ray at Guest

    What you're looking for is a WHERE clause.

    <%
    sWhereValue = Session("something")
    ''Do you need to use a session variable as opposed to request.form?

    sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    YourColumnName='" & sWhereValue & "'"

    '''If your column that you use in your WHERE clause is numeric, do not
    delimit the value with '.

    Set RS = DataConnection.Execute(sSQL)
    %>

    Ray at work


    "Guy Hocking" <> wrote in message
    news:...

    >
    > ****
    > Set RS = DataConnection.Execute("SELECT vchrSolution_Name, vchrChannel,
    > intTotalSols FROM vw_SOLS_NationalCount_u ORDER BY vchrSolution_name,
    > vchrchannel")
    > ****
    >
    > Basically i need to know how to filter a SELECT statement in relation to
    > what is in the session variable list boxes, ideally a conditional SELECT
    > statement to minimise the data in the RecordSet.
    >
    Ray at, Jan 21, 2004
    #2
    1. Advertising

  3. Guy Hocking

    Guy Hocking Guest

    Thanks for the response....

    The only prob with that is that there are many list boxes and many option in
    each -

    So if lstRegion = SouthEast and lstArea = London then data for london will
    need selecting
    But if if lstRegion = SouthEast and lstArea = Essex then different data is
    selected.

    or if lstRegion = SouthWest and lstArea = Exeter......

    And so on.... + loads more conditions and dates and stuff....
    Any ideas?

    not sure if iv helped explain the prob

    --
    Guy

    www.bradflack.com

    Please remove ANTI and SPAM from my
    email address before sending me an email.
    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:...
    > What you're looking for is a WHERE clause.
    >
    > <%
    > sWhereValue = Session("something")
    > ''Do you need to use a session variable as opposed to request.form?
    >
    > sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    > vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    > YourColumnName='" & sWhereValue & "'"
    >
    > '''If your column that you use in your WHERE clause is numeric, do not
    > delimit the value with '.
    >
    > Set RS = DataConnection.Execute(sSQL)
    > %>
    >
    > Ray at work
    >
    >
    > "Guy Hocking" <> wrote in message
    > news:...
    >
    > >
    > > ****
    > > Set RS = DataConnection.Execute("SELECT vchrSolution_Name, vchrChannel,
    > > intTotalSols FROM vw_SOLS_NationalCount_u ORDER BY vchrSolution_name,
    > > vchrchannel")
    > > ****
    > >
    > > Basically i need to know how to filter a SELECT statement in relation to
    > > what is in the session variable list boxes, ideally a conditional SELECT
    > > statement to minimise the data in the RecordSet.
    > >

    >
    >
    Guy Hocking, Jan 21, 2004
    #3
  4. Guy Hocking

    Ray at Guest

    You can have multiple conditions in your WHERE clause. Typically what you
    do is present the user with the first option he has to specify, like the
    continent. And then, based on the continent selected, you display a list of
    countries. Then, based on what country the user selects, you display a list
    of cities. This is a one-step-at-a-time thing if you're doing it all in ASP
    (as opposed to client side arrays or something).

    Are you displaying a form that has areas and regions both listed at the same
    time? Or are you just displaying one part at a time?

    You can have multiple conditions in your WHERE clause.

    sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    YourColumnName='" & sWhereValue & "' AND YourOtherColumnName='" &
    sOtherValue & "'"

    Ray at work









    "Guy Hocking" <> wrote in message
    news:...
    > Thanks for the response....
    >
    > The only prob with that is that there are many list boxes and many option

    in
    > each -
    >
    > So if lstRegion = SouthEast and lstArea = London then data for london will
    > need selecting
    > But if if lstRegion = SouthEast and lstArea = Essex then different data is
    > selected.
    >
    > or if lstRegion = SouthWest and lstArea = Exeter......
    >
    > And so on.... + loads more conditions and dates and stuff....
    > Any ideas?
    >
    > not sure if iv helped explain the prob
    >
    > --
    > Guy
    >
    > www.bradflack.com
    >
    > Please remove ANTI and SPAM from my
    > email address before sending me an email.
    > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > news:...
    > > What you're looking for is a WHERE clause.
    > >
    > > <%
    > > sWhereValue = Session("something")
    > > ''Do you need to use a session variable as opposed to request.form?
    > >
    > > sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    > > vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    > > YourColumnName='" & sWhereValue & "'"
    > >
    > > '''If your column that you use in your WHERE clause is numeric, do not
    > > delimit the value with '.
    > >
    > > Set RS = DataConnection.Execute(sSQL)
    > > %>
    > >
    > > Ray at work
    > >
    > >
    > > "Guy Hocking" <> wrote in message
    > > news:...
    > >
    > > >
    > > > ****
    > > > Set RS = DataConnection.Execute("SELECT vchrSolution_Name,

    vchrChannel,
    > > > intTotalSols FROM vw_SOLS_NationalCount_u ORDER BY vchrSolution_name,
    > > > vchrchannel")
    > > > ****
    > > >
    > > > Basically i need to know how to filter a SELECT statement in relation

    to
    > > > what is in the session variable list boxes, ideally a conditional

    SELECT
    > > > statement to minimise the data in the RecordSet.
    > > >

    > >
    > >

    >
    >
    Ray at, Jan 21, 2004
    #4
  5. Guy Hocking

    Anith Sen Guest

    You can devise a WHERE clause like:

    WHERE offercol = COALESCE(NULLIF(lstOFFER, 'ALL'), offercol)
    AND Areacol = COALESCE(NULLIF(lstArea , 'ALL'), Areacol)
    AND ...

    Alternatively, you can check the values using the ASP code & build the SQL
    string accordingly as well. For some ideas on different options on such
    problems, please refer to :
    http://www.sommarskog.se/dyn-search.html

    --
    Anith
    Anith Sen, Jan 21, 2004
    #5
  6. Guy Hocking

    Guy Hocking Guest

    I am displaying the whole form at the same time but i havnt yet de-limited
    the list boxes in accordance to each other - that will be later.....

    if i use the WHERE claus, will it be conditional (i like that word ;-) ? as
    the data needs to be displyed on the same page according to what was
    selected in the form on the previous page.

    So they will vary, but not all of the WHERE claus will be relevant..... and
    thats one hell of a statement with 10 list boxes and 20 values in each!?
    what you reckon?


    --
    Guy

    www.bradflack.com

    Please remove ANTI and SPAM from my
    email address before sending me an email.
    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:...
    > You can have multiple conditions in your WHERE clause. Typically what you
    > do is present the user with the first option he has to specify, like the
    > continent. And then, based on the continent selected, you display a list

    of
    > countries. Then, based on what country the user selects, you display a

    list
    > of cities. This is a one-step-at-a-time thing if you're doing it all in

    ASP
    > (as opposed to client side arrays or something).
    >
    > Are you displaying a form that has areas and regions both listed at the

    same
    > time? Or are you just displaying one part at a time?
    >
    > You can have multiple conditions in your WHERE clause.
    >
    > sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    > vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    > YourColumnName='" & sWhereValue & "' AND YourOtherColumnName='" &
    > sOtherValue & "'"
    >
    > Ray at work
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > "Guy Hocking" <> wrote in message
    > news:...
    > > Thanks for the response....
    > >
    > > The only prob with that is that there are many list boxes and many

    option
    > in
    > > each -
    > >
    > > So if lstRegion = SouthEast and lstArea = London then data for london

    will
    > > need selecting
    > > But if if lstRegion = SouthEast and lstArea = Essex then different data

    is
    > > selected.
    > >
    > > or if lstRegion = SouthWest and lstArea = Exeter......
    > >
    > > And so on.... + loads more conditions and dates and stuff....
    > > Any ideas?
    > >
    > > not sure if iv helped explain the prob
    > >
    > > --
    > > Guy
    > >
    > > www.bradflack.com
    > >
    > > Please remove ANTI and SPAM from my
    > > email address before sending me an email.
    > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > > news:...
    > > > What you're looking for is a WHERE clause.
    > > >
    > > > <%
    > > > sWhereValue = Session("something")
    > > > ''Do you need to use a session variable as opposed to request.form?
    > > >
    > > > sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    > > > vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    > > > YourColumnName='" & sWhereValue & "'"
    > > >
    > > > '''If your column that you use in your WHERE clause is numeric, do not
    > > > delimit the value with '.
    > > >
    > > > Set RS = DataConnection.Execute(sSQL)
    > > > %>
    > > >
    > > > Ray at work
    > > >
    > > >
    > > > "Guy Hocking" <> wrote in message
    > > > news:...
    > > >
    > > > >
    > > > > ****
    > > > > Set RS = DataConnection.Execute("SELECT vchrSolution_Name,

    > vchrChannel,
    > > > > intTotalSols FROM vw_SOLS_NationalCount_u ORDER BY

    vchrSolution_name,
    > > > > vchrchannel")
    > > > > ****
    > > > >
    > > > > Basically i need to know how to filter a SELECT statement in

    relation
    > to
    > > > > what is in the session variable list boxes, ideally a conditional

    > SELECT
    > > > > statement to minimise the data in the RecordSet.
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Guy Hocking, Jan 21, 2004
    #6
  7. Guy Hocking

    Ray at Guest

    Do you have a link or anything that you can post?

    To populate your 10 listboxes, you can execute 10 queries, if each of them
    contains different data. You could do it all in one and then use your ASP
    code to determine when one starts and the other ends, but that would be a
    bit of a headache. I can't tell if today is a day that I have a low
    comprehension level and I'm not understanding where you're stuck, or if
    you're not explaining where you're stuck. :]

    Ray at work

    "Guy Hocking" <> wrote in message
    news:%23eSrk$...
    > I am displaying the whole form at the same time but i havnt yet de-limited
    > the list boxes in accordance to each other - that will be later.....
    >
    > if i use the WHERE claus, will it be conditional (i like that word ;-) ?

    as
    > the data needs to be displyed on the same page according to what was
    > selected in the form on the previous page.
    >
    > So they will vary, but not all of the WHERE claus will be relevant.....

    and
    > thats one hell of a statement with 10 list boxes and 20 values in each!?
    > what you reckon?
    >
    >
    > --
    > Guy
    >
    > www.bradflack.com
    >
    > Please remove ANTI and SPAM from my
    > email address before sending me an email.
    > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > news:...
    > > You can have multiple conditions in your WHERE clause. Typically what

    you
    > > do is present the user with the first option he has to specify, like the
    > > continent. And then, based on the continent selected, you display a

    list
    > of
    > > countries. Then, based on what country the user selects, you display a

    > list
    > > of cities. This is a one-step-at-a-time thing if you're doing it all in

    > ASP
    > > (as opposed to client side arrays or something).
    > >
    > > Are you displaying a form that has areas and regions both listed at the

    > same
    > > time? Or are you just displaying one part at a time?
    > >
    > > You can have multiple conditions in your WHERE clause.
    > >
    > > sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    > > vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel WHERE
    > > YourColumnName='" & sWhereValue & "' AND YourOtherColumnName='" &
    > > sOtherValue & "'"
    > >
    > > Ray at work
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > > "Guy Hocking" <> wrote in message
    > > news:...
    > > > Thanks for the response....
    > > >
    > > > The only prob with that is that there are many list boxes and many

    > option
    > > in
    > > > each -
    > > >
    > > > So if lstRegion = SouthEast and lstArea = London then data for london

    > will
    > > > need selecting
    > > > But if if lstRegion = SouthEast and lstArea = Essex then different

    data
    > is
    > > > selected.
    > > >
    > > > or if lstRegion = SouthWest and lstArea = Exeter......
    > > >
    > > > And so on.... + loads more conditions and dates and stuff....
    > > > Any ideas?
    > > >
    > > > not sure if iv helped explain the prob
    > > >
    > > > --
    > > > Guy
    > > >
    > > > www.bradflack.com
    > > >
    > > > Please remove ANTI and SPAM from my
    > > > email address before sending me an email.
    > > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in

    message
    > > > news:...
    > > > > What you're looking for is a WHERE clause.
    > > > >
    > > > > <%
    > > > > sWhereValue = Session("something")
    > > > > ''Do you need to use a session variable as opposed to request.form?
    > > > >
    > > > > sSQL = "SELECT vchrSolution_Name, vchrChannel,intTotalSols FROM
    > > > > vw_SOLS_NationalCount_u ORDER BY vchrSolution_name, vchrchannel

    WHERE
    > > > > YourColumnName='" & sWhereValue & "'"
    > > > >
    > > > > '''If your column that you use in your WHERE clause is numeric, do

    not
    > > > > delimit the value with '.
    > > > >
    > > > > Set RS = DataConnection.Execute(sSQL)
    > > > > %>
    > > > >
    > > > > Ray at work
    > > > >
    > > > >
    > > > > "Guy Hocking" <> wrote in message
    > > > > news:...
    > > > >
    > > > > >
    > > > > > ****
    > > > > > Set RS = DataConnection.Execute("SELECT vchrSolution_Name,

    > > vchrChannel,
    > > > > > intTotalSols FROM vw_SOLS_NationalCount_u ORDER BY

    > vchrSolution_name,
    > > > > > vchrchannel")
    > > > > > ****
    > > > > >
    > > > > > Basically i need to know how to filter a SELECT statement in

    > relation
    > > to
    > > > > > what is in the session variable list boxes, ideally a conditional

    > > SELECT
    > > > > > statement to minimise the data in the RecordSet.
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Ray at, Jan 21, 2004
    #7
  8. Guy Hocking

    Bob Barrows Guest

    Here is an article explaining your options:

    http://www.algonet.se/~sommar/dyn-search.html


    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows, Jan 21, 2004
    #8
    1. Advertising

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. Replies:
    3
    Views:
    787
  2. Anonymous
    Replies:
    0
    Views:
    1,442
    Anonymous
    Oct 13, 2005
  3. Alec S.
    Replies:
    10
    Views:
    10,091
    Alec S.
    Apr 16, 2005
  4. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    194
    Jeff Cochran
    Sep 22, 2004
  5. David

    Dates in ASP SQL Select Statement....

    David, Apr 12, 2005, in forum: ASP General
    Replies:
    2
    Views:
    217
    dlbjr
    Apr 12, 2005
Loading...

Share This Page