Rowset position cannot be restarted

Hello all;
Changing everything over to SQL Server and trying to tighten up the code.

OK.
Below OLD CODE  works
When I put in the new code, it screams this at me.

==============================
Microsoft OLE DB Provider for SQL Server error '80040e18'
Rowset position cannot be restarted.
page.asp, line 19
==============================

How would the bottom be written up now with the NEW CODE?

Thanks All;
Carrzkiss
OLD CODE------------
rsMemFriends.open MemFriends, objConn,3,3
 
NEW CODE------------------
set rsMemFriends = MemFriends.execute

Open in new window

LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

b0lsc0ttIT ManagerCommented:
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
0
Anthony PerkinsCommented:
>>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.
0
Anthony PerkinsCommented:
And of course this:
"In one one Performance "

Should have read:
"In one word Performance "
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wayne BarronAuthor, Web DeveloperAuthor Commented:
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")
%>

Open in new window

Google-Type-Paging-System.txt
0
Anthony PerkinsCommented:
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")
0
Anthony PerkinsCommented:
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")
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thanks AC.

What would I need to do to continue using this Paging script?
And keep my SQL Code intact?
0
rob_farleyCommented:
I'd do the paging on the server, by tweaking the query.

What version of SQL Server are you using? If it's SQL2005/8, then I'd do it using row_number(). On SQL2000, TOP.

SQL 2005/8:
select *, row_number() over (order by orderfield1, orderfield2) as rownum
from
(/*original query without order*/) t
where rownum between 41 and 60
order by orderfield1, orderfield2;

SQL2000:

select *
from
(
select top 20 *
from
(
select top 60 *
from
(/*original query without order*/) t
order by orderfield1, orderfield2) t2
order by orderfield1 desc, orderfield2 desc) t3
order by orderfield1, orderfield2;

This gets the top 60, then finds the last 20, then orders it correctly. But bear in mind that in 2000, TOP can't take variables (so use ...TOP " & CStr(CInt(num)) & "...)

Doing it on the server lets SQL do the work, instead of sending everything across the network every time.

Rob
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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")
%>

Open in new window

0
rob_farleyCommented:
I'm on my Mobile this morning, and can't see the code snippets.

But...

suppose your original query was "SELECT GalID, MemID, Username FROM dbo.Gal WHERE MemID = ? ORDER BY GalID"

Now use:

select *, row_number() over (order by GalID) as rownum
from
(SELECT GalID, MemID, Username FROM dbo.Gal WHERE MemID = ?) t
where rownum between ? and ?
order by GalID;

...and then pass in two more parameters, for the start and end of the page.

row_number() is a ranking function you can use for paging with the construct I've shown here.
Rob
0
Anthony PerkinsCommented:
>>What would I need to do to continue using this Paging script?<<
You would have to use the Recordset Open method.

Alternatively for a better approach use a stored procedure or query with ROW_NUMBER as rob_farley suggests, although my preference would be using a CTE as in:

;WITH    PageCTE
          AS ( SELECT   m.MyLN,
                        m.MyFN,
                        i.MemID,
                        i.VisID,
                        p.UserName,
                        p.[PI],
                        ROW_NUMBER() OVER ( ORDER BY MyLn, MyFn ) Row
               FROM     Members m
                        INNER JOIN MyInven i ON m.Id = i.VisID
                        INNER JOIN MyPics p ON i.VisID = p.PCCID
               WHERE    i.MemID = ?
             )
    SELECT  MyLN,
            MyFn,
            VisID,
            UserName,
            [PI]
    FROM    PageCTE
    WHERE   Row BETWEEN ? AND ?
    ORDER BY
            MyLN,
            MyFn

Notes:
1. Notice the correct use of Inner Joins (a tad different to MS Access)
2. Get used to using Aliases, it makes it easier to read.
3. PI is a keyword it is best to use a different name or at the very least enclose it in square brackets.
4. You would have to add the start and end row parameters.

You can also wrap this in a stored Procedure as follows:

CREATE PROCEDURE usp_GetMembers
                              @MemID INTEGER,
                              @StartRow INTEGER,
                              @EndRow INTEGER
AS

SET NOCOUNT ON

;WITH    PageCTE
          AS ( SELECT   m.MyLN,
                        m.MyFN,
                        i.MemID,
                        i.VisID,
                        p.UserName,
                        p.[PI],
                        ROW_NUMBER() OVER ( ORDER BY MyLn, MyFn ) Row
               FROM     Members m
                        INNER JOIN MyInven i ON m.Id = i.VisID
                        INNER JOIN MyPics p ON i.VisID = p.PCCID
               WHERE    i.MemID = @MemID
             )
    SELECT  MyLN,
            MyFn,
            VisID,
            UserName,
            [PI]
    FROM    PageCTE
    WHERE   Row BETWEEN @StartRow AND @EndRow
    ORDER BY
            MyLN,
            MyFn
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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......

0
Anthony PerkinsCommented:
Ok, why not take it slowly, then.  Change the way you are creating the recordset to use the Recordset's Open method instead of the Command's Execute method.  Then you can control the type of cursor produced and continue to use your code.  It may not be the best way, but it will keep you in your comfort zone.

It is not a great leap to use the ROW_NUMBER() function to achieve paging, but when you are ready for that you can ask again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
0
Anthony PerkinsCommented:
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.
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
At the moment I have to choose #1.

Thanks AC.
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thanks guys.
At least now I know what to look forward too in the future.
When dealing with SQL Server coding.

Carrzkiss
0
Anthony PerkinsCommented:
That is fine.  Just let me know if you get stuck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.