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 www.bradflack.com
Please remove ANTI and SPAM from my
email address before sending me an email. 7  3508 
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" <gu*@ANTIbradfl ack.SPAMcom> wrote in message
news:eO******** ******@TK2MSFTN GP09.phx.gbl...
****
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.
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******** ******@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 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*@ANTIbradfl ack.SPAMcom> wrote in message
news:eO******** ******@TK2MSFTN GP09.phx.gbl...
****
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.
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" <gu*@ANTIbradfl ack.SPAMcom> wrote in message
news:ub******** ******@TK2MSFTN GP10.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******** ******@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 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*@ANTIbradfl ack.SPAMcom> wrote in message
news:eO******** ******@TK2MSFTN GP09.phx.gbl...
****
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.
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
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******** ******@TK2MSFTN GP11.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_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" <gu*@ANTIbradfl ack.SPAMcom> wrote in message
news:ub******** ******@TK2MSFTN GP10.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******** ******@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 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*@ANTIbradfl ack.SPAMcom> wrote in message
news:eO******** ******@TK2MSFTN GP09.phx.gbl...
>
> ****
> 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.
>
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*@ANTIbradfl ack.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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******** ******@TK2MSFTN GP11.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_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" <gu*@ANTIbradfl ack.SPAMcom> wrote in message
news:ub******** ******@TK2MSFTN GP10.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******** ******@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
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*@ANTIbradfl ack.SPAMcom> wrote in message
> news:eO******** ******@TK2MSFTN GP09.phx.gbl...
>
> >
> > ****
> > 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.
> >
>
>
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: neblackcat |
last post by:
Would anyone like to comment on the following idea?
I was just going to offer it as a new PEP until it was suggested that
I post it here for comment & consideration against PEP 308.
I'm far from being a "language internist" (on Python or anything else)
so go easy on me if this is stupid - it just seemed quite elegant to
me as a relative newbie in town :-)
I also havent got a clue whether this would be easy or even possible
|
by: Thomas Baxter |
last post by:
Is it possible to have a conditional union statement in a stored proc?
Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.
Is the only solution to create a dynamic sql string then call exec on
it?
Any help appreciated.
|
by: web developer |
last post by:
hi
I need to write a stored procedure that takes input parameters,and
according to these parameters the retrieved fields in a select
statement are chosen.
what i need to know is how to make the fields of the select statement
conditional,taking in consideration that it is more than one field
added
for example
SQLStmt="select"
if param1 then
|
by: chuy08 |
last post by:
Basically I am using PHP 5.1.2 with Apache 2.0.5 on a FreeBSD 5.4 box
with Mysql 4.1.1 running.
I am attempting to write information to a Mysql table called Jabber. I
can connect successfully, query data, and write data pretty much
without problem. My problem is this.
I am trying to do some duplicate checking on this table so I query the
dbase with the following:
|
by: googleboy |
last post by:
I am trying to create a query that will perform a set of queries in a sequence. If the results of the first query are null, then I want the query to continue to the next query. In all, I believe the squence will be about four layers deep. As soon as a query is not null, then I want that result to be output as the solution.
I believe my issue is understanding how to store the results of the first query to then use it as part of a conditional...
| | |
by: RCapps |
last post by:
When running the below SQL Query I keep getting the following error:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefault'.
For some reason it is only returning the first 11 chars of the column name?
Any help would be greatly appreciated...
This query searches a DB and determines which columns are 'Invalid' if the column name is >30 and...
|
by: veaux |
last post by:
Question deals with linking tables in queries. I'm not a code writer
so use the GUI for all my queries.
Table 1 - Master
Table 2 - Sub1
Table 3 - Sub 2
All 3 tables have the same key field.
I'm trying to find all the records that are in "Master", that are not
|
by: tiptap |
last post by:
Hey Guys,
I have a huge statement loads of if statements in... and its getting bigger.
On closer inspection there is only 3 difference in the select statement. so I thought I could cut the whole thing down to just 1 select statement if I have a conditional Having.
I've simplified the IF statement down a bit to give you an idea of what im trying to achieve
IF @month <> 0 & @diffFuture = 0 & @showDate <> 0
|
by: dizzydangler |
last post by:
Hi, I'm new to access (2007, running on XP), but I've gotten some great help from this site on my project so far. However, the more I know, the more complex the problems seem to become....
I'm working with all of my data in a single table and, right now, I've got a form linked to a query that allows the user to input whatever search criteria they want through a variety of controls. I've also created a series of check boxes on the form...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
