I'm having a problem with the advanced sql statement of a page. If anybody has any advice... please! Using UD4, JSP, Access...

1. The page is on
www.theblackbook.net/industry/directory/index2.jsp

2. The customer picks a service from the list menu, which should check to see if the field (fbusiness, fclubs, etc) for that title is true or false (1 or 0), and return all companies that offer that service.

3. Right now, it will just return the service if you pick business/legal, of which there are five companies.

4. I want to make the SQL for
the entire service list.

5. the searchbox is named service. the search parameters are :
name; varservice
default 1
runtime request.getParameter("service"

SELECT * from companies
WHERE (fbusiness = 'varservice' and fbusiness > 0)

if I do this;
WHERE (fbusiness = 'varservice' and fbusiness > 0) OR
(fclubs = 'varservice' and fclubs > 0)

it returns only an answer if business selected, and this
time the answer includes the results where clubs are 1 as
well, therefore it is not looking at whether the user has
chosen clubs.

What I need, therefore, is some SQL statement that will
choose fbusiness and check if fbusiness true/false only if the user
selects business from the list, and choose fclubs and check fclubs only
if the user selects clubs from the list, etc...

any help is gratefully received!!

First you might just go read my message in "aarugh! NT server config for Ultradev JSP... "
Now let's have a look at your problem.

First i can't access the page you put in that link, 404 not found, if you wish me to look at it ? You might put it back ..


My experience lies with IBM Websphere but ill give it a try and see if this works:

So your using the same var for both fbusiness and fclubs, right ? Instead of doing fbusiness > 0 change it it to fbusiness = 1 and change fbusiness LIKE 'varservice' and don't forget if your var is numeric that you can't use the quotes, so would be varservice withou the quotes ! Well hope this gives you some tips, test it and let me know how it works out.
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I'm having a problem with the advanced sql statement of a page. If anybody has any advice... please! Using UD4, JSP, Access...

1. The page is on
www.theblackbook.net/industry/directory/index2.jsp

2. The customer picks a service from the list menu, which should check to see if the field (fbusiness, fclubs, etc) for that title is true or false (1 or 0), and return all companies that offer that service.

3. Right now, it will just return the service if you pick business/legal, of which there are five companies.

4. I want to make the SQL for
the entire service list.

5. the searchbox is named service. the search parameters are :
name; varservice
default 1
runtime request.getParameter("service"

SELECT * from companies
WHERE (fbusiness = 'varservice' and fbusiness &gt; 0)

if I do this;
WHERE (fbusiness = 'varservice' and fbusiness &gt; 0) OR
(fclubs = 'varservice' and fclubs &gt; 0)

it returns only an answer if business selected, and this
time the answer includes the results where clubs are 1 as
well, therefore it is not looking at whether the user has
chosen clubs.

What I need, therefore, is some SQL statement that will
choose fbusiness and check if fbusiness true/false only if the user
selects business from the list, and choose fclubs and check fclubs only
if the user selects clubs from the list, etc...

any help is gratefully received!!




<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Dave Joosten

Thanks for your help. the link works now. sorry about that.
I think the problem is with my default value: when I put a default value of 0, it returns no answers, when it is 1, it returns all answers.... here is my sql
SELECT *
FROM companies
WHERE (MMColParam = fbusiness AND fbusiness &gt; 0) OR (MMColParam = fclubs AND fclubs &gt; 0)
ORDER BY fcompany ASC

Name: MMColParam Default Value = 1
run-time value = request.getParameter("scompany"

Yes that's correct, you always have default value 1 unless it has a specific value.
I checked your page again now and it works, just you got a runtime error on that index2.jsp (Line 10) page and a runtime error on companyres.jsp (Line 19)

These are some minor things but very anoying if everyclick you have to click that off.

Dave Joosten

Hmmm...thanks again. It's not working though: business/legal should only return 5 answers: It is returning both business and clubs at the same time.

oh, by the way, those run-time errors are caused by my sql statement.

maybe if I try to make this question easier:

why does "WHERE (x=y and y=1)OR (x=z and z=1)"

and the user selects x (y or z)

return BOTH y and z values: how do I get it to return y OR z but not y AND z?

Ill post a reply later today, gotta do a few things first.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
maybe if I try to make this question easier:

why does "WHERE (x=y and y=1)OR (x=z and z=1)"

and the user selects x (y or z)

return BOTH y and z values: how do I get it to return y OR z but not y AND z?


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Dave Joosten

it seems okay, now! Thanks for your help, eh!