Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-11
7
Medium Priority
?
238 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 2000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

722 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