I've used the following code to return the number of records in a 

recordset that was created from a Table:



 recordsetobject.RecordCount 



But when i use a Query to fill the recordset it returns -1. 

What's going on?



(BTW, I'm using an Access database)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: I've used the following code to return the number of records in a 

: recordset that was created from a Table:

: 

:  recordsetobject.RecordCount 

: 

: But when i use a Query to fill the recordset it returns -1. 

: What's going on?

: 

: (BTW, I'm using an Access database)



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



www.adOpenStatic.com/faq/recordcounterror.asp

www.adOpenStatic.com/faq/recordcountalternatives.asp



Cheers

Ken

Hi Pete,



This is to do with the type of cursor you are using. In particular, the

.RecordCount method will return -1 if you are using either a Dynamic

cursor, or a forward-only cursor. This is because these two types of cursor



do not support either the adApproxPosition or the adBookmark methods. If

you /have/ to use one of these cursor types, and want the recordcount, then



you'll either have to do a rsObj.movelast, or if /this/ isn't support, loop



through the entire recordset, maintaining a count variable.



Cheers,



Ben



At 23:06 18/05/01 +0000, you wrote:

>I've used the following code to return the number of records in a

>recordset that was created from a Table:

>

>  recordsetobject.RecordCount

>

>But when i use a Query to fill the recordset it returns -1.

>What's going on?

>

>(BTW, I'm using an Access database)

>

>





http://benmeg.com



Home 020 8892 8744

Mobile 0771 30 555 82

You may want to include in your query the COUNT function:

SELECT COUNT(fldName) as RecordCount FROM tblName..... 



This will give you an count of the number of records returned from your

query.



Regards,

Yoshie





-----Original Message-----

From: Pete Cofrancesco [mailto:pcofran@y...] 

Sent: Friday, May 18, 2001 11:07 PM

To: ASP Databases

Subject: [asp_databases] How do I find how many records are in a Query?



I've used the following code to return the number of records in a 

recordset that was created from a Table:



 recordsetobject.RecordCount 



But when i use a Query to fill the recordset it returns -1. 

What's going on?



(BTW, I'm using an Access database)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: This is to do with the type of cursor you are using. In particular, the

: .RecordCount method will return -1 if you are using either a Dynamic

: cursor, or a forward-only cursor. This is because these two types of

cursor

: do not support either the adApproxPosition or the adBookmark methods. If

: you /have/ to use one of these cursor types, and want the recordcount,

then

: you'll either have to do a rsObj.movelast, or if /this/ isn't support,

loop

: through the entire recordset, maintaining a count variable.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



I'd strongly advise against doing either of these two suggestions. They are

listed under "The methods that suck" on this page:

http://www.adopenstatic.com/faq/recordcountalternatives.asp along with

explanantions on why they such.



Better alternatives are at the top of the page.



Cheers

Ken

Thanks to everyone who replied, this is a great forum! After switching 

from adOpenForward to adOpenStatic cursor everything worked fine! I'm 

surprized that the WROX book "ASP 3.0" doesn't cover this. 



Am I using the best method to do my task ?

I've heard that this cursor type is expensive. The table i'm displaying 

has a lot of records so im using recordcount calculate how many pages to 

use to divide up the records, I then use getRows to display the data from 

the range of records I want.



<% 'RECORD LIST page, lists all records for table selected from the 

	

'open connection to database

	DBName = Session("DBname")

	adOpenStatic = 3

	adLockReadOnly = 1

	set CON = Server.CreateObject ("ADODB.Connection") 'create 

connection object

	set RS = Server.CreateObject ("ADODB.Recordset") 'create recordset 

object

	sDatabaseFile=(server.mappath("..\..\Database\"&DBName)) 'name and 

path of database

	CON.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data 

Source="&sDatabaseFile&"" 'open database

	SQL="SELECT * FROM " & QueryName & " "

	RS.open SQL, CON, adOpenStatic, adLockReadOnly



	TotalRecords = RS.RecordCount  

	%>

	

<!--start ------- Navigation --------- --><td class="pgSubtitle" 

colspan="2"><img src="images/icon_table.gif" width="27" height="27" 

align="absmiddle"> 

      <b><%= TableCaption %></b> table. (only 50 records per page are 

displayed)<br>

<% ' display links to pages that break up record list if there is more 

than 50 records.

if TotalRecords > 50 then

	x=0

	i=0

	Do while 50*i < TotalRecords

		i=i+1

		if (i*50)-50 = eval(StartRow) then %>

			<b>Page <%= i %></b>  |   

		<% else %>

			<a href="RecordList.asp?StartRow=<%= x %>" 

class="light">Page <%= i %></a>  |    

		<% end if %>

		<%

		 x=x+50

		if x>TotalRecords then x=StartRow end if 

	loop

end if 

%>

<!--end =======SUBTITLE SECTION =============-->

<% 'store values and names of fields to Arrays. Close Connection to 

database

	TotalFields = RS.Fields.count

	dim AryFieldName(30) 'store fields names

	i=0

	 For Each Field in RS.Fields 

		AryFieldName(i) = Field.name 

		i=i+1

	next 



	RS.Move(StartRow) 

	AryGetRows = RS.GetRows(50) 'store all columns and rows in an Array

	LastCol = Ubound(AryGetRows, 1)

	LastRow = Ubound(AryGetRows, 2)

	RS.close

	set RS = nothing

	CON.close

%>



  <!--start =======CONTENT SECTION =============-->

<!-- start Edit record row -->

	<% For intRow = 0 to LastRow %>

        <tr> 

          <% For intCol = 0 to LastCol %>

<% if intCol = 0 then %>

  <td>

    <a href="<%=sNextPage%>?editType=UPDATE&KFvalue=<%= AryGetRows(0, 

intRow) %>&KFname=<%= AryFieldName(0) %>"><img src="edit.gif"></a>

 </td>

<% end if %>

<td><%= AryGetRows(intCol, intRow) %> </td>

          <% next %>

        </tr>

<!-- end Edit record row -->

	<% next %>

<%  set AryGetRows = nothing 'clean up %>