Hi
Could any one suggest me, how can I choose the variable. I have got my database, one Culumn, name is Region, which contains, East london, North London, West London so on.
In SQL what will be my default value?
I want to keep one option which is, if any one choose Any Region, it should come up with all records.
Please, Suggest me what will be the default value?
Thanks


Mashkur

Edited by - mash07 on 13 Feb 2003 00:49:28

try
Select *
From dbo.whatever

this will return all the files in your DB. is this what you are looking for?

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
try
Select *
From dbo.whatever

this will return all the files in your DB. is this what you are looking for?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Thanks for reply my topics. Actually this is not what I am looking for, lets make it an example: I have two drop down menu (list), one is Price and another one is Region. If I choose Any Price, from East London, it show me the perfect result, and also if I select a single price it aslo does work, my question is, if any user select ANY PRICE from ANY REGION the result should come up with all the records, isn't? I set the Price value 1, which is working fine, but what will be my ANY REGION Value, (eg: Region value for other..is East London value is East London, North London value is North London so on) in this case, what should be my Any Region Value? and also what will be the SQL command.
Here are the SQL and Variables:

SELECT *
FROM sales
WHERE Price MMColParam AND Region='salesRegion'(note: this is the list menu name)
ORDER BY Price ASC

Variables:
Name: MMColParam, Default value: 1, Run Time Value: Request.Form("salesPrice"
Name: salesRegion, Default value: ???, Run time Value: Request.Form("salesRegion"

The question mark, here is the problem, what should I select here, for the ANY REGION.

I think, it will help to understand.
Looking forward from any friend.
Thanks

Mashkur

Edited by - mash07 on 13 Feb 2003 23:39:00

Hi
I checked on MM site and found a simple solution which used on it and works fine, thats it.
I got mistake actualy, it should be value %, and in SQL I need to add Like command insteed of = sign.
Now another error problem, when I click for next 5 offset, it showes me an error,
like: Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Price 1 AND Region Like '%''.
/result.asp, line 27
URL Pass parameter is like: gungchill/result.asp?salesRegion=%25&salesPrice=BETWEEN+350000+AND+499000&Submit=Search&offset=5
please, any friend, help me.

Edited by - mash07 on 14 Feb 2003 02:13:01

somewhere in this thread is a really strange remark...

SQL Server works quite happily with =.

I only read the last message and i spotted the problem and i didn't even have to read the first messages, except to get your query statement, change it to:

SELECT *
FROM sales
WHERE Price = MMColParam AND Region='salesRegion'
ORDER BY Price ASC

that is all, you forget to specify what price should be, hence the missing operator error. = is the missing operator here, should work fine now.

price 1 returns error, price = 1 works, price like 1% will also work, price like %1% will keep working only each variation will return different results. You were right to put the % as default value for salesregion

if it still won't work and returns a BOF/EOF error then you need to setup a show if recordset is empty and stick your results in a show if recordset is NOT empty.

HTH

with regards,

Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Edited by - djvgalen on 14 Feb 2003 22:16:19

Hi
This is great that atleast some one trying to help me...thanks a lot.
I tried according your suggestion, it doesn't work, not even any records, any price.
So previously I used Like command:

SELECT *
FROM sales
WHERE Price MMColParam AND Region Like 'salesRegion'
ORDER BY Price ASC

And value: Price: 1
Region: %

Ok, when I select Any Region and Any Price it brings all the result, when I select Any Region and a particular Price, it brings the particular row. Absolutely right info, what was I am looking for.
Now the problem is, at the bottom I have got Recordset Paging, when I select for Next...it showes me this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Price 1 AND Region Like '%''.
/result.asp, line 27

Now what should I do?
Please, Help me.
mash


Mashkur

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
query expression 'Price 1 AND Region Like '%''.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

and as i mentioned in my above post:

you forget to specify what price should be, hence the missing operator error.
= is the missing operator here. Itshould work fine after you change your sql to include the operator in the first condition of your query.

in your SQL change Price MMcolparam to Price = MMcolparam

i type too much, i guess.

but this:
Price 1 AND Region Like '%'
must change into this:
Price = 1 AND Region Like '%'
or it will never work.

always happy to help out, i had to learn too and found i got most usefull responces on this website.

with regards,

Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Hi
Boss, I am realy sorry, its just makes me confuse, I tried like this

Price = 1 AND Region Like '%' but MX doesn't allow this to input this kind of SQl, actually I made it confuse. I am not sure how the code will be look like. I am very nobis in this area. Could you just explain a bit more,

SELECT *
FROM sales
WHERE Price= MMColParam AND Region Like 'salesRegion'
ORDER BY Price ASC

current SQL is like this, but it doesn't work anything.
Looking for furthure suggestion
Thanks

Mashkur

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Boss, I am realy sorry, its just makes me confuse, I tried like this

Price = 1 AND Region Like '%' but MX doesn't allow this to input this kind of SQl, actually I made it confuse. I am not sure how the code will be look like. I am very nobis in this area. Could you just explain a bit more,

SELECT *
FROM sales
WHERE Price= MMColParam AND Region Like 'salesRegion'
ORDER BY Price ASC

current SQL is like this, but it doesn't work anything.
Looking for furthure suggestion
Thanks

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

I am nobody's boss, except of myself.

just change your code in advanced recordset window so that it reads:

SELECT *
FROM sales
WHERE Price = MMColParam AND Region Like 'salesRegion'
ORDER BY Price ASC

the webserver will then translate this to:

SELECT *
FROM sales
WHERE Price = 1 AND Region Like '%'
ORDER BY Price ASC

and sends that to the database.
which is going to check each row in the table sales for a price = 1, if this is true then it also looks for a region Like %, if, however, price = 1 is not true then it skips to the next row (record) in the table. When both conditions are true it returns the record in the query and finally the database will sort the records ascending on price, that is
1
2
3.

I am not sure what else there is to explain about this particular query.

SQL is fun though.
except when your query spans 2 (or more) sheets of paper when printed and it returns a error somewhere...

with regards,

Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Hi,
After trying 100 times, I couldn't make it possible...its really annoying me..I don't know...I follow adjactly what you said, here...if I use = sign then it doesn't show anything, not a single data. I tried with ANY REGION and ANY PRICE combination, noooooopppp.
And also Any Region with a selected Price.....
Is there anything that I have made wrong in Price value.....
What I did is....for the Price drop down menu...
Item Label 1: Any Price and value: BETWEEN 0 AND 2000000
Item Label 2: £0 - £99,000 and value: BETWEEN 0 AND 99000
Like so on.
Now whats next?
Many thanks


Mashkur

Mashkur,

can you give me your code of the form with the lists ?

This looks like the problem:

value: BETWEEN 0 AND 2000000

if you actually use this then you will get nowhere, because this will send the following SQL to the database:

SELECT *
FROM sales
WHERE Price = BETWEEN 0 AND 2000000 AND Region Like '%'
ORDER BY Price ASC

That will never work, it should be something like this:

SELECT *
FROM sales
WHERE Price =&gt; 0 AND Price &lt;= 2000000 AND Region Like '%'
ORDER BY Price ASC

So you need to change the value in your list that now reads:
BETWEEN 0 AND 2000000

into:
=&gt; 0 AND Price &lt;= 2000000

And for BETWEEN 0 AND 99000 that becomes =&gt; 0 AND &lt;= 99000
and so on, also if there are values like 1 (which was in the operator error you mentioned) then you need to hardcode the operator into your listbox, so 1 becomes Value: = 1.

Dennis