Hi all,



We are switching from an Access DB to SQL Server 7.0. My problem is that

on our web site we are using the recordset property RecordCount and

AbsolutePage and it works fine with access but with SQL Server it

doesn't. I've been reading about it and I found out that not all

provider support those property. right now the provider I use is

SQLOLEDB. I would love to give you some code but I don't know what would

be useful. What can I do to fix the problem?



Thanks!



--

Martin Maurais

Tribeworks, Web Administrator

martin@t...

(xxx) xxx-xxxx

988 Market St. 8th Fl. SF, Ca 94102

http://www.tribeworks.com

are you trying to provide paged results in your web pages from your massive

recordset?





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

From: "Martin Maurais" 

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 26, 2000 12:46 PM

Subject: [asp_databases] RecordCount and AbsolutePage property not working





> Hi all,

>

> We are switching from an Access DB to SQL Server 7.0. My problem is that

> on our web site we are using the recordset property RecordCount and

> AbsolutePage and it works fine with access but with SQL Server it

> doesn't. I've been reading about it and I found out that not all

> provider support those property. right now the provider I use is

> SQLOLEDB. I would love to give you some code but I don't know what would

> be useful. What can I do to fix the problem?

>

> Thanks!

>

> --

> Martin Maurais

> Tribeworks, Web Administrator

> martin@t...

> (xxx) xxx-xxxx

> 988 Market St. 8th Fl. SF, Ca 94102

> http://www.tribeworks.com

>

>

>

Aside from Procs, if you return a recordset use the .getRows method and

evaluate the UBound() of the 2nd dimension of the resulting array.



:: sample code ::



objRS.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

If objRS.EOF and objRS.BOF then

    Response.Write("No Records")

Else

    arrResults = objRS.getRows

    intNumRecords = UBound(arrResults, 2)

    Response.Write("There are " & intNumRecords + 1 & " records")

    ' Need to add 1 to intNumRecords as the first element is numbered 0

    ' So 10 records would result in a UBound() of 9

End if

objRS.close

set objRS = nothing



The reason I'm recommending doing things this way is that you'll want to

move to .getRows anyway as you can .close your recordset straight after

calling the .getRows method. This allows you to minimise connection time to

your database and instead manipulate the data in a VBScript array



Cheers

Ken



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

From: "Martin Maurais" 

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, July 27, 2000 2:46 AM

Subject: [asp_databases] RecordCount and AbsolutePage property not working





> Hi all,

>

> We are switching from an Access DB to SQL Server 7.0. My problem is that

> on our web site we are using the recordset property RecordCount and

> AbsolutePage and it works fine with access but with SQL Server it

> doesn't. I've been reading about it and I found out that not all

> provider support those property. right now the provider I use is

> SQLOLEDB. I would love to give you some code but I don't know what would

> be useful. What can I do to fix the problem?

>

> Thanks!

Here you have an exampel how I do it with OLE DB and ADO.



   Dim nPage,nPageCount,nRecord

   Dim strConn

   Dim rs



   strConn = "Provider=SQLOLEDB;Data Source=Local;Initial Catalog=test;User Id=sa;Password=;"



   Set rs = Server.CreateObject("ADODB.Recordset")

   ' Use client cursor to enable AbsolutePosition property.

   rs.CursorLocation = adUseClient

   rs.Open "select * from customer", strConn

   

   ' Display Name, ten records at a time.

   rs.PageSize = 10

   nPageCount = rs.PageCount

   For nPage = 1 To nPageCount

      ' What page we want to see

      rs.AbsolutePage = nPage



      For nRecord = 1 To rs.PageSize

         Response.Write rs("Firstname") & "<BR>" & vbCrLf 

         Response.Write rs("Lastname") & "<BR>" & vbCrLf

         rs.MoveNext

         If rs.EOF Then Exit For

      Next



   Next



   rs.Close



I hope this will help you..



/Fredrik Normen