recordcount returning -1 when there are 50 records 
Author Message
 recordcount returning -1 when there are 50 records

Hi Matt,
Recordcount is an unreliable way of finding how many
records are in a recordset.
One way of getting an acurate record count out of a
recordset is to loop through it and increment a integer
variable for each loop, like this...
oRec.MoveFirst
dim i as integer
i = 0
do while not oRec.EOF
   i = i + 1
   oRec.MoveNext
Loop
msgbox i

There are other ways, but this one is pretty foolproof,
and will have a negligable impact on performance unless
you are dealing with thousands of records.
HTH
Ed Voss
MCP

Quote:
>-----Original Message-----
>Brand new to ADO.. just got the SDK and I'm fumbling

through it. Why would
Quote:
>the following return -1?

>Dim oCon, oRec

>set oCon = CreateObject("ADODB.Connection")
>set oRec = CreateObject("ADODB.RecordSet")

>'con.Open "Driver={SQL Server};Server=" & SERVER

& ";Database=" & DATABASE &
Quote:
>";Trusted_Connection=yes;"

>oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass"
>'oRec.Open "delete from " & TABLE & " where portcd = ' "
& portcode & " ' ",
>con, adOpenStatic

>oRec.Open "select * from FVAL_HOLD where PORTCD

= '051504' and PROFILE_ID =
Quote:
>107 ", oCon

>msgbox oRec.recordcount

>oRec.close
>Set oRec = Nothing
>Set oCon = Nothing

>I'm working in Windows Script right now..

>I read the deal about using .movelast before

calling .recordcount.. I tried
Quote:
>that but no luck. I've also tried different cursors and
such.. maybe I just
>didn't get the right combo?

>This Query works fine when I run it from WinSQL and
returns 50 records.

>TIA

>Matt

>.



Sun, 08 May 2005 08:03:31 GMT  
 recordcount returning -1 when there are 50 records
Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would
the following return -1?

Dim oCon, oRec

set oCon = CreateObject("ADODB.Connection")
set oRec = CreateObject("ADODB.RecordSet")

'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE &
";Trusted_Connection=yes;"

oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass"
'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " ' ",
con, adOpenStatic

oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID =
107 ", oCon

msgbox oRec.recordcount

oRec.close
Set oRec = Nothing
Set oCon = Nothing

I'm working in Windows Script right now..

I read the deal about using .movelast before calling .recordcount.. I tried
that but no luck. I've also tried different cursors and such.. maybe I just
didn't get the right combo?

This Query works fine when I run it from WinSQL and returns 50 records.

TIA

Matt



Sun, 08 May 2005 06:23:26 GMT  
 recordcount returning -1 when there are 50 records
Matt

Apart from Ed's solution, you can try as follows

    set the cursortype to adOpenStatic or adOpenKeyset and get the
recordcount
    (as only client side recordsets or keyset cursors return the correct
number of rows).

   oRec.Open strSql, cn, adOpenKeyset, adLockOptimistic, adCmdText
    or
   oRec.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText

   textbox.text=oRec.RecordCount

Sukesh


Quote:
> Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would
> the following return -1?

> Dim oCon, oRec

> set oCon = CreateObject("ADODB.Connection")
> set oRec = CreateObject("ADODB.RecordSet")

> 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE
&
> ";Trusted_Connection=yes;"

> oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass"
> 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " '
",
> con, adOpenStatic

> oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID
=
> 107 ", oCon

> msgbox oRec.recordcount

> oRec.close
> Set oRec = Nothing
> Set oCon = Nothing

> I'm working in Windows Script right now..

> I read the deal about using .movelast before calling .recordcount.. I
tried
> that but no luck. I've also tried different cursors and such.. maybe I
just
> didn't get the right combo?

> This Query works fine when I run it from WinSQL and returns 50 records.

> TIA

> Matt



Sun, 08 May 2005 12:52:40 GMT  
 recordcount returning -1 when there are 50 records
Set the cursor location to 3 or adUseClient and you'll get your recordcount.

oRec.Cursorlocation=3
oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID =
107 ", oCon
 msgbox oRec.recordcount

HTH


Quote:
> Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would
> the following return -1?

> Dim oCon, oRec

> set oCon = CreateObject("ADODB.Connection")
> set oRec = CreateObject("ADODB.RecordSet")

> 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE
&
> ";Trusted_Connection=yes;"

> oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass"
> 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " '
",
> con, adOpenStatic

oRec.Cursorlocation=3
oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID =
107 ", oCon
 msgbox oRec.recordcount

- Show quoted text -

Quote:

> oRec.close
> Set oRec = Nothing
> Set oCon = Nothing

> I'm working in Windows Script right now..

> I read the deal about using .movelast before calling .recordcount.. I
tried
> that but no luck. I've also tried different cursors and such.. maybe I
just
> didn't get the right combo?

> This Query works fine when I run it from WinSQL and returns 50 records.

> TIA

> Matt



Sun, 08 May 2005 13:50:02 GMT  
 recordcount returning -1 when there are 50 records
Matt,

RecordCount -1 means that provider cannot determine actual record count
based on current setting of cursor. Usually (but not all) server side
cursors cannot return that information. Rules for that quite simple: the
RecordCount property will return -1 for a forward-only cursor; the actual
count for a static or keyset cursor; and either -1 or the actual count for a
dynamic cursor, depending on the data source.What you need to do is to
change settings of your cursor to state, which would allow to get
recordcount and will be suitable for your application.
Also check next KB about RecordCount

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194973

You need to do that ONLY in case if you really need to get record count. If
you just need to find out if recordset has any records, then you could use
EOF property right after you opened reordset

If Not MyRecordset.EOF then
'there are some records in recordset
else
'there are no records in recordset
endif

Another way to find actual record count is to use COUNT function in your
SELECT statement

SELECT COUNT(*) AS RESULT WHERE ....

it will return 0 or actual count any time when you call it

--
Val Mazur
Microsoft MVP


Quote:
> Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would
> the following return -1?

> Dim oCon, oRec

> set oCon = CreateObject("ADODB.Connection")
> set oRec = CreateObject("ADODB.RecordSet")

> 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE
&
> ";Trusted_Connection=yes;"

> oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass"
> 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " '
",
> con, adOpenStatic

> oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID
=
> 107 ", oCon

> msgbox oRec.recordcount

> oRec.close
> Set oRec = Nothing
> Set oCon = Nothing

> I'm working in Windows Script right now..

> I read the deal about using .movelast before calling .recordcount.. I
tried
> that but no luck. I've also tried different cursors and such.. maybe I
just
> didn't get the right combo?

> This Query works fine when I run it from WinSQL and returns 50 records.

> TIA

> Matt



Sun, 08 May 2005 21:08:11 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. 50 cent bring em in | 50 cent brings out jim jones | 50 cent broke

2. 50 users running Access, am I crazy.

3. Need to Display 50 records from a data-recordset

4. Help!!!! Msflexgrid show 50 records at a time

5. Help: retrieve LAST record on database of 50.000 record

6. I am trying to update a record, i am not using data control

7. I am trying to update a record, i am not using data control

8. I am getting duplicate records, I mean everything is duplicate even Access record number

9. DAO Recordset returning incorrect recordcount

10. How can I combine 50 one-page docs into one 50 page doc?

11. Can't returns RecordCount using ADO

12. HELP--Recordcount keeps returning -1

 

 
Powered by phpBB® Forum Software