Way to count specific value frequency in recordset?

looping an rs with "+1" to variable when a certain value appears

[evilracecarlive]

After a recordset is created with user input, I need to be able to count how many times a specific value appears in one of the columns.

The db from which the rs is queried contains a column called PageName with a text value, like default.asp

I have tried the below code, but it doesn't seem to work:

dim default
 default=0
While (Not rs.EOF) and (rs.Fields("PageName").Value = "default.asp")
 default = default + 1
 rs.MoveNext
Wend

This response.write always returns a zero value.

response.write "Matches: " & default & " "

Can someone tell me where I am going wrong? Am I missing some syntax or something? I am not getting any returned errors and I am very new to ASP so any help would be appreciated.

In case it is relevant, the db is Access 2003 with an ADODB connection. I am using Dreamweaver CS4.

Thank you!

[marcel]

If I recall correctly you need to reset the RecordSet after reading it, if you have already looped through the recordset you will need to start at the beginning with:

rs.MoveFirst
While (Not rs.EOF)...

[evilracecarlive]

Hi marcel, thanks for your reply.

I already had the rs.MoveFirst slightly earlier in the code, closer to the actual opening of the rs. I moved it right above the While statement just in case, but still no luck. the value still returned zero.

Any other thoughts or ideas?

[defanjos]

Look into GetRows. That should help you.
Do a search for "GetRows", and you'll get all kinds of info.

[evilracecarlive]

Hi defanjos,

i am familiar with .GetRows and am using it elsewhere, the problem is that the only way I know how to use it is to get the total # of records from the rs, not counts on an individual value. I would like to avoid running multiple queries to the db for each individual value of "PageName" that I need counts for.

is there a way to use GetRows to count specific values from a single rs?

Thanks for your help

[defanjos]

Even without using GetRows, would't something like this work?

dim default
default=0
While (Not rs.EOF)
if rs.Fields("PageName").Value = "default.asp" then
default = default+1
end if
rs.MoveNext
Wend

response.write default

[evilracecarlive]

thanks for the suggestion. i tried it with the conditional statment inside of the

While (Not rs.EOF)

, but I still got a zero result.

maybe its a problem with my While statment... but it's used other places in the code without any problems...

[defanjos]

what about:

dim default
default=0

Do While Not rs.EOF

if rs.Fields("PageName").Value = "default.asp" then
default = default+1
end if

rs.MoveNext
loop

[defanjos]

Also, make sure the value of "PageName" is exactly "default.asp", no spaces to the right or left (i.e. " default.asp").
When inputting in DB, use trim to get rid of spaces before or after.

[evilracecarlive]

It works!

Thank you for your help defanjos.

The data is placed in the db by an INSERT statement that had extra spaces in the code... as a result the values had a leading and trailing space. I fixed the INSERT and ran a trim() in access and replaced my values and now it actually counts. The ASP that I am using now for counting is:

dim default 
default=0 
While Not rsURLIDgr.EOF
if rsURLIDgr.Fields.Item("PageName").Value = "default.asp" then 
default = default+1 
end if 
rsURLIDgr.MoveNext 
Wend

Thanks again for your help!

[defanjos]

glad I could help