Classic ASP Discussion - Keeps Returning a Value of 0

Print Page | Close Window

Keeps Returning a Value of 0

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=5917
Printed Date: 31 March 2026 at 1:17am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Keeps Returning a Value of 0

Posted By: ldiuf
Subject: Keeps Returning a Value of 0
Date Posted: 23 September 2003 at 12:34pm

Can someone please look at this? This just keeps giving me a count of 0 when I know that there are at least 10 that should be showing.

'--- establish access connection
Sub OpenDB (ByRef con, d)
DSN ="ConversionDB_DSN"
Set con = Server.CreateObject("ADODB.Connection")
con.Open DSN
End Sub
'--- end establish access connection

OpenDB con, "Admin"
Set type = "Yes"
    icount = 0
SQL = "SELECT * FROM tblConversions Where fldConverted='" & type & "'"
Set rs = con.Execute(SQL)
i = rs.RecordCount
     Do While icount < i And not rs.EOF
    icount = icount+1
   rs.MoveNext
Loop
Response.write "" & icount & "" & vbcrlf

Thanks In Advance,

Larry

Replies:

Posted By: FLATLINE
Date Posted: 23 September 2003 at 1:01pm

I'm not sure but I think you need to set a special LockType for the recordset if you're gonna use RecordCount.

Either way, it's not really needed anyway. You can (and should) do it without the i counting, like this:

Set rs= con.Execute(SQL)
While NOT rs.EOF
icount = icount+1
rs.MoveNext
WEND
Response.Write "" & icount & "" vbcrlf

-------------
Visit my site: http://darkgreen.service-club.net - DarkGreen HQ

Posted By: michael
Date Posted: 23 September 2003 at 1:57pm

If all you do is counting why don't you just do a
Select Count(*) as noofrecords FROM tblConversions Where fldConverted='" & type & "'"

This just retrurns one value, the amount of records to your criteria, much faster the looping through the whole junk.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: FLATLINE
Date Posted: 23 September 2003 at 2:30pm

Michael, I'm glad you've mentioned that SQL statement because I'm a bit confused about how it works... How exactly will I be able to use the counted number later on? Is the statement simply creating a variable named noofrecords and puts the count value in it? Or do I have to access the variable through the recordset, like rs("noofrecords") ?

Hehe, sorry for stealing the topic, Idiuf

-------------
Visit my site: http://darkgreen.service-club.net - DarkGreen HQ

Posted By: michael
Date Posted: 23 September 2003 at 2:55pm

rs("noofrecords") As count only returns one record you can skip the looping and such. And yes, it just returns one integer value

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Flamewave
Date Posted: 24 September 2003 at 7:14am

Just an FYI, if you need to return the record count along with a bunch of records, you can do it like this:

SELECT (SELECT Count(*) FROM table WHERE column1 = 1) AS NumRecords, column1, column2 FROM table WHERE column1 = 1 ORDER BY column2 ASC

this will return a recordset like this:

NumRecords   column1   column2
3           ;        1           ;   0
3           ;        1           ;   6
3           ;        1           ;   8

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: ldiuf
Date Posted: 24 September 2003 at 8:43am

Thank GOD that this forum exists!!!

Posted By: 3BEPb
Date Posted: 26 September 2003 at 12:20am

FLATLINE wrote:

I'm not sure but I think you need to set a special LockType for the recordset if you're gonna use RecordCount.

Problem with Count(*) has been resolved, as I see, but FYI I usually use following locks, when I want recordset to do whatever I want (

RS.Open "SELECT bla_bla_regular_select_statement",,adOpenStatic,adLockReadOn ly

Posted By: 3BEPb
Date Posted: 26 September 2003 at 12:22am

Damn... adLockReadOnly without blankspace, inserted by this forum script, probably to split flood.... :)