...

View Full Version : how I get a recordset that contains only records with Price > $90.00 ?



lse123
07-10-2007, 08:05 PM
ado/sql
how to program
a table has ProductName and Price fields
how I get a recordset that contains only records with Price > $90.00 ?

Daemonspyre
07-10-2007, 08:07 PM
SELECT ProductName, Price FROM yourTable WHERE Price > '90.00'

lse123
07-10-2007, 08:14 PM
the x = "SELECT ProductName, Price FROM yourTable WHERE Price > '90.00'" may included in oRS.Open x, oConn ....is correct with WHERE ? how to sort same time by name or price ?

Daemonspyre
07-10-2007, 08:29 PM
oRS.Open "SELECT ProductName,Price FROM yourTable WHERE Price > '90.00'", conn

Yes, you can use oRS.Open with WHERE statements. You can do any valid SELECT SQL code with oRS.Open.


TO ORDER BY Price (use ASC for Ascending order, use DESC for Descending order, removing the | character)

...WHERE Price > '90.00' ORDER BY Price ASC|DESC

TO ORDER BY ProductName (use ASC for Ascending order, use DESC for Descending order, removing the | character)

...WHERE Price > '90.00' ORDER BY ProductName ASC|DESC

lse123
07-11-2007, 07:53 AM
ANOTHER FILTER
THIS can be done ?

strFilter = "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'"
oRS.Filter = strFilter

lse123
07-11-2007, 07:59 AM
how to use "....ORDER BY Price ASC|DESC..." with the other way using

oRS.Sort = strSorting
oRS.Filter = strFilter

strSorting=?
strFilter=?

Daemonspyre
07-11-2007, 01:02 PM
You can use the ORDER BY with the filter, as the 2 are unrelated. However, the .Sort method won't help you, as the sorting should be done on the SQL server.

However, you should not use the ADO .Filter, as this can easily be accomplished with SQL. Putting this into the ADO .Filter method is more work on the Web server which should only be handing out documents, not parsing SQL.

lse123
07-11-2007, 07:08 PM
ANOTHER FILTER
THIS can be done ?

strFilter = "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'"
oRS.Filter = strFilter

glenngv
07-13-2007, 06:53 PM
SELECT ProductName, Price FROM yourTable WHERE Price > '90.00'
The price value should have no quotes.

SELECT ProductName, Price FROM yourTable WHERE Price > 90.00 order by Price
If 90.00 is in a variable.

sql = "SELECT ProductName, Price FROM yourTable WHERE Price > " & amount & " order by Price"

lse123
07-13-2007, 07:14 PM
what about compare two fields together, can be done ?
eg.
strFilter = "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'"

glenngv
07-13-2007, 08:02 PM
Try:

strFilter = "UnitsInStok < ReorderLevel"
oRS.Filter = strFilter
Links on using Filter.
http://www.asp101.com/tips/index.asp?id=86
http://www.devguru.com/technologies/ado/QuickRef/recordset_filter.html

lse123
07-14-2007, 02:30 PM
"UnitsInStok < ReorderLevel" these are from the table record ? They do not need oRS like "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'" ?

glenngv
07-14-2007, 08:33 PM
Yes. Did you try it? Filters are just exactly like the conditions you put in the WHERE clause. Go read the links I posted in my previous post.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum