| Author |
 Topic  |
|
|
kolucoms6
Average Member
845 Posts |
 Posted - 05 May 2007 : 16:23:41
|
sql = "SELECT Count(*) as RCount FROM ABC"
RS.Open sql, session("cn")
RS.Filter="Sponsored=1"
SponCount=RS("RCount")
Above code doesnt work.It says that
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
Whereas I have a column named "Sponsored".
Is it by any chance related with Count(*) and Filter ??
|
Edited by - kolucoms6 on 05 May 2007 16:24:09 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 05 May 2007 : 16:55:17
|
| Your select will return a single value (the result of count(*)) as a single row, single column recordset, with no field 'sponsored' - I'm a bit rusty but I think that's what's going on... |
 |
|
|
kolucoms6
Average Member
845 Posts |
Posted - 05 May 2007 : 17:01:59
|
Any way to filter it , but not within query ? |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 05 May 2007 : 17:24:21
|
Maybe something like
sql = "SELECT <primary key>, [Sponsored] FROM [ABC]"
RS.Open sql, session("cn")
RS.Filter="Sponsored=1"
SponCount=RS.rowcount()
You'll have to check the syntax, but something like that ought to do it (although less efficient than doing it in the SQL)
Also, I don't like the idea of keeping a connection object inside the session object, can't remember why, but I remember it was bad form, certainly back in the early days... |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 05 May 2007 : 17:39:10
|
Is it better to open a diffrent recordset everytime and get the total count with different where clause ?
OR
Use a query like this :
sql = "SELECT <primary key>, [Sponsored],[Col2],[Col3] FROM [ABC]"
and then running Filter , RS.Filter with filter value??
I guess SECOND one... |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 05 May 2007 : 17:53:14
|
The high-cost item is the retrieval of the recordset from the db, so get the data once then use your rs.filters is almost certainly quicker once you have the data.
Hope it helps :) |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 05 May 2007 : 17:55:08
|
| Thanks a lot for very quick response. |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 05 May 2007 : 17:57:40
|
Last quick question :
How to "clear" the filter after assigning it ? |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 05 May 2007 : 19:59:31
|
Don't know the official line, may be explained on msdn.microsoft.com or try .filter=""!
P |
|
|
| |
Topic |
|
Topic Locked
