Strange sql server/ado behavior


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Strange sql server/ado behavior

  1. #1
    SomePerson Guest

    Strange sql server/ado behavior

    Thx in advance (hopefully, =o/

    Okay here's the culprit:

    '/\/\/\/\/\/\/\/\/\/\/\/\/
    RS.Open "SELECT PinID, Pin, Batch, SerialNumber, PinValue, Used FROM Pins
    WHERE SerialNumber = (SELECT Min(SerialNumber) FROM Pins WHERE Used = 0 AND
    PinValue = " + DBNum(PinValue) + ")", DB, adOpenDynamic, adLockPessimistic
    If RS.Eof Then
    RS.Close
    CancelReason = "No Available Pins"
    Exit Function
    End If

    RS("Used") = 1 '***** Error happens on this line
    RS.Update
    '/\/\/\/\/\/\/\/\/\/\/\/\/

    The error is:
    ////
    ADODB.Recordset error '800a0cb3'

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

    sql server 2000
    iis/windows 2000
    latest ado

    I've tried every combination of RecordsetType and LockingType.
    A multitude of different login accounts, including ones with server
    administration privileges.

    And the strangest thing is that it works on my local computer with my local
    sql server instance/local iis server,
    but I get this error when I move it to the web sql server/web iis server,
    with the exact same configuration as my local machine.

    Used is a bit field
    PinValue is a currency field.

    I do NOT get this error if I open a full table - RS.Open "Pins", DB,
    adOpenDynamic, adLockPessimistic

    I need pessimistic locking for concurrency, don't care however if it is
    dynamic or keyset.
    And I'd rather fix this than put it as a stored procedure, =o/

    agh!

    thx,
    Going nuts in NYC



  2. #2
    smithrr Guest

    Re: Strange sql server/ado behavior


    You will have to rewrite your query. You cannot update a recordset built
    on a nested select statement in the where clause.

    You may just want to break it up into 2 statements and then it will work.

    "SomePerson" <SomePerson@Example.com> wrote:
    >Thx in advance (hopefully, =o/
    >
    >Okay here's the culprit:
    >
    >'/\/\/\/\/\/\/\/\/\/\/\/\/
    > RS.Open "SELECT PinID, Pin, Batch, SerialNumber, PinValue, Used FROM Pins
    >WHERE SerialNumber = (SELECT Min(SerialNumber) FROM Pins WHERE Used = 0

    AND
    >PinValue = " + DBNum(PinValue) + ")", DB, adOpenDynamic, adLockPessimistic
    > If RS.Eof Then
    > RS.Close
    > CancelReason = "No Available Pins"
    > Exit Function
    > End If
    >
    > RS("Used") = 1 '***** Error happens on this line
    > RS.Update
    >'/\/\/\/\/\/\/\/\/\/\/\/\/
    >
    >The error is:
    >////
    >ADODB.Recordset error '800a0cb3'
    >
    >Current Recordset does not support updating. This may be a limitation of

    the
    >provider, or of the selected locktype.
    >////
    >
    >sql server 2000
    >iis/windows 2000
    >latest ado
    >
    >I've tried every combination of RecordsetType and LockingType.
    >A multitude of different login accounts, including ones with server
    >administration privileges.
    >
    >And the strangest thing is that it works on my local computer with my local
    >sql server instance/local iis server,
    >but I get this error when I move it to the web sql server/web iis server,
    >with the exact same configuration as my local machine.
    >
    >Used is a bit field
    >PinValue is a currency field.
    >
    >I do NOT get this error if I open a full table - RS.Open "Pins", DB,
    >adOpenDynamic, adLockPessimistic
    >
    >I need pessimistic locking for concurrency, don't care however if it is
    >dynamic or keyset.
    >And I'd rather fix this than put it as a stored procedure, =o/
    >
    >agh!
    >
    > thx,
    > Going nuts in NYC
    >
    >



  3. #3
    SomePerson Guest

    Re: Strange sql server/ado behavior

    actually you can, and I did.
    It just so happens that I somehow managed to not move over the primary key
    property on the PinID field.
    Thx anyway.

    "smithrr" <smithrr@smith-krenning.com> wrote in message
    news:3c5ae427$1@10.1.10.29...
    >
    > You will have to rewrite your query. You cannot update a recordset built
    > on a nested select statement in the where clause.
    >
    > You may just want to break it up into 2 statements and then it will work.





  4. #4
    Smithrr Guest

    Re: Strange sql server/ado behavior


    I'll have to look further into it for my own information. I've never had
    any luck with it myself and it would come in handy every once in a while.
    Thanks for letting me know that you had success with it.

    "SomePerson" <SomePerson@Example.com> wrote:
    >actually you can, and I did.
    >It just so happens that I somehow managed to not move over the primary key
    >property on the PinID field.
    >Thx anyway.
    >
    >"smithrr" <smithrr@smith-krenning.com> wrote in message
    >news:3c5ae427$1@10.1.10.29...
    >>
    >> You will have to rewrite your query. You cannot update a recordset built
    >> on a nested select statement in the where clause.
    >>
    >> You may just want to break it up into 2 statements and then it will work.

    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center