Solved

Rowset position cannot be restarted

Posted on 2009-07-10
18
646 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
  • 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
 
LVL 30

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 30

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 14

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 30

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 14

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 30

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 30

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 30

Author Comment

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

Thanks AC.
0
 
LVL 30

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CREATE DATABASE ENCRYPTION KEY 1 44
INSERT INTO SELECT JOIN THING 2 26
separate column 24 20
Sql query to Stored Procedure 6 16
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

21 Experts available now in Live!

Get 1:1 Help Now