ASP Paging (MS SQL)

I have some problems connecting to to my Microsoft SQL Server 2000. I'm doing ADO Paging to separate results into several pages. I've got the code below from google and was trying to modify it to suit my program. When i do a check for

Response.write rs.Pagesize
or
Response.write rs.Count

it will return -1

I have around 200records, so it should return me a value.
==============================================


Sql = "SELECT IDX, CLIENTIDX, COMPANY FROM CL_ADDRESSES WHERE CLIENTIDX= '" & Session.Contents("clientidx") & "' ORDER BY COMPANY"

rs.CursorLocation = 3
rs.CursorType = 3

rs.ActiveConnection = Session("Conn")
set rs = Session("Conn").Execute(Sql)

rs.PageSize = 20
rs.CacheSize = rs.PageSize
intPageCount = rs.PageCount
intRecordCount = rs.RecordCount

If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1

If intRecordCount > 0 Then
rs.AbsolutePage = intPage
intStart = rs.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (rs.PageSize - 1)
End if
End If

If RS.EOF Then
RS.Close
Response.Write "No Items found."
ELSE
...
....
End if

==============================================
I had placed the connection portion in global.asa on the Session_onstart event.
Could anyone advise me on this? Thanks in advance

a) have you tested the statement in the DBMS?
b) have you printed out Session.Contents("clientidx") to see what it's value is before hand?
c) have you printed out the SQL string generated by your asp coding to make sure you didn't screw anything up in the switch over?
d) are you sure you're using the method for the record set correctly?
e) this sounds like asp.net, have you tried that forum instead of the just plain pure asp forum?

It did retrieve the Session.Contents("Clientidx").
I tested the the statement and Query Analyzer and it worked.
The above code return 20 records to the page only but when i do a check for AbsolutePage, PageCount, it will still return -1

You can open recordsets in ways that will not provide you with a recordcount. One problem when you execute sql on your connection, you will get a forward-only read-only recordset, which doesn't happen to support recordcount.

Instead of the con.execute(sql), try something like:


Take a look at the documentation for the recordset.recordcount property, there is a description of various conditions that prevent getting a recordcount from your ado recordset, and some comments on possible performance issues when using recordcount.

You can always roll your own recordcount with a SELECT COUNT(*) FROM table

Do you mean it's better to open the connection on each page that needs it, then close the connection as soon as you are finished with it?

What's the use of Global.asa anyway?
Do we store connection there?

==============================================
Where i stored my db connection ..
==============================================
<script language="vbscript" runat="server">
sub Application_OnStart
end sub

sub Application_OnEnd
end sub

sub Session_OnStart
'' Now i'm placing all my db connection here
Set Session("Conn") = Server.CreateObject("ADODB.Connection")
Session("Conn").Provider = "SQLOLEDB"
Session("Conn").Open("server=192.168.0.22;UID=sa;PWD=test;database=test;")
end sub

sub Session_OnEnd
Session.Abandon
end sub
</script>
=============================================

Can i say that my approach is wrong?

Yes.

http://msdn.microsoft.com/library/d...tml/ASPtips.asp

If you look in the msdn library at http://msdn.microsoft.com/library there is full documentation on global.asa