navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

 

"OR" Bullet point to change "AND" query

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> "OR" Bullet point to change "AND" query 		Page: [1]
 
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		"OR" Bullet point to change "AND" q... - 9/13/2005 16:26:16   
Howdy.

I was wondering if anyone could give me some advice on this...

I have 6 variable-fields (3 drop down boxes and 3 sets of "range" fields) in a search form.
Size, Customer, Slip, Load, Water Depth, and Setting Depth. They are all searched at the same time.

The query looks like this;
SELECT * FROM [LSjob]
WHERE (Load BETWEEN ::Loadfrom:: AND ::Loadto::)
AND (SettingDepth BETWEEN ::Settingfrom:: AND ::Settingto::)
AND (WaterDepth BETWEEN ::Waterfrom:: AND ::Waterto::)
AND (Size LIKE '%::Sizeddb::%')
AND (Customer LIKE '%::Customerddb::%')
AND (Slip LIKE '%::Slipddb::%') ORDER BY JobNumber


I need to keep the ANDs in there to really narrow the search down. However, it has occured to me that occasionally a user might want to search for a particular "load" or a particular "Customer".

What I would like is to have a bullet point next to each search field. When the bullet is on for a particular field, that field is searched as an OR. I would make it so only one field could be searched as an "or" per search.

My question is, Is this possible? If it is, would it require a rediculous amount of effort?

Any and All help is always appreciated.

Eric
Mesturee@sbcglobal.net
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/13/2005 16:56:09   
YMMV, but this is the general theory (using the diet) 

SELECT * FROM [LSjob]
WHERE (Load BETWEEN ::Loadfrom:: AND ::Loadto::)
::radio1:: (SettingDepth BETWEEN ::Settingfrom:: AND ::Settingto::)
::radio2::(WaterDepth BETWEEN ::Waterfrom:: AND ::Waterto::)
::radio3:: (Size LIKE '%::Sizeddb::%')
::radio4:: (Customer LIKE '%::Customerddb::%')
::radio5::  (Slip LIKE '%::Slipddb::%') ORDER BY JobNumber


Using :

[selection 1] <input type=radio name=radio1 value=and checked>(and) <input type=radio name=radio1 value=or>(or)
[selection 2] <input type=radio name=radio2 value=and checked>(and) <input type=radio name=radio2 value=or>(or)

etc...

If that doesnt work, then a similar thing can be done in pure vbscript

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/13/2005 17:25:06   
Two questions. First, about the diet. As I understand it, I get rid of the useless webbot code, and the "red" sql statements can then be edited. Am I right or am I wrong?

Second question is a two parter.

Where would I type this?

[selection 1] <input type=radio name=radio1 value=and checked>(and) <input type=radio name=radio1 value=or>(or)
[selection 2] <input type=radio name=radio2 value=and checked>(and) <input type=radio name=radio2 value=or>(or)

and is [selection 1] supposed to stand for something?

I guess that technically that is three questions.

Eric

(in reply to Spooky)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/13/2005 18:12:06   
Nevermind. :)

I figured out where I was supposed to enter the [selections].

Worked like a charm.

Thank you so much for your help.

There is one thing though. In the posted scenario "tensileload" is the "foundation" and therefore cannot be searched as an "or".

Do you know how I can get around this?

Or in other words, do you know of an "all encompassing(sp?)" WHERE statement that I can use as the foundation?

Thanks again.

(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/14/2005 11:41:59   
OK there is a problem, and I can't think of a way around it.

The way this query is written, it creates a string

WHERE Load and Setting and Water and Casing and Customer and Slip

When the Or radio is selected, it cuts the string and starts a new string at the point of the "Or Cut"

For instance, when the "or" radio for Water Depth is active, the query changes to this:
WHERE Load and Setting
Or (WHERE) Water and Casing and Customer and Slip

I need this to happen when the "or" radio for water is active:
WHERE Load and Setting and Water and Casing and Customer and Slip
Or Water

The problem, of course, is that there are six variables. Basically any "or" statements need to be at the end of the string to keep them from having any "and" statements connected to them.

I can't think of an easy way of doing this.

I have tried giving the OR radio a value of "OR (Load BETWEEN ::Loadfrom:: AND ::Loadto::)" and then adding

::radio1:: ::radio2:: ::radio3::

to the end of my pre-existing query. So it looked like this 
SELECT * FROM [LSjob] 
WHERE (Load BETWEEN ::Loadfrom:: AND ::Loadto::) 
AND (SettingDepth BETWEEN ::Settingfrom:: AND ::Settingto::) 
AND (WaterDepth BETWEEN ::Waterfrom:: AND ::Waterto::) 
AND (Size LIKE '%::Sizeddb::%') 
AND (Customer LIKE '%::Customerddb::%') 
AND (Slip LIKE '%::Slipddb::%') ::radio1:: ::radio2:: ::radio3:: ORDER BY JobNumber


However the fields like ::loadfrom:: would not be filled in and I would get an error.

I am not sure if I have clearly explained the situation, but I would be happy to go into more detail if it is needed.

(in reply to mesturee)
dzirkelb1

 

Posts: 1313
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 12:46:56    
SELECT * FROM LSjob WHERE " &_
" IIf(('"&request.form("LoadFrom")&"'&''=''), True, Load>='"&request.form("LoadFrom")&"')" &_
" AND IIf(('"&request.form("LoadTo")&"'&''=''), True, Load<='"&request.form("LoadTo")&"')" &_
" IIf(('"&request.form("Settingfrom")&"'&''=''), True, SettingDepth >='"&request.form("Settingfrom")&"')" &_
" AND IIf(('"&request.form("Settingto")&"'&''=''), True, SettingDepth <='"&request.form("Settingto")&"')" &_
" IIf(('"&request.form("Waterfrom")&"'&''=''), True, WaterDepth >='"&request.form("Waterfrom")&"')" &_
" AND IIf(('"&request.form("Waterto")&"'&''=''), True, WaterDepth <='"&request.form("Waterto")&"')" &_
" IIf(('"&request.form("Sizeddb")&"'&''=''), True, Size ='"&request.form("Sizeddb")&"')" &_
" IIf(('"&request.form("Customerddb")&"'&''=''), True, Customer ='"&request.form("Customerddb")&"')" &_
" IIf(('"&request.form("Slipddb")&"'&''=''), True, Slip='"&request.form("Slipddb")&"')"


The above statement should do what you are looking for (I didn't finish it). It basically says, if LoadFrom is empty, and there is a value in LoadTo, then it will take everything less of LoadTo. BAsically, it ignores the statement if the input is blank adn goes on to the next.

(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 13:56:30   
Thanks Dave. You helped me solve a major problem that I was having. I needed to have an "ANY" option in my drop down boxes, that basically would not limit the search by the records in that field. This suggestion helped allowed me to do that by giving the ANY option a value of "", and adding %s to either side of the drop down forms.

Thanks again.
Eric

(in reply to dzirkelb1)
dzirkelb1

 

Posts: 1313
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 14:19:25   
Oh ya, I forgot to add that tidbit of value "" for the dropdowns...nice to see it works for ya! I had the same problem about a year ago and I use this for like, all of my searches :)

(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 14:38:08   
I just tried something out and I couldn't figure out what the problem is.

How would you get this to work for just one drop down box search field?

Here is what I tried
SELECT * FROM LScomponent WHERE (Manufacturer LIKE'%*%' AND) " &_
" IIf(('"&request.form("Manufacturerddb")&"'&''=''), True, Manufacturer LIKE'"%&request.form("Manufacturerddb")&%"')""


When I try to do it, it tells me that the Red %s are invalid characters, even though I used them in the query you gave me.

It is not really necessary, but I figured I would see if it worked, and I ran smack dab into a problem.

Thanks again
Eric

(in reply to dzirkelb1)
dzirkelb1

 

Posts: 1313
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 14:42:23   
SELECT * FROM LScomponent WHERE (Manufacturer LIKE'%*%' ) " &_
" AND IIf(('"&request.form("Manufacturerddb")&"'&''=''), True, Manufacturer LIKE'"%&request.form("Manufacturerddb")&%"')""

Try that, the and was iun the () of the like statement

(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 14:50:46   
Your'e right, it was.
Unfortunately it is still telling me that the % is an invalid character in this section...

Manufacturer LIKE'"%&request.form("Manufacturerddb")&%"')

Here is the end result for the query you came up with 

SELECT * FROM LSjob WHERE " &_
" IIf(('"&request.form("TensileLoadfrom")&"'&''=''), True, TensileLoad>='"&request.form("TensileLoadfrom")&"')" &_
" AND IIf(('"&request.form("TensileLoadto")&"'&''=''), True, TensileLoad<='"&request.form("TensileLoadto")&"')" &_
" IIf(('"&request.form("SettingDepthfrom")&"'&''=''), True, SettingDepth >='"&request.form("SettingDepthfrom")&"')" &_
" AND IIf(('"&request.form("SettingDepthto")&"'&''=''), True, SettingDepth <='"&request.form("SettingDepthto")&"')" &_
" IIf(('"&request.form("WaterDepthfrom")&"'&''=''), True, WaterDepth >='"&request.form("WaterDepthfrom")&"')" &_
" AND IIf(('"&request.form("WaterDepthto")&"'&''=''), True, WaterDepth <='"&request.form("WaterDepthto")&"')" &_
" IIf(('"&request.form("CasingSizeddb")&"'&''=''), True, CasingSize LIKE'"%&request.form("CasingSizeddb")&%"')" &_
" IIf(('"&request.form("CustomerCompanyddb")&"'&''=''), True, CustomerCompany LIKE'"%&request.form("CustomerCompanyddb")&%"')" &_
" IIf(('"&request.form("SlipTypeddb")&"'&''=''), True, SlipType LIKE'"%&request.form("SlipTypeddb")&%"')""


As best I can tell, the %s are in the same place in the last three statements, and this query works like a charm.

(in reply to dzirkelb1)
dzirkelb1

 

Posts: 1313
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 15:22:13    
SELECT * FROM LSjob WHERE " &_
" IIf(('"&request.form("TensileLoadfrom")&"'&''=''), True, TensileLoad>='"&request.form("TensileLoadfrom")&"')" &_
" AND IIf(('"&request.form("TensileLoadto")&"'&''=''), True, TensileLoad<='"&request.form("TensileLoadto")&"')" &_
" AND IIf(('"&request.form("SettingDepthfrom")&"'&''=''), True, SettingDepth >='"&request.form("SettingDepthfrom")&"')" &_
" AND IIf(('"&request.form("SettingDepthto")&"'&''=''), True, SettingDepth <='"&request.form("SettingDepthto")&"')" &_
" AND IIf(('"&request.form("WaterDepthfrom")&"'&''=''), True, WaterDepth >='"&request.form("WaterDepthfrom")&"')" &_
" AND IIf(('"&request.form("WaterDepthto")&"'&''=''), True, WaterDepth <='"&request.form("WaterDepthto")&"')" &_
" AND IIf(('"&request.form("CasingSizeddb")&"'&''=''), True, CasingSize LIKE'"%&request.form("CasingSizeddb")&%"')" &_
" AND IIf(('"&request.form("CustomerCompanyddb")&"'&''=''), True, CustomerCompany LIKE'"%&request.form("CustomerCompanyddb")&%"')" &_
" AND IIf(('"&request.form("SlipTypeddb")&"'&''=''), True, SlipType LIKE'"%&request.form("SlipTypeddb")&%"')""


Musta copied and pasted wrong...forgot all the rest of the AND's

(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 16:00:57   
OK. I don't know what happened.

When I tried this earlier, it worked perfectly. 
SELECT * FROM LSjob WHERE " &_
" IIf(('"&request.form("TensileLoadfrom")&"'&''=''), True, TensileLoad>='"&request.form("TensileLoadfrom")&"')" &_
" AND IIf(('"&request.form("TensileLoadto")&"'&''=''), True, TensileLoad<='"&request.form("TensileLoadto")&"')" &_
" IIf(('"&request.form("SettingDepthfrom")&"'&''=''), True, SettingDepth >='"&request.form("SettingDepthfrom")&"')" &_
" AND IIf(('"&request.form("SettingDepthto")&"'&''=''), True, SettingDepth <='"&request.form("SettingDepthto")&"')" &_
" IIf(('"&request.form("WaterDepthfrom")&"'&''=''), True, WaterDepth >='"&request.form("WaterDepthfrom")&"')" &_
" AND IIf(('"&request.form("WaterDepthto")&"'&''=''), True, WaterDepth <='"&request.form("WaterDepthto")&"')" &_
" IIf(('"&request.form("CasingSizeddb")&"'&''=''), True, CasingSize LIKE'"%&request.form("CasingSizeddb")&%"')" &_
" IIf(('"&request.form("CustomerCompanyddb")&"'&''=''), True, CustomerCompany LIKE'"%&request.form("CustomerCompanyddb")&%"')" &_
" IIf(('"&request.form("SlipTypeddb")&"'&''=''), True, SlipType LIKE'"%&request.form("SlipTypeddb")&%"')""


Now it doesn't. I get the "Invalid character" error that points to the %

When I try this. 

"SELECT * FROM LSjob WHERE " &_
" IIf(('"&request.form("TensileLoadfrom")&"'&''=''), True, TensileLoad>='"&request.form("TensileLoadfrom")&"')" &_
" AND IIf(('"&request.form("TensileLoadto")&"'&''=''), True, TensileLoad<='"&request.form("TensileLoadto")&"')" &_
" AND IIf(('"&request.form("SettingDepthfrom")&"'&''=''), True, SettingDepth >='"&request.form("SettingDepthfrom")&"')" &_
" AND IIf(('"&request.form("SettingDepthto")&"'&''=''), True, SettingDepth <='"&request.form("SettingDepthto")&"')" &_
" AND IIf(('"&request.form("WaterDepthfrom")&"'&''=''), True, WaterDepth >='"&request.form("WaterDepthfrom")&"')" &_
" AND IIf(('"&request.form("WaterDepthto")&"'&''=''), True, WaterDepth <='"&request.form("WaterDepthto")&"')" &_
" AND IIf(('"&request.form("CasingSizeddb")&"'&''=''), True, CasingSize LIKE'"%&request.form("CasingSizeddb")&%"')" &_
" AND IIf(('"&request.form("CustomerCompanyddb")&"'&''=''), True, CustomerCompany LIKE'"%&request.form("CustomerCompanyddb")&%"')" &_
" AND IIf(('"&request.form("SlipTypeddb")&"'&''=''), True, SlipType LIKE'"%&request.form("SlipTypeddb")&%"')""


I get the Invalid character error pointing to the %

When I remove the %s, I get an Unterminated String Constant Error, pointing to the end of the query.

When I place a " and the end, I get a database results error.

(in reply to dzirkelb1)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 16:36:35   
Your '%' characters must be inside the string :
eg :

LIKE '"%&request.form("SlipTypeddb")&%"')""

Should be :

LIKE '%"&request.form("SlipTypeddb")&"%')""

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 16:41:49   
Yeah, I figured that I would try that, but now I get a Database results error

mismatched parameter delimiters 

<%
fp_sQry="SELECT * FROM LSjob WHERE " &_
" IIf(('"&request.form("TensileLoadfrom")&"'&''=''), True, TensileLoad>='"&request.form("TensileLoadfrom")&"')" &_
" AND IIf(('"&request.form("TensileLoadto")&"'&''=''), True, TensileLoad<='"&request.form("TensileLoadto")&"')" &_
" AND IIf(('"&request.form("SettingDepthfrom")&"'&''=''), True, SettingDepth >='"&request.form("SettingDepthfrom")&"')" &_
" AND IIf(('"&request.form("SettingDepthto")&"'&''=''), True, SettingDepth <='"&request.form("SettingDepthto")&"')" &_
" AND IIf(('"&request.form("WaterDepthfrom")&"'&''=''), True, WaterDepth >='"&request.form("WaterDepthfrom")&"')" &_
" AND IIf(('"&request.form("WaterDepthto")&"'&''=''), True, WaterDepth <='"&request.form("WaterDepthto")&"')" &_
" AND IIf(('"&request.form("CasingSizeddb")&"'&''=''), True, CasingSize LIKE '%"&request.form("CasingSizeddb")&"%')" &_
" AND IIf(('"&request.form("CustomerCompanyddb")&"'&''=''), True, CustomerCompany LIKE '%"&request.form("CustomerCompanyddb")&"%')" &_
" AND IIf(('"&request.form("SlipTypeddb")&"'&''=''), True, SlipType LIKE '%"&request.form("SlipTypeddb")&"%')"""
fp_sDefault="TensileLoadfrom=&TensileLoadto=&SettingDepthfrom=&SettingDepthto=&WaterDepthfrom=&WaterDepthto=&CasingSizeddb=&CustomerCompanyddb=&SlipTypeddb="
fp_sNoRecords="<tr><td colspan=17 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="LSDB"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="JobNumber"
fp_sMenuValue="JobNumber"
fp_iDisplayCols=17
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


I've been through it, but I don't see any.

(in reply to Spooky)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 16:45:06   
The easiest way to do that (as before) is to use :

response.write fp_sQry
response.end

That would go on the line before "fp_sDefault...."

This will write the string so it can be checked for accuracy

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to mesturee)
mesturee

 

Posts: 40
Joined: 9/1/2005
Status: offline

  		RE: "OR" Bullet point to change "AND&quo... - 9/15/2005 16:57:55   
You are so right.

Here is what I got 
SELECT * FROM LSjob WHERE IIf(('0'&''=''), True, TensileLoad>='0') AND IIf(('2000000'&''=''), True, TensileLoad<='2000000') AND IIf(('0'&''=''), True, SettingDepth >='0') AND IIf(('50000'&''=''), True, SettingDepth <='50000') AND IIf(('0'&''=''), True, WaterDepth >='0') AND IIf(('50000'&''=''), True, WaterDepth <='50000') AND IIf((''&''=''), True, CasingSize LIKE '%%') AND IIf((''&''=''), True, CustomerCompany LIKE '%%') AND IIf((''&''=''), True, SlipType LIKE '%%')"


The only odd thing I can see is the extra " at the end. However, when I decrease the number of "s at the end of the query by one, I get an Unterminated string constant error.

(in reply to Spooky)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> "OR" Bullet point to change "AND" query 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.