Rowset position cannot be restarted

carrzkiss,

What is the code above that line?  Specifically the code that deals with objConn or this or another recordset?  How are you trying to use the recordset in this query?

Let me know if you have any questions or need more information.

b0lsc0tt

>>OLD CODE------------
rsMemFriends.open MemFriends, objConn,3,3
<<
This is a Open Static, Lock Optimistc cursor

NEW CODE------------------
set rsMemFriends = MemFriends.execute
This is a firehose (Forward Only, read Only) cursor.

As the name implies you cannot restart a Forward Only cursor.
Then why use  a firehose cursor, you ask?  In one one Performance.  It does not have all the overhead that your old code enjoys.

And of course this:
"In one one Performance "

Should have read:
"In one word Performance "

OK guys.
Here is the code and the error hits on Line:16
(Using another code similuar to that line I listed above, but not as complexe that receives the same error)

This works with a paging system, I tested to see if it was the paging system that was causing the issue, by removing the top 1/2 of the code that you see below, and the page runs with it removed.
But of course I loose the paging system.
I happen to like this one, as it is like Googles.

(The Attached Code, is the actual Code that I took it from. NOT!!! What I am using, just the original, incase anyone wants to look at it to compare)

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")
%>

Select allOpen in new window

Google-Type-Paging-System.txt

As I explained before the Execute method for the Command object produces a firehose cursor which does not support many of the properties that you are using.  Namely, RecordCount, MoveFirst, PageSize, PageCount, AbsolutePage.  You can tell because they will return a value of -1.  You can also use the Supports(adApproxPosition) to confirm.

On a totally unrelated problem, the Execute permission (unlike the Recordset Open method) instantiates the Recordset object so the following line is unnecessary and should be deleted:
set rsMemPics = CreateObject("ADODB.RecordSet")

If performance is not paramount and you want to use those properties (RecordCount, MoveFirst, PageSize, PageCount & AbsolutePage) you will have to use the Recordset's Open method with the Command object and in that case you will have to use the line:
set rsMemPics = CreateObject("ADODB.RecordSet")

Thanks AC.

What would I need to do to continue using this Paging script?
And keep my SQL Code intact?

Hello Rob. (SQL 2005)
I am not sure that I quite understand what you are referring to here.
(Code below is taken from one of the Paging Pages with it's Query attached.)

I have done some checking on it, and I found some code example BUT in .NET
I am not ready to cross over quite yet.
And probably will not for sometime now.

Still looking for some information on using your theory in Classic ASP
Hopefully I can find something that will do this similuar to what I am using now, as it is
Well liked by those that have better tested for me.

Thanks Rob.
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")
%>

Select allOpen in new window

Well AC.
You have succedded in going over my head. (I hate not knowing this)
Dealing with Access all these years, I never learned (or) had the need to learn this type of coding.
It is over my head big-time, and I hate it so much, I hate not knowing...

I do not even know where to begin,
I do not even know where to end.. I do not know...

I am lost......

Thank you AC.
BUT.
What really concerns me is keeping SQL Injections away.
If I go back to using the
rs.open sql, objconn
How (In the Paging) since I am making Queries, can I make sure I am am protected?

(Also. I have this link: stored procedures basics that I have started reading)

Also.
The site has to go LIVE tomorrow night, regardless, there is no way I can stop it from not.

I can learned the Stored Procedure and always update the site once I have it
Successfully Implemented into the site.

The Paging System that I have implemented (As shown here)
Is used in about 12 different pages with 12 different types of Queries.
So, this is something that I have to plan on.

------
Thank you for your help thus far.
Carrzkiss

Whether you use Execute or Open to retrieve rows has nothing to do with SQL Injection, so let's not get side-tracked.  So you do not want to use Stored Procedure right now, that is Ok, so you have two choices:
1. Change the way you retrieve the rows using the Recordset's Open mehod instead of the Command's Execute method and keep the code you have or
2. Use either the query that Rob or I posted using the ROW_NUMBER() function.

Pick your poison.

At the moment I have to choose #1.

Thanks AC.

Thanks guys.
At least now I know what to look forward too in the future.
When dealing with SQL Server coding.

Carrzkiss

That is fine.  Just let me know if you get stuck.