Professional Web Applications Themes

ORDER BY clause causes read-only recordset - Microsoft SQL / MS SQL Server

I have a website with some asp pages running off of a MS Sql db. For reasons that I can't explain, whenever querying the database with a SELECT query containing an order by clause, the returned recordset is read-only, regardless of cursor or lock type. This seems to be specific to the hosting provider I am using, as it works fine on development boxes. My provider's support people don't know anything about this problem, and I have been unable to find useful information in the knowledge base. The SQL server is running Windows 2000 Server and SQL 2000 The www ...

  1. #1

    Default ORDER BY clause causes read-only recordset

    I have a website with some asp pages running off of a MS
    Sql db. For reasons that I can't explain, whenever
    querying the database with a SELECT query containing an
    order by clause, the returned recordset is read-only,
    regardless of cursor or lock type. This seems to be
    specific to the hosting provider I am using, as it works
    fine on development boxes.

    My provider's support people don't know anything about
    this problem, and I have been unable to find useful
    information in the knowledge base.

    The SQL server is running Windows 2000 Server and SQL 2000
    The www server is running Windows 2000 Server and IIS 5
    MDAC 2.7

    Brian Guest

  2. #2

    Default Re: ORDER BY clause causes read-only recordset

    Why do you need a recordset that is *not* read-only? If you need to affect
    data, issue a separate INSERT, UPDATE or DELETE statement. Less locking on
    an entire recordset, and a much more efficient transaction...


    "Brian" <bbellingergenerationsms.com> wrote in message
    news:727801c34630$c15b0c50$3101280aphx.gbl...
    > I have a website with some asp pages running off of a MS
    > Sql db. For reasons that I can't explain, whenever
    > querying the database with a SELECT query containing an
    > order by clause, the returned recordset is read-only,
    > regardless of cursor or lock type. This seems to be
    > specific to the hosting provider I am using, as it works
    > fine on development boxes.
    >
    > My provider's support people don't know anything about
    > this problem, and I have been unable to find useful
    > information in the knowledge base.
    >
    > The SQL server is running Windows 2000 Server and SQL 2000
    > The www server is running Windows 2000 Server and IIS 5
    > MDAC 2.7
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: ORDER BY clause causes read-only recordset

    unfortunately, I didn't write this. Had I, I certainly wouldn't have
    done it this way. I'd recode it myself, but that would end our
    developer's obligation to support this.

    Regardless, that's not the issue. How do I make it work?!?!? What
    settings would be causing this?!?!?

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brian Bellinger Guest

  4. #4

    Default Re: ORDER BY clause causes read-only recordset

    > Regardless, that's not the issue. How do I make it work?!?!?

    Tough to say, can you show the code that "doesn't work" and let us know what
    error message you're getting?


    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: ORDER BY clause causes read-only recordset

    I suspect that your ORDER BY clause may contain columns not included in the
    SELECT clause. IIRC, this can result in a non-updatable recordset. If that's
    not the case, then you will need to post a repro script in order for us to
    help.

    Bob Barrows

    Brian Bellinger wrote:
    > unfortunately, I didn't write this. Had I, I certainly wouldn't have
    > done it this way. I'd recode it myself, but that would end our
    > developer's obligation to support this.
    >
    > Regardless, that's not the issue. How do I make it work?!?!? What
    > settings would be causing this?!?!?
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Bob Barrows Guest

  6. #6

    Default Re: ORDER BY clause causes read-only recordset

    set oConn = server.createobject("adodb.connection")
    oConn.open
    "Provider=SQLOLEDB;server=********;database=****** **;uid=********;pwd=**
    ******" 'obviously this line is slightly different from what is actually
    being used
    Set oRs = Server.CreateObject("ADODB.Recordset")
    oRs.open "SELECT * FROM [User] ORDER BY PersonID", oConn, 1, 2
    oRs("FirstNM") = "UserFirstName" 'erroring here
    oRs.update

    (also tried with every other cursor type and lock type combination)
    when the "ORDER BY PersonID" is removed, the page processes successfully

    The error:
    ADODB.Recordset error '800a0cb3'

    Current Recordset does not support updating. This may be a limitation of
    the provider, or of the selected locktype.


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brian Bellinger Guest

  7. #7

    Default Re: ORDER BY clause causes read-only recordset

    Since you did not provide DDL for the User table and some insert statements
    containing some sample data so we could test this for ourselves, I ran a
    test against the Employees table in the Northwind database. There were no
    problems performing any updates using this code:

    Set rs=server.createobject("adodb.recordset")
    sSQL="SELECT EmployeeID, LastName,FirstName " & _
    "FROM dbo.Employees"
    rs.Open sSQL,cn,adOpenStatic,adLockOptimistic,adCmdText
    rs(1) = "a" & rs(1)
    'rs(1) = mid(rs(1),2)
    rs.Update
    Response.Write rs(1)
    rs.Close

    sSQL="SELECT EmployeeID, LastName,FirstName " & _
    "FROM dbo.Employees order by EmployeeID"
    rs.Open sSQL,cn,adOpenStatic,adLockOptimistic,adCmdText
    rs(1) = mid(rs(1),2)
    'rs(1) = "D" & rs(1)
    rs.Update
    Response.Write "<BR>" & rs(1)

    Bob Barrows
    PS. I hope your production code isn't using "Select *" . This virtually
    guarantees that unneeded data is being dragged across the wire.

    Brian Bellinger wrote:
    > set oConn = server.createobject("adodb.connection")
    > oConn.open
    > "Provider=SQLOLEDB;server=********;database=****** **;uid=********;pwd=**
    > ******" 'obviously this line is slightly different from what is
    > actually being used
    > Set oRs = Server.CreateObject("ADODB.Recordset")
    > oRs.open "SELECT * FROM [User] ORDER BY PersonID", oConn, 1, 2
    > oRs("FirstNM") = "UserFirstName" 'erroring here
    > oRs.update
    >
    > (also tried with every other cursor type and lock type combination)
    > when the "ORDER BY PersonID" is removed, the page processes
    > successfully
    >
    > The error:
    > ADODB.Recordset error '800a0cb3'
    >
    > Current Recordset does not support updating. This may be a limitation
    > of the provider, or of the selected locktype.
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Bob Barrows Guest

Similar Threads

  1. ORDER BY clause in asp.net dataset
    By davidxtaylor in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: February 22nd, 07:05 PM
  2. Strange results of ORDER BY clause when item begins with slash orbackslash
    By SCassidy@overlandstorage.com in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 19th, 09:36 PM
  3. Where Clause: Order of Precedence
    By GH in forum ASP Database
    Replies: 2
    Last Post: July 20th, 08:09 PM
  4. Cannot re-read Recordset??
    By David Lozzi in forum ASP Components
    Replies: 9
    Last Post: January 28th, 08:39 PM
  5. Trouble with ORDER BY clause
    By Peroq in forum ASP
    Replies: 15
    Last Post: October 23rd, 09:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139