Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: Current Recordset does not support updating. This may be a limitation of the provider

    I keep getting this error when I try to update

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

    It worked fine in Access, but when I switched to SQL Server it stopped working.

    Can someone help! Please!

    thx

    Justin

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you declared a primary key in SQL Server for the table(s) in your rowset?

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    Originally posted by Pat Phelan
    Have you declared a primary key in SQL Server for the table(s) in your rowset?

    -PatP
    yes i have

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is the column that SQL Server recognizes as the primary key included in your keyset? Can you post the DDL (the CREATE TABLE statement) and the DML (the SELECT statement) so we have some more to go on?

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    12
    Originally posted by Pat Phelan
    Is the column that SQL Server recognizes as the primary key included in your keyset? Can you post the DDL (the CREATE TABLE statement) and the DML (the SELECT statement) so we have some more to go on?

    -PatP
    Actually I found out what the problem was.

    Access will allow 'ORDER BY' in the SQL statement, but SQL Server won't.

    Thanks for your help though!

    Justin

  6. #6
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    Originally posted by justinbezanson
    Actually I found out what the problem was.

    Access will allow 'ORDER BY' in the SQL statement, but SQL Server won't.

    Thanks for your help though!

    Justin
    What do you mean by "SQL Server won't"?
    [Order By] is a commonly used in SQL Server. Somthing else must be the cause of the problem and I doubt that it is [Order By].

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by GDMI
    What do you mean by "SQL Server won't"?
    [Order By] is a commonly used in SQL Server. Somthing else must be the cause of the problem and I doubt that it is [Order By].
    I had some trouble swallowing that one too, but as long as Justin is happy with his answer, I'm Ok with it. I use ORDER BY all of the time, and have never had any problem with it, even in Recordsets.

    -PatP

  8. #8
    Join Date
    Mar 2013
    Posts
    4

    Thumbs up Resolved

    I had the same error but it resolved after removing "ORDER BY" from SQL. For some reason it is not working on SQL 2005 with order by in Update query but it works fine with SQL 2008.

    thank you very much.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    ORDER BY works perfectly well in SQL Server. You just have to use the TOP keyword in the SELECT statement.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by weejas View Post
    ORDER BY works perfectly well in SQL Server. You just have to use the TOP keyword in the SELECT statement.
    I don't think that the use of ORDER BY was a problem, other than in some ADO Recordsets. It appears that with a few ADO drivers the use of ORDER BY invalidates updates on that Recordset, but I haven't been able to reproduce this problem (and I have tried).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah, fair enough. The only time I've had trouble with ORDER BY is when I forget to add "TOP 100 PERCENT" after "SELECT"...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Mar 2013
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    I don't think that the use of ORDER BY was a problem, other than in some ADO Recordsets. It appears that with a few ADO drivers the use of ORDER BY invalidates updates on that Recordset, but I haven't been able to reproduce this problem (and I have tried).

    -PatP
    I am using ADO Recordsets; it works fine on SQL server 2008 not on SQL server 2005. I was using SELECT with ORDER BY and trying to update each record.

Posting Permissions

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