asked on

ASP Paging result from stored procedure with parameters

Hi
I'm trying to do some paging on an ASP page.

I get the following error:

Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.

It is required by the customer that I use parameters, but it seams like I cant change the cursortype when I do so???
Is there an easy way to get around this?

Set rs = server.createobject("ADODB.recordset")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "dbo.Search"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@SearchFrase", 200, 1, 255, keywords)
Set rs = cmd.Execute
 
If rs.EOF then
    Response.Write("<span class=""Fejl"" style=""font-size:14px;"">Der er ingen resultater der passer til din søgning<br><b>(""" & Keywords & """)</b></span>")
else
    rs.PageSize = 15
    rs.AbsolutePage = intPage
    intRecCount = rs.PageSize
    intPageCount = rs.PageCount
 
    Response.write("<table border=""0"" cellpadding=""0"" cellspacing=""0"" width=""100%" class=""Text"">")
 
    Do while not rs.EOF
   
        Response.write("<tr onMouseOver=""this.style.background='#eeeeee'"" onMouseOut=""this.style.background='none'"" onclick=""location.href='" & rs("link") & "'"" style=""cursor:pointer"">")
            Response.write("<td>")
                Response.write("<b>" & rs("SearchTitle") & "</b><br>")
                Response.write(rs("SearchDescription") & "<br>")
            Response.write("</td>")
        Response.write("</tr>")
        Response.write("<tr><td><hr noshade color=""#d5d5d7"" align=""left"" size=""1""></td></tr>")
 
            intRecCount = intRecCount - 1
        rs.MoveNext
    loop
End if

Open in new window

SOLUTION

Badotz

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

koldstar

ASKER

Thank you for your answers.
I have tried inserting the 3 lines before the code but I still gets the same error.
acperkins> I'm not sure what you want me to do? Can you show me some code? Is there a better way of doing this that i'm trying to do?

Anthony Perkins

>>Is there a better way of doing this that i'm trying to do?<<
I have no idea what you are trying to do. However if you are bound and determined to have a recordset that support bookmarks than you will have to do something like this:

Set rs = server.createobject("ADODB.recordset")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "dbo.Search"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@SearchFrase", 200, 1, 255, keywords)
' Set rs = cmd.Execute
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockBatchOptimistic
rs.Open cmd ' Do not set a connection object here.
Response.Write "Does this resultset support bookmarks: " & CStr(rs.Supports(adBookmark))

...

rs.Close
Set rs = Nothing

Badotz

The important things to remember are set the Recordset's attributes before you open it:

rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockBatchOptimistic

and to create the Recordset object with the >Open< method:

rs.Open cmd

koldstar

ASKER

It works perfectly now
Thank you very much :-)

Badotz

No worries - glad to help.