sql server recordcount return false(pls help)

[PinoyDev]

Hi good day.!

Im so confused why my code to get the total records in a certain table in sql server returns false. Im already connected to sql server and set-up a table named "loanlist".This table contains 10 records. But when i want to get the recordcount, it will returned to -1. Please help. My code below.

Sub loadloanlist()

dim totalrowintbl as integer

objCommand.ActiveConnection = strConnection

objCommand.CommandText = "Select * from loanlist"
objCommand.CommandTimeout = 600
objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute

 totalrowintbl= objRS.RecordCount

msgbox totalrowintbl

   objRS.Close
   Set objCommand = Nothing
   Set objRS = Nothing
   
end sub

Pls help guys!.Thank you for your time.

[vb5prgrmr]

This is because of one of the quirks of ADO. -1 means that it has found records but does/did not get an accurate count. A couple of ways to get the accurate count and to test to see if you have records are as follows...

If adoRs.RecordCount <> 0 And adoRs.BOF = False And adoRs.EOF = False Then
  'then we have records
  adoRs.MoveLast
  MyRecordCount  = adoRs.RecordCount
  aodRs.MoveFirst
  '....
Else
  '...
End If

or you can explicitly get the count of records through a count query...

strSQL = "SELECT COUNT(*) AS THECOUNT FROM tablename 'where...
'execute...
MyCount = adoRs.Fields("THECOUNT")

Good Luck

[PinoyDev]

This is because of one of the quirks of ADO. -1 means that it has found records but does/did not get an accurate count. A couple of ways to get the accurate count and to test to see if you have records are as follows...

If adoRs.RecordCount <> 0 And adoRs.BOF = False And adoRs.EOF = False Then
  'then we have records
  adoRs.MoveLast
  MyRecordCount  = adoRs.RecordCount
  aodRs.MoveFirst
  '....
Else
  '...
End If

or you can explicitly get the count of records through a count query...

strSQL = "SELECT COUNT(*) AS THECOUNT FROM tablename 'where...
'execute...
MyCount = adoRs.Fields("THECOUNT")

Good Luck

Thank you very much. It is much easier to count the records by counting it in a query. This problem was solved.!

See on next thread.!