Solved

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

Posted on 2009-07-11
7
218 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
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

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

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 30

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 30

Author Comment

by:Wayne Barron
ID: 24833739
You Rock Angel.

Thank you so very much for the needed code.

Carrzkiss
0
 
LVL 142

Expert Comment

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

Author Comment

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

Carrzkiss
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is format f12.8 for a CSV file 6 41
How to use left join to take all data from master table? 11 49
SQL Select Statement 2 22
MSDN Licensing query 5 55
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now