Strange sql server/ado behavior

[SomePerson]

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

[smithrr]

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
>
>

[SomePerson]

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.

[Smithrr]

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.
>
>
>