ASP / SQL Query - Conditional SELECT Statement

[Guy Hocking]

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.

 

[Ray at]

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]

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]

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

 

[Anith Sen]

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

 

[Guy Hocking]

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]

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

 

[Bob Barrows]

Here is an article explaining your options:

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


HTH,
Bob Barrows