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

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

LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
cursor location must be adUseClient, and you eventually need a keyset cursor type...
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Hello Angel.

Could you possibly show a little code as to how this would be added to my code supplied here?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thank you so very much Angel.
Did exactly as it needed to.

That makes my night. (On this part anyway)

Carrzkiss
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
You Rock Angel.

Thank you so very much for the needed code.

Carrzkiss
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help :)
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
You always do Angel.
Helping to bring your Knowledge across.

Carrzkiss
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.