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_Na me, vchrChannel,
intTotalSols FROM vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me,
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

https://www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

[Ray at]

Re: ASP / SQL Query - Conditional SELECT Statement

What you're looking for is a WHERE clause.

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

sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, 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" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:eOUTliE4DH A.2380@TK2MSFTN GP09.phx.gbl...
[color=blue]
>
> ****
> Set RS = DataConnection. Execute("SELECT vchrSolution_Na me, vchrChannel,
> intTotalSols FROM vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me,
> 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.
>[/color]

[Guy Hocking]

Re: ASP / SQL Query - Conditional SELECT Statement

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

https://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:uJU56mE4DH A.3576@TK2MSFTN GP11.phx.gbl...[color=blue]
> What you're looking for is a WHERE clause.
>
> <%
> sWhereValue = Session("someth ing")
> ''Do you need to use a session variable as opposed to request.form?
>
> sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
> vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, 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" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> news:eOUTliE4DH A.2380@TK2MSFTN GP09.phx.gbl...
>[color=green]
> >
> > ****
> > Set RS = DataConnection. Execute("SELECT vchrSolution_Na me, vchrChannel,
> > intTotalSols FROM vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me,
> > 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.
> >[/color]
>
>[/color]

[Ray at]

Re: ASP / SQL Query - Conditional SELECT Statement

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_Na me, vchrChannel,int TotalSols FROM
vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, vchrchannel WHERE
YourColumnName= '" & sWhereValue & "' AND YourOtherColumn Name='" &
sOtherValue & "'"

Ray at work









"Guy Hocking" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:ubI2twE4DH A.1596@TK2MSFTN GP10.phx.gbl...[color=blue]
> Thanks for the response....
>
> The only prob with that is that there are many list boxes and many option[/color]
in[color=blue]
> 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:uJU56mE4DH A.3576@TK2MSFTN GP11.phx.gbl...[color=green]
> > What you're looking for is a WHERE clause.
> >
> > <%
> > sWhereValue = Session("someth ing")
> > ''Do you need to use a session variable as opposed to request.form?
> >
> > sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
> > vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, 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" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > news:eOUTliE4DH A.2380@TK2MSFTN GP09.phx.gbl...
> >[color=darkred]
> > >
> > > ****
> > > Set RS = DataConnection. Execute("SELECT vchrSolution_Na me,[/color][/color][/color]
vchrChannel,[color=blue][color=green][color=darkred]
> > > intTotalSols FROM vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me,
> > > vchrchannel")
> > > ****
> > >
> > > Basically i need to know how to filter a SELECT statement in relation[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > what is in the session variable list boxes, ideally a conditional[/color][/color][/color]
SELECT[color=blue][color=green][color=darkred]
> > > statement to minimise the data in the RecordSet.
> > >[/color]
> >
> >[/color]
>
>[/color]

[Anith Sen]

Re: ASP / SQL Query - Conditional SELECT Statement

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

[Guy Hocking]

Re: ASP / SQL Query - Conditional SELECT Statement

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

https://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:eKslc3E4DH A.1700@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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[/color]
of[color=blue]
> countries. Then, based on what country the user selects, you display a[/color]
list[color=blue]
> of cities. This is a one-step-at-a-time thing if you're doing it all in[/color]
ASP[color=blue]
> (as opposed to client side arrays or something).
>
> Are you displaying a form that has areas and regions both listed at the[/color]
same[color=blue]
> time? Or are you just displaying one part at a time?
>
> You can have multiple conditions in your WHERE clause.
>
> sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
> vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, vchrchannel WHERE
> YourColumnName= '" & sWhereValue & "' AND YourOtherColumn Name='" &
> sOtherValue & "'"
>
> Ray at work
>
>
>
>
>
>
>
>
>
> "Guy Hocking" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> news:ubI2twE4DH A.1596@TK2MSFTN GP10.phx.gbl...[color=green]
> > Thanks for the response....
> >
> > The only prob with that is that there are many list boxes and many[/color][/color]
option[color=blue]
> in[color=green]
> > each -
> >
> > So if lstRegion = SouthEast and lstArea = London then data for london[/color][/color]
will[color=blue][color=green]
> > need selecting
> > But if if lstRegion = SouthEast and lstArea = Essex then different data[/color][/color]
is[color=blue][color=green]
> > 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:uJU56mE4DH A.3576@TK2MSFTN GP11.phx.gbl...[color=darkred]
> > > What you're looking for is a WHERE clause.
> > >
> > > <%
> > > sWhereValue = Session("someth ing")
> > > ''Do you need to use a session variable as opposed to request.form?
> > >
> > > sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
> > > vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, 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" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > news:eOUTliE4DH A.2380@TK2MSFTN GP09.phx.gbl...
> > >
> > > >
> > > > ****
> > > > Set RS = DataConnection. Execute("SELECT vchrSolution_Na me,[/color][/color]
> vchrChannel,[color=green][color=darkred]
> > > > intTotalSols FROM vw_SOLS_Nationa lCount_u ORDER BY[/color][/color][/color]
vchrSolution_na me,[color=blue][color=green][color=darkred]
> > > > vchrchannel")
> > > > ****
> > > >
> > > > Basically i need to know how to filter a SELECT statement in[/color][/color][/color]
relation[color=blue]
> to[color=green][color=darkred]
> > > > what is in the session variable list boxes, ideally a conditional[/color][/color]
> SELECT[color=green][color=darkred]
> > > > statement to minimise the data in the RecordSet.
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Ray at]

Re: ASP / SQL Query - Conditional SELECT Statement

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" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:%23eSrk$E4 DHA.2132@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> 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 ;-) ?[/color]
as[color=blue]
> 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.....[/color]
and[color=blue]
> 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:eKslc3E4DH A.1700@TK2MSFTN GP11.phx.gbl...[color=green]
> > You can have multiple conditions in your WHERE clause. Typically what[/color][/color]
you[color=blue][color=green]
> > 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[/color][/color]
list[color=blue]
> of[color=green]
> > countries. Then, based on what country the user selects, you display a[/color]
> list[color=green]
> > of cities. This is a one-step-at-a-time thing if you're doing it all in[/color]
> ASP[color=green]
> > (as opposed to client side arrays or something).
> >
> > Are you displaying a form that has areas and regions both listed at the[/color]
> same[color=green]
> > time? Or are you just displaying one part at a time?
> >
> > You can have multiple conditions in your WHERE clause.
> >
> > sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
> > vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, vchrchannel WHERE
> > YourColumnName= '" & sWhereValue & "' AND YourOtherColumn Name='" &
> > sOtherValue & "'"
> >
> > Ray at work
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Guy Hocking" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > news:ubI2twE4DH A.1596@TK2MSFTN GP10.phx.gbl...[color=darkred]
> > > Thanks for the response....
> > >
> > > The only prob with that is that there are many list boxes and many[/color][/color]
> option[color=green]
> > in[color=darkred]
> > > each -
> > >
> > > So if lstRegion = SouthEast and lstArea = London then data for london[/color][/color]
> will[color=green][color=darkred]
> > > need selecting
> > > But if if lstRegion = SouthEast and lstArea = Essex then different[/color][/color][/color]
data[color=blue]
> is[color=green][color=darkred]
> > > 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[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > news:uJU56mE4DH A.3576@TK2MSFTN GP11.phx.gbl...
> > > > What you're looking for is a WHERE clause.
> > > >
> > > > <%
> > > > sWhereValue = Session("someth ing")
> > > > ''Do you need to use a session variable as opposed to request.form?
> > > >
> > > > sSQL = "SELECT vchrSolution_Na me, vchrChannel,int TotalSols FROM
> > > > vw_SOLS_Nationa lCount_u ORDER BY vchrSolution_na me, vchrchannel[/color][/color][/color]
WHERE[color=blue][color=green][color=darkred]
> > > > YourColumnName= '" & sWhereValue & "'"
> > > >
> > > > '''If your column that you use in your WHERE clause is numeric, do[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > > delimit the value with '.
> > > >
> > > > Set RS = DataConnection. Execute(sSQL)
> > > > %>
> > > >
> > > > Ray at work
> > > >
> > > >
> > > > "Guy Hocking" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > > news:eOUTliE4DH A.2380@TK2MSFTN GP09.phx.gbl...
> > > >
> > > > >
> > > > > ****
> > > > > Set RS = DataConnection. Execute("SELECT vchrSolution_Na me,[/color]
> > vchrChannel,[color=darkred]
> > > > > intTotalSols FROM vw_SOLS_Nationa lCount_u ORDER BY[/color][/color]
> vchrSolution_na me,[color=green][color=darkred]
> > > > > vchrchannel")
> > > > > ****
> > > > >
> > > > > Basically i need to know how to filter a SELECT statement in[/color][/color]
> relation[color=green]
> > to[color=darkred]
> > > > > what is in the session variable list boxes, ideally a conditional[/color]
> > SELECT[color=darkred]
> > > > > statement to minimise the data in the RecordSet.
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Bob Barrows]

Re: ASP / SQL Query - Conditional SELECT Statement

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.