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.Li stIndex = i. Here is my code: (I've used FOR LOOP to loop in all the entries in my Listbox).

[CODE=vb]Private Function UpdateBottles()

Dim i As Integer

For i = 0 To ListAssigned.Li stCount - 1

With rsbot2

.ActiveConnecti on = cn
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.Open "SELECT * FROM bottles WHERE bot_number =" & Val(ListAssigne d.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.T ext)
rsbot2(1) = "Borrowed"
rsbot2(3) = Val(lblDumOrder Id.Caption)
rsbot2(4) = Val(lblDumID.Ca ption)

End Sub[/CODE]
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:
[CODE=vb]Private Function UpdateBottles()

Dim i As Integer

For i = 0 To ListAssigned.Li stCount - 1

With rsbot2

.ActiveConnecti on = cn
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.Open "UPDATE bottles SET bot_status = ' & Borrowed & ', order_id = '& Val(lblDumOrder Id.Caption) & ',cust_id = ' & Val(lblDumID.Ca ption) & ' WHERE bot_no =" & Val(ListAssigne d.ListIndex = i)
'"UPDATE bottles SET FROM bottles WHERE bot_number =" & Val(ListAssigne d.ListIndex = i)

End With

Call assignBot

With rsbot2

.Update
.Requery
.UpdateBatch

End With

'rsbot2.Close

Next i

End Function[/CODE]
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?