Updating a MySQL database with items from a listview

Question

I have a listview with a few columns, and I'm trying to choose all items and add each of them to my MYSQL database online. However, when I try to run the script, it will either only update the first record, or the last record, but never all the records. Any help would be appreciated. Thank you.

Dim strsql As String
        For i = 0 To ListView1.Items.Count - 1
            Dim a As String = ListView1.SelectedItems(i).SubItems(15).Text---------------------------Customer Name Column
            Dim b As Integer = CInt(ListView1.SelectedItems(i).Text)------------------------------------Id Number of Customer
            'strsql = "Update orders SET Name = '" & ListView1.SelectedItems(i).SubItems(15).Text & "' where ID = '" & ListView1.SelectedItems(i).Text & "'"

strsql = "Update orders SET Name = '" & a & "' where ID = '" & b & "'"
        Dim da As New MySqlDataAdapter(strsql, CONNECTION)

        Next

        da.Fill(ds)

I've tried a couple different ways of doing this, and it "partially" works either way. I also get a error stating that "the integer i will not be inferred as it is bound to a filed in an enclosing scope."

Thanks again for any help.

Zcast

Kevin Davis · Answer

Well, after hours of trying to figure this out, this is the code I have that actually works correctly: Private sub UPDATE() For Me.i = 0 To ListView1.SelectedItems.Count - 1 strSQL = "Update orders set Name = '" & ListView1.SelectedItems(i).SubItems(15).Text & "' where ID = " & Val(ListView1.SelectedItems(i).Text & "") Dim da As New MySqlDataAdapter(strSQL, CONNECTION) da.Fill(ds) Next end sub Thanks for all your help Vulpes. I appreciate it very much.

Vulpes · Answer

I'd see if it's any better if you fill the dataset just the once and then update it multiple times: Dim da As New MySqlDataAdapter("Select Name, ID From orders", CONNECTION) da.Fill(ds) For Me.i = 0 To ListView1.SelectedItems.Count - 1 Dim a As String = ListView1.SelectedItems(i).SubItems(15).Text Dim b As Integer = CInt(ListView1.SelectedItems(i).Text) strsql = "Update orders SET Name = '" & a & "' where ID = ' " & b & "" da.UpdateCommand = new MySqlCommand(strsql) da.Update(ds) Next The documentation for the MySqlDataAdapter is linked to below and I'm reasonably certain that you can only pass a SELECT command to the constructor. The UPDATE command needs to be assigned to the UpdateCommand property: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqldataadapter.html#connector-net-examples-mysqldataadapter-updatecommand

Kevin Davis · Answer

This doesn't seem to update any of the records.......

Vulpes · Answer

I think the problem is that you're not using the DataAdapter correctly. Try it like this: Dim da As New MySqlDataAdapter("Select Name, ID From orders", CONNECTION) For Me.i = 0 To ListView1.SelectedItems.Count - 1 Dim a As String = ListView1.SelectedItems(i).SubItems(15).Text Dim b As Integer = CInt(ListView1.SelectedItems(i).Text) strsql = "Update orders SET Name = '" & a & "' where ID = ' " & b & "" da.UpdateCommand = new MySqlCommand(strsql) da.Fill(ds) da.Update(ds) Next

Kevin Davis · Answer

For Me.i = 0 To ListView1.SelectedItems.Count - 1 Dim a As String = ListView1.SelectedItems(i).SubItems(15).Text Dim b As Integer = CInt(ListView1.SelectedItems(i).Text) strsql = "Update orders SET Name = '" & a & "' where ID = ' " & b & "" Dim da As New MySqlDataAdapter(strsql, CONNECTION) da.Fill(ds) Next The weird thing is it very sporadic in which records it will update. I just tried it again, and it updated records 4,5,7,8 but skipped record 6. Tried it again, and it updated records 4,5,6, and 7, but not 8. I'm super confused....

Vulpes · Answer

Can you repost the corrected code - might help if I could see what you're actually using now.

Kevin Davis · Answer

Actually, there is not. Record ID's are 3,4,5,6,and 7. Now even if I try to update ONLY record 4, using the same code, it will not update at all.

Vulpes · Answer

I'm struggling to think of a possible explanation for that. Is it possible that there could be a couple of rows in the ListView with the same ID column?

Kevin Davis · Answer

I just tried for i = 0 to listview1.selecteditems.count-1 with 5 records, and it only adds 4 of those to the database, but not the last record

Kevin Davis · Answer

Vuples, yes you are correct, it should be "selecteditems", and I did correct the single quote issue. However when I use: For i = Listview1.selecteditems.count-1, it will only add the first two out of three records, but not the last record, and when I use: For i= 0 to Listview1.selecteditems.count, it will add all three records but always gives the error "4 is not a valid index" when using three items

Vulpes · Answer

Shouldn't this line: For i = 0 To ListView1.Items.Count - 1 be: For i = 0 To ListView1.SelectedItems.Count - 1 One other thing I noticed is that you're putting single quotes around the integer 'b' in the Update command. This will be OK as long as ID is a varchar or similar in the database but not if it's an integer.