"OR" Bullet point to change "AND" query (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

mesturee -> "OR" Bullet point to change "AND" query (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 -> RE: "OR" Bullet point to change "AND" query (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



mesturee -> RE: "OR" Bullet point to change "AND" query (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



mesturee -> RE: "OR" Bullet point to change "AND" query (9/13/2005 18:12:06)

Nevermind. [:D]

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.



mesturee -> RE: "OR" Bullet point to change "AND" query (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.



dzirkelb1 -> RE: "OR" Bullet point to change "AND" query (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.



mesturee -> RE: "OR" Bullet point to change "AND" query (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



dzirkelb1 -> RE: "OR" Bullet point to change "AND" query (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 :)



mesturee -> RE: "OR" Bullet point to change "AND" query (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



dzirkelb1 -> RE: "OR" Bullet point to change "AND" query (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



mesturee -> RE: "OR" Bullet point to change "AND" query (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.



dzirkelb1 -> RE: "OR" Bullet point to change "AND" query (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



mesturee -> RE: "OR" Bullet point to change "AND" query (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.



Spooky -> RE: "OR" Bullet point to change "AND" query (9/15/2005 16:36:35)

Your '%' characters must be inside the string :
eg :

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

Should be :

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



mesturee -> RE: "OR" Bullet point to change "AND" query (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.



Spooky -> RE: "OR" Bullet point to change "AND" query (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



mesturee -> RE: "OR" Bullet point to change "AND" query (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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375