SQL Query always returns a '-1' RecordCount 1

[LegoPiece]

I remember getting this problem a couple of years ago using DAO, but a) It's reared it's ugly head in ADO now, and b) I can't remember the darn fix!

Any thoughts?

Call adorsRecordSet.Open(SQLQueryString, mconnConnection)
MsgBox "RecordCount = " + str(adorsRecordSet.RecordCount)

Yep, it doesn't matter how many records are present, I always get a '-1' RecordCount!

Thanks
LeGo PiEcE

 

[LegoPiece]

Oh yes, I remember the fix now... it's not really a fix, but a 'workaround', but it does the job! It'd still be interesting to know why it does that in the first place - so any answers are always appreciated!

 

[vijayab]

If the cursortype and locktype are not set, the default cursortype = adForwardonly and locktype=adreadonly.
With a forwardonly cursor you cannot move back and forth, and this goes only in the forwardonly direction.

For getting a recordcound, it should be able to hit the last record and then get the count.

To get a recordcount, the cursortype can be anything except for adForwardonly.

Hope this helps.

 

[LegoPiece]

Hey Vijay - I was ALWAYS wondering about that! Thanks for helping me out - I can tell you that this will be something that won't ever escape me again!
Thanks!
LeGo PiEcE

"The Computer Spock! Destroy it!" - Captain James T. Kirk

 

[HebieBug]

Have the same problem at the moment..
My code is as follows
Form1.Cls
Dim Y As Integer
Dim strSql As String
Dim aMainrecord As New ADODB.Recordset
Dim objConn As New ADODB.Connection
aMainrecord.CursorType = adOpenStatic
aMainrecord.CursorLocation = adUseClient
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\ctsdata.mdb"
strSql = "Select * from User_details2 WHERE LastName LIKE '" & Me.Text1 & "%'"
Set aMainrecord = objConn.Execute(strSql)

Y = aMainrecord.RecordCount
aMainrecord.MoveFirst
While Not aMainrecord.EOF
Form1.Print aMainrecord("Lastname&quot
aMainrecord.MoveNext
Wend
aMainrecord.Close
Set aMainrecord = Nothing
Set objConn = Nothing

It will produce a -1 value in the record count
Anyone have ideas as I made sure of the changes being correct... But still it will not work for me

 

[TimLarkin]

use aMainrecord.MoveLast before the aMainrecord.MoveFirst
statement.

 

[LegoPiece]

Tim,

Thanks for the help also! A question though - is this a bug with recordsets in general, or is this how we're supposed to use them? It does seem a little weird though don't you think?
Thanks!
LeGo PiEcE

"The Computer Spock! Destroy it!" - Captain James T. Kirk

 

[Ologhai]

to get the recordcount, open your recordset with Cursortype = adOpenStatic

if you open with adOpenDynamic you will not get the recordcount but -1.

_____________________________
Hugues Gauthier P.A,DEC
Programmeur Analyste
Progitech GS Inc.

http://www.progitech.com/

hugues.gauthier@progitech.com
hugues_gauthier@hotmail.com
Tel. : (418) 698-4383 #19
Fax.: (418) 698-8633
---------------------
ICQ: 2151800

 

[HebieBug]

For anyone with this problem Microsoft have a page for it

http://support.microsoft.com/support/kb/articles/q194/9/73.asp

The only thing is it still will not work for me
Have tried all the cursor types, CursorLocation and LockType in different combinations...
Microsoft's site also discusses that these options have to be set a certain way for it to work...
Anyone have some further ideas with with...
Lego Peice Microsoft don't say it's a bug but they use the word 'work around'.... Nice way of putting it

 

[HebieBug]

OK so had to do some remodeling of the code. It does not seem to want to produce anything but -1 no matter what changes you make to the connection types ect...
This way worked for me
Private Sub Form_Load()
'set up rs
Dim strSql As String
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
strSql = "Select * from User_details2 WHERE &_ LastName LIKE '" & Me.Text1 & "%'"
rs.Open strSql, "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data &_ Source=c:\CTSDATA.mdb", adOpenStatic, adLockUnspecified
Debug.Print rs.RecordCount
MsgBox "number" & rs.RecordCount
End Sub


Happy hunting

 

[petermeachem]

It's very simple. No recordset, snapshot, dynaset or anything else knows when it is opened how many records there are. You must move to the end first so
thing.movelast
correctanswer = thing.recordcount

If you just want the number of records, or the recordset is huge (and will therefore take ages to get to the end) do

set thing = db.open etc ( SELECT Count(stuff) AS TheTotal ...)
correctanswer = thing("TheTotal&quot

 

[chiph]

LegoPiece -

I would do as petermeachem suggests and use the Count() function -- It's super-efficient compared to bringing back the entire recordset over the network, as the counting ocurrs on the database server, not on the desktop.

So, if your query were to bring back 1,000 rows at 500 bytes each, that's half a megabyte (at least) transmitted. With the Count() function, it gets reduced to 500 bytes or less, counting overhead.

Chip H.

 

[RealQuiet]

HerbieBug,
I see that you have
aMainrecord.CursorLocation = adUseClient
where aMainrecord is your recordset object.
Instead, set the CursorLocation of the Connection object to adUseClient of the connection object before you open the connection. Give that a try, and you should see correct recordcount results.

 

[mohmehran]

Hey guys, keep in mind that:

1- The recordset should be client-side, so that the client knows about the number of rows.

*** 2- Execute method of Connection and Command objects always return a forward-only, read-only recordset, even if you assign the return value to a static (,etc) recordset. So use Open method of Recorset object instead.
Mohammad Mehran Nikoo, MCP
mohmehran@yahoo.com