List and Update code of SQL...

[daveftl]

Hello everyone good day!

I have this problem in my program.

Situation: I have ListBox that contains all Bottle Number, which is a Primary Key of my Table: Bottles. What I want to do is update all rows in the table Bottles where bottle number = ListAssigned.ListIndex = i. Here is my code: (I've used FOR LOOP to loop in all the entries in my Listbox).

Private Function UpdateBottles()
 
Dim i As Integer
 
For i = 0 To ListAssigned.ListCount - 1
 
 With rsbot2
 
     .ActiveConnection = cn
     .CursorLocation = adUseClient
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
     .Open "SELECT * FROM bottles WHERE bot_number =" & Val(ListAssigned.ListIndex = i)
 
 End With
 
  Call assignBot
 
    With rsbot2
 
        .Update
        .Requery
        .UpdateBatch
 
    End With
 
 rsbot2.Close
 
Next i
 
End Function
 
Private Sub assignBot()
 
 rsbot2(0) = Val(cmboBotNo.Text)
 rsbot2(1) = "Borrowed"
 rsbot2(3) = Val(lblDumOrderId.Caption)
 rsbot2(4) = Val(lblDumID.Caption)
 
End Sub

This code is not working. It didn't update any row in the table. I need help on this.
Thanks and God bless

DaveftL

[debasisdas]

Why you need to Select while you can Update directly .

[daveftl]

Why you need to Select while you can Update directly .

ah okay...over looked that one...thanks

[daveftl]

Why you need to Select while you can Update directly .

ive tried that one but
It is still not working well...it doesnt still update the table...

[Killer42]

ive tried that one but
It is still not working well...it doesnt still update the table...

Can you show us the latest version?

[daveftl]

Can you show us the latest version?

heres my latest code:

Private Function UpdateBottles()
 
Dim i As Integer
 
For i = 0 To ListAssigned.ListCount - 1
 
 With rsbot2
 
     .ActiveConnection = cn
     .CursorLocation = adUseClient
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
     .Open "UPDATE bottles SET bot_status = ' & Borrowed & ', order_id = '& Val(lblDumOrderId.Caption) & ',cust_id = ' & Val(lblDumID.Caption) & ' WHERE bot_no =" & Val(ListAssigned.ListIndex = i)
      '"UPDATE bottles SET  FROM bottles WHERE bot_number =" & Val(ListAssigned.ListIndex = i)
 
 End With
 
  Call assignBot
 
    With rsbot2
 
        .Update
        .Requery
        .UpdateBatch
 
    End With
 
 'rsbot2.Close
 
Next i
 
End Function

whats wrong with it?

God bless

[Killer42]

I'm really short on time right now. But I have to say, this whole concept of using Open with the “UPDATE” SQL, then applying an Update method to it, just seems totally bizarre to me. I thought the whole point of the SQL “UPDATE” statement is that you execute it, and it does the update. You don’t use it to retrieve a recordset, then start applying updates to that.

Or am I just out of touch?