[RESOLVED] why recordcount returns -1?

[abdbuet]

Why I get recordcount=-1 in the following program?

Call connectionOpen
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM daily_list_cell WHERE show='Yes'"
    rs.Open strSQL,connection
    MsgBox rs.RecordCount
    rs.Close
    Set rs = Nothing
    Call connectionClose

[vksingh24]

I guess following things need to done
1) Attach a connection to the recordset.
rs.Open strSQL, CONNECTION_NAME

2) You need to set the cursorType and LockType for recordset.

3) Check your sql query before running to through ADO

[abdbuet]

I am sorry for mistake in the post. I did add Connection_name in rs.open (now i corrected that in previous post)

Now my question is it really necessary to add cursorType and LockType to get recordcount?

And I run the sql query in Access and it successfully show 16 records. So nothing wrong in sql.

[vksingh24]

You need to set the rs.CursorLocation = adUseClient property.

It's not necessarry to set the CursorType and LockType, but you need to set the CursorLocation property to get the recordcount.

[anhn]

Read this:

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks — that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True — this value will be the exact number of records in the Recordset, regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

The cursor type of the Recordset object affects whether the number of records can be determined. 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.

'Try 
   rs.MoveLast
'before access Recordcount 
   Msgbox rs.RecordCount
'problem usually happens with queries, not likely with tables

[vksingh24]

Thanks.

Now, you understand why did i said in my first post to set the CursorType, LockType property.

In my second post assumed you have set up the CursorType and LockType property correctly,

Reason for the not returning recordcount could be any wrong property setting.

Any way you solved your problem...

[abdbuet]

thanx guys .. This is why I love this forum becoz I got detail answer . Btw, In a previous project, I did not use cursor location in any form (and I didn't require recordcount property). That software is used by many people and the Database (Access) resides on a server. Does this create any problem in future for not using cursor location?

[si_the_geek]

That's not a problem at all - in fact it is better to leave the CursorLocation as the default (Server), as it is faster, uses less memory (well actually for Access it doesn't, but it does for most DBMS's!), and also gives fewer problems in terms of locking etc when the database is used by multiple people at the same time.

You will probably find useful info in the FAQ article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[leinad31]

Another way to get the count is with an aggregate query. That method may or may not suitable to your needs.