By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,967 Members | 815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,967 IT Pros & Developers. It's quick & easy.

ASP / SQL Query - Conditional SELECT Statement

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:eO**************@TK2MSFTNGP09.phx.gbl...

****
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.

Jul 19 '05 #2

P: n/a
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:uJ**************@TK2MSFTNGP11.phx.gbl...
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:eO**************@TK2MSFTNGP09.phx.gbl...

****
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.


Jul 19 '05 #3

P: n/a
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:ub**************@TK2MSFTNGP10.phx.gbl...
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:uJ**************@TK2MSFTNGP11.phx.gbl...
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:eO**************@TK2MSFTNGP09.phx.gbl...

****
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.



Jul 19 '05 #4

P: n/a
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
Jul 19 '05 #5

P: n/a
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:eK**************@TK2MSFTNGP11.phx.gbl...
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:ub**************@TK2MSFTNGP10.phx.gbl...
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:uJ**************@TK2MSFTNGP11.phx.gbl...
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:eO**************@TK2MSFTNGP09.phx.gbl...

>
> ****
> 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.
>



Jul 19 '05 #6

P: n/a
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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:eK**************@TK2MSFTNGP11.phx.gbl...
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:ub**************@TK2MSFTNGP10.phx.gbl...
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:uJ**************@TK2MSFTNGP11.phx.gbl...
> 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" <gu*@ANTIbradflack.SPAMcom> wrote in message
> news:eO**************@TK2MSFTNGP09.phx.gbl...
>
> >
> > ****
> > 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.
> >
>
>



Jul 19 '05 #7

P: n/a
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.
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.