ASP Recordset count

Try doing a movelast and then a movefirst so that the recordset value gets populated.

Just got hold of a database crack.
he recommended the following:

<%recordset.movelast%>
<% = RecordSet.RecordCount%>
<%recordset.movefirst%>

I'll try it when I'm home tonight.

Sorry, mayhew. Didn't see your comment before.

But thanks anyway

cheers   trashy

Cursor type...

When you request the RecordCount for a serverside recordset, a -1 may return. This occurs with ActiveX Data Objects (ADO) version 2.0 when the CursorType is adOpenForwardonly or adOpenDynamic. This occurs with ADO 1.5 only when the cursortype is adOpenForwardonly.  
When you select a CursorType that is not supported, the provider should select the CursorType closest to what you request.  Also, please note that not all combinations of LockType and CursorType work together. Changing a LockType may force a change in the CursorType. Be sure to use debug to check the value of CursorType when conducting tests with your OLEDB provider.

Use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType you select.

Dim rs As ADODB.Recordset      
Private Sub Form_Load()        
'set up rs
Set rs = New ADODB.Recordset        
rs.CursorLocation = adUseServer
rs.Open "Select ProductID from products", & "Provider=Microsoft.Jet.OLEDB.3.51;" & _                   "Data Source=d:\vb5_win95\nwind.mdb", _                    adOpenDynamic, adLockUnspecified        
Debug.Print rs.RecordCount      
End Sub

Replace the preceding Data Source with a Data Source on your computer.  Run the preceding form and note the record count. Change the CursorType to adOpenForwardonly and note the record count.
Change the CursorLocation to adUseClient and experiment with the different CursorTypes. In all cases the correct record count returns.

Or this: (the MoveLast is the key...)

Sub Form_Load ()      
Dim MyDB As Database, MyDyna As Dynaset
Set MyDB = OpenDatabase("C:\VB3\BIBLIO.MDB")
Set MyDyna = MyDB.CreateDynaset("Authors")      MyDyna.MoveLast
MyRecordCount = MyDyna.RecordCount      
MyDyna.Close      

form1.Show
Print MyRecordCount  
End Sub

So did you get this working?

You have to do movelast as mayhew suggested, because the engine doesn't know how many records it retrieved until it has walked through them.

As soon as I add the 'recordset.movelast' I get the following error:

The rowset does not support fetching backwards

I also tried to set the CursorType but didn't suceed.
Can any one help me with VBScript syntax to set the cursor type?

We need to see the code you're using.

Can you post it?

mgfranz
Sorry, can't get it to work.
I never wrote VB only VBScript, but there seems to be a difference in connecting databases.

The code I'm using:

IN THE GLOBAL.ASA FILE:
*****************************
Set Session("db") = Server.CreateObject("ADODB.Connection")
Session("db").Open "MyDB"
*****************************
executing global.asa is no problem, it works.

IN THE TEST.ASP FILE:
*****************************
MySQL = "SELECT * FROM Table1 WHERE Field1 = 11"
set rs = Session("db").Execute(MySQL)
rs.MoveLast
*****************************
As soon as I use movelast I get the error message.
'80040e24', 'The rowset does not support fetching backwards'

I tried to get your example to work, but didn't succeed.

trashy

Why are you doing this?  

MySQL = "SELECT * FROM Table1 WHERE Field1 = 11"

You are querying field1 where data = 11...  Don't you want to count the # of enteries in field1?

Read these lines carefully:

Set MyDyna = MyDB.CreateDynaset("Authors") ' Open dB field Authors
MyDyna.MoveLast ' Move to last record
MyRecordCount = MyDyna.RecordCount ' Count the records
MyDyna.Close    ' Close the dB

Print MyRecordCount    ' Print the results

So, in your file, try this: (untested...)

set rs = Session("db").CreateDynaset("Field1")
rs.MoveLast
rsRecordCount = rs.RecordCount
rs.Close

Mark

Ok, ok, it shouldn't be Field1 = 11 it rather should be Field1 > 11.

Anyway, it doesn't work. I get the following message:
The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.

Sorry, but at the moment I'm short in time for my web project. I don't have time to work on it all the time, so I think I will close this question thread unless someone has a solution that he knows it will work.

For the meantime I go for siabods solution. that one works.

Thanks for your help
Trashy

Something just occurred to me.  I know you don't have time to work on it, but I wanted to throw it out there.  I'll check it later myself.

I've never tried to directly manipulate a db object that is stored in a session variable.

I've always defined a local variable in the asp page and set it equal to the session db object.  That is:

IN THE TEST.ASP FILE:
*****************************
MySQL = "SELECT * FROM Table1 WHERE Field1 > 11"
set LocalDB = Session("db")
set rs = LocalDB.Execute(MySQL)
rs.MoveLast
*****************************


Again, I'll try this myself later and let you know what I come up with.

I tried this already. I actually work with a local variable for my DB. I just left it out, because I didn't think it makes any difference. I assume you'll get the following message when you try it:

The rowset does not support fetching backwards.

Here's an article referring to what I was saying:

http://www.4guysfromrolla.com/webtech/120898-1.shtml