Problem in locking rows in database

[Bitaoo]

Hello,

I have a locking problem that I cannot underestand it. Would you please help me to find the problem?
I appreciate your help in advance. I ahev a table named T1 with the following columns:

Id --> Int primary key
Name --> Char(10)
R_Id --> Int
ValCol --> Int
Choice --> Int


and here is my code:

sub main
call Open_DB(cn)
call Begin_Transaction(cn)

set cmd.commandText = "Insert into T1(Name, R_Id, ValCol, Choice) Values ('B1', 1, 8, 1)
cmd.execute ' Insert a row in database

set cmd.commandText = "update T1 set Choice = 1 where R_Id = 1"
cmd.execute ' Update many rows in table T1

rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.open "Select * from T1 Where R_Id = 1"
do until rs.EOD
cmd.commandText = "Update T1 Set ValCol = 12 Where Id = " & rs("Id").value
cmd.execute ' Here I have problem, The row in locked and I cannot upddate it. (TimeOut Expired)

rs.movenext
loop
rs.close

call Commit_transaction(cn)
call db_close(cn)
end sub

The problem is the Update in the loop. would you please help me.
--Bita

 

[zemp]

Try resetting the command object with each iteration of the loop.

...
do until rs.EOD
      [b]Set[/b] cmd.commandText = ...
...

Or using a separate set statement,

...
do until rs.EOD
     Set cmd = New ADODB.Command
     cmd.commandText = ...
...

zemp

 

[Bitaoo]

Thanks, but it doesn't work.

--Bita