Solved

Rowset position cannot be restarted

Posted on 2009-07-10
18
658 Views
Last Modified: 2012-05-07
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

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
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 24829678
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831009
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831042
And of course this:
"In one one Performance "

Should have read:
"In one word Performance "
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 31

Author Comment

by:Wayne Barron
ID: 24831608
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831851
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831862
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24832034
Thanks AC.

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

Assisted Solution

by:rob_farley
rob_farley earned 200 total points
ID: 24832449
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24832516
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
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 200 total points
ID: 24832631
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 24832788
>>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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24832943
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 24832975
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24833022
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24833040
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24833076
At the moment I have to choose #1.

Thanks AC.
0
 
LVL 31

Author Closing Comment

by:Wayne Barron
ID: 31602377
Thanks guys.
At least now I know what to look forward too in the future.
When dealing with SQL Server coding.

Carrzkiss
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24833084
That is fine.  Just let me know if you get stuck.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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