| Author |
 Topic Search  Topic Options
|
ldiuf 
Groupie
Joined: 17 October 2001
Location: United States
Status: Offline
Points: 49
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Keeps Returning a Value of 0
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
|
 |
FLATLINE
Groupie
Joined: 08 July 2002
Location: Israel
Status: Offline
Points: 142
|
Post Options
Thanks(0)
Quote Reply
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
|
|
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
FLATLINE
Groupie
Joined: 08 July 2002
Location: Israel
Status: Offline
Points: 142
|
Post Options
Thanks(0)
Quote Reply
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 
|
|
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
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
|
|
|
|
Flamewave
Senior Member
Joined: 19 June 2002
Location: United States
Status: Offline
Points: 376
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
ldiuf
Groupie
Joined: 17 October 2001
Location: United States
Status: Offline
Points: 49
|
Post Options
Thanks(0)
Quote Reply
Posted: 24 September 2003 at 8:43am |
|
Thank GOD that this forum exists!!!
|
|
3BEPb
Groupie
Joined: 07 August 2003
Location: United States
Status: Offline
Points: 81
|
Post Options
Thanks(0)
Quote Reply
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
Edited by 3BEPb
|
|
