Solved

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

Posted on 2009-07-11
7
230 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

759 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