Updating a MySQL database with items from a listview

Kevin Davis

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

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kevin Davis14y ago
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.
Vulpes14y ago
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 Davis14y ago
This doesn't seem to update any of the records.......
Vulpes14y ago
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 Davis14y ago
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....
Vulpes14y ago
Can you repost the corrected code - might help if I could see what you're actually using now.
Kevin Davis14y ago
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.
Vulpes14y ago
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 Davis14y ago
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 Davis14y ago
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
Vulpes14y ago
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.