Solved

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

Posted on 2009-07-11
7
216 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

20 Experts available now in Live!

Get 1:1 Help Now