Member Avatar for awanz

hello buddy,
I have a problem about listview update data
the first I input data from textbox to listview, after listview have many data/row, I want to update data only one click button use query WHERE ID
the problem : I just can save first row only, not all or only by selected..
How to update all data in listview

mycode :

If ListView1.Items.Count > 0 Then
            strSQL = "Update errorlog set errorreport = errorreport - '" & ListView1.Items(0).SubItems(1).Text & "' where idreport = '" & ListView1.Items(0).SubItems(0).Text & "'"
            Dim da As New MySqlDataAdapter(strSQL, CONNECTION)
            da.Fill(ds)
            Me.Refresh()
            MsgBox("Success")
        Else
            MsgBox("Error")
        End If

please, help to fix it ..
thank you :)

Edited by awanz because: n/a
  • 2 Contributors
  • 3 Replies
  • 238 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Begginnerdev

Dani AI

Generated

Short diagnosis and concise fix: the code only ever touches the first ListView row (Items(0)) and is using a data adapter fill for an UPDATE, so only a single row is affected. The usual pattern is to loop the ListView.Items (or ListView.SelectedItems when only selected rows should be updated), and execute a parameterized UPDATE via MySqlCommand/ExecuteNonQuery (not DataAdapter.Fill). Keep the record primary key with each item (item.Tag is convenient) so the WHERE clause targets the correct id.

A safe, efficient pattern (prepare once, reuse, wrap in a transaction):

Imports MySql.Data.MySqlClient

Using conn As New MySqlConnection(CONN_STRING)
    conn.Open()
    Using tran = conn.BeginTransaction()
        Using cmd As New MySqlCommand("UPDATE errorlog SET errorreport = @report WHERE idreport = @id", conn, tran)
            cmd.Parameters.Add("@report", MySqlDbType.Int32)
            cmd.Parameters.Add("@id", MySqlDbType.VarChar)
            cmd.Prepare()

            For Each itm As ListViewItem In ListView1.Items
                Dim idText = If(itm.Tag IsNot Nothing, itm.Tag.ToString(), itm.SubItems(0).Text.Trim())
                Dim reportVal As Integer
                If Integer.TryParse(itm.SubItems(1).Text, reportVal) Then
                    cmd.Parameters("@report").Value = reportVal
                    cmd.Parameters("@id").Value = idText
                    cmd.ExecuteNonQuery()
                Else
                    ' log or skip malformed value
                End If
            Next
        End Using
        tran.Commit()
    End Using
End Using

Troubleshooting and notes: confirm subitem indexes (SubItems(0) = id, SubItems(1) = value) and data types; use Tag for the PK to avoid changing visible text; prefer parameters to avoid SQL injection and type errors; check ExecuteNonQuery return for affected rows; use a transaction for performance and atomicity. If the intention was to decrement a value (report = report - X) use that arithmetic explicitly in SQL with numeric parameters; if replacing the value use SET report = @report. Finally, if a dataset/DataAdapter workflow is preferred, configure an UpdateCommand and call adapter.Update(dataTable) instead of Fill for updates.

This expands on ’s point about keeping a unique value per item and addresses ’s flow (populate ListView, then persist all rows back to MySQL).

Recommended Answers

All 3 Replies

Member Avatar for Begginnerdev

You will want to create a listview item, and then bind the .text of the listview item to a unique value(primary key) then build the item and add it to the listview.

Example:

dim itmListView as New ListViewItem

'Random value, substitute it with your own.
itmListView.Text = iamunique

'Next add your values
'Example is if myValues was a datatable.
for i = 0 to myValues.rows.count - 1
itmListView.Subitems.Add(myValues.rows(i).Item(0))
next
MyListView.items.add(itmListView)

You can get your data from the data adapter by creating a data set and data table then passing it through.

'da = Data Adapter, ds = Dataset, MyValues = datatable
da.fill (ds,"tablename")
MyValues = ds.SelectTable("tablename")

To safeguard from NULL references, try this.

'Place a try catch block and an extra if for added protection.

Try
   
   If MyValues isnot Nothing Then

     'Your code here.

   End IF

Catch ex as Exception
   MsgBox("Oops!" & vbcrlf & ex.message")
End try

Hope this helps

Edited by Begginnerdev because: n/a
Member Avatar for awanz

This code read multiple row ?? .. and your code using sql server, I'm use Mysql reference ..
flow my code are :
1. Insert to listview use button addlist
2. When I think enough e.g listview

ID ErrorReportTotal
1001 20
2002 30
3003 10

3. click button update and run connection then all data in listview has updated by ID Report

but I don't how to all data in listview updated, my code only selected or the first row .. :(

Member Avatar for Begginnerdev

You will have to redraw the listview. I normally use a chain of sub procedures to:


1) Design (Columns)
-Calls-
2) Get Data (Data Reader)
-Calls-
3) Fill the listview

You can create this in one procedure, but I made it 3 for flexibility.


I hope this helps.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.