Solved

How do I use ADODB.Command to get a recordset that allows MovePrevious, etc

Posted on 2009-07-11
7
235 Views
Last Modified: 2012-05-07
Hello All;

Using the below code, I need to continue using it, (The working code I am still using, the below code is modified, and does not work)
but it does not allow MovePrevious.
Could someone help in making this work? (if it is possible?)

Error from code below.
==============================
Microsoft OLE DB Provider for SQL Server error '80040e18'
Rowset position cannot be restarted.
page.asp, line 19
==============================

I have been told to use Stored Procesures,
BUT at the current time I am stressed for time. And cannot do it at the moment.
BUT will be doing it in the coming month(s).

Another EE gave me the Subject, and told me to ask, MAYBE someone can assist
In letting me know if it is possible or not.

Thanks All;
Carrzkiss
<%
Set MemPics = Server.CreateObject("ADODB.Command")
MemPics.ActiveConnection=objConn
MemPics.Prepared = true
set rsMemPics = CreateObject("ADODB.RecordSet")
if request("NumPerPage") <> 0 Then NumPerPage = Int(request("NumPerPage")) Else NumPerPage = 20 end if
 MemPics.CommandText = "SELECT Members.MyLN, Members.MyFN, MyInven.MemID, MyInven.VisID, MyPics.UserName, MyPics.PI FROM Members INNER JOIN (MyInven INNER JOIN MyPics ON MyInven.VisID = MyPics.PCCID) ON Members.Id = MyInven.VisID WHERE MyInven.MemID=?"
MemPics.Parameters.Append MemPics.CreateParameter("@MemID", adInteger,adParamInput, , visID)
 if Request.QueryString("page") = "" Then
    CurrentPage = 1 'We're On the first page
	NumPerPageOf = 1
    Else
    CurrentPage = CInt(Request.QueryString("page"))
	NumPerPageOf = ((CurrentPage * NumPerPage) - NumPerPage) + 1
    End if
    set rsMemPics = MemPics.execute
    Number = rsMemPics.RecordCount
    if Not rsMemPics.EOF Then
    	rsMemPics.MoveFirst
    	rsMemPics.PageSize = NumPerPage
    TotalPages = rsMemPics.PageCount
    rsMemPics.AbsolutePage = CurrentPage
    End if
    ScriptName = request.servervariables("ScriptName")
%>

Open in new window

0
Comment
Question by:Wayne Barron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24833523
cursor location must be adUseClient, and you eventually need a keyset cursor type...
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24833650
Hello Angel.

Could you possibly show a little code as to how this would be added to my code supplied here?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24833657
the problem is here:
set rsMemPics = MemPics.execute

this will open the recordset as clientonly, readonly, forwardonly (last one being the problem)

change to:

set rsMemPics = server.createobject("adodb.recordset")
rsMemPics.CursorLocation = adUseClient
rsMemPics.CursorType = adOpenKeyset
rsMemPics.LockType = adLockreadonly
rsMemPics.open MemPics
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 31

Author Closing Comment

by:Wayne Barron
ID: 31602534
Thank you so very much Angel.
Did exactly as it needed to.

That makes my night. (On this part anyway)

Carrzkiss
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24833739
You Rock Angel.

Thank you so very much for the needed code.

Carrzkiss
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24833900
glad I could help :)
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24835071
You always do Angel.
Helping to bring your Knowledge across.

Carrzkiss
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question