Pagecount error 1

[rsshetty]

    Set adoConn = OpenConnection
    adoRS.CursorLocation = adUseClient

    strSQL = "exec sp_SearchData '" & Request.Form("Keyword") & "'"
    
       
    adoRS.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly
    
    'number of rows to cache at a time. Should be set to the same as PageSize
    adoRS.CacheSize = CLng(PageSize)
    
    'number of items to display per 'page'
    adoRS.PageSize = CLng(PageSize)
    
    On Error GoTo ErrorOut
    
    'get the number of pages
    NumPages = adoRS.PageCount

When I run the SQL code separately, a row is returned. However, when I plug in the statement with the application, adoRS.PageCount isn't retrieved and its says Operation is not allowed when the object is closed.

Any insights??????

rsshetty.
It's always in the details.

 

[DotNetGnat]

are you sure that your stored procedure is getting some records...

i would suggest you to out...

if not(adoRS.EOF) then
NumPages = adoRS.PageCount

also put the
SET NOCOUNT ON in your stored procedure...

-DNG

 

[DotNetGnat]

if you do not put the SET NOCOUNT ON...you will get the messages like "1 rows effected" etc and this will cause the connection to break giving that error you mentioned...

-DNG

 

[Sheco]

Depending on how many records in your resultset, sometimes it is better to do this kind of work inside the stored procedure.

Just select your existing results into a #temporary table with an identity colum starting at 1. Then select the actual results out of the temp table using the identity column. That way you can just return records 100-120 or whatever from the stored procedure and not have to worry about paging in the recordset. This can really come in handy if, for some reason, you've got like a gazzillion-thousand records and you happen to want to view records 52350 thru 52375... also saves a lot of network traffic between the database and the web server...

 

[rsshetty]

Thanks. That worked.

rsshetty.
It's always in the details.

 

[DotNetGnat]

no problem...glad to be of help...

-DNG