Insert Listview content into database (Mysql)

Question

Hello, I am developing an application in Vb and would like to know, how to pass a list created in Listviw control to the database? until the moment I built it:

  Private Sub criarLista()
    ListView1.Clear()
    ListView1.View = View.Details
    ListView1.LabelEdit = True
    ListView1.AllowColumnReorder = True
    ListView1.CheckBoxes = True
    ListView1.FullRowSelect = True
    ListView1.GridLines = True
    ListView1.Sorting = SortOrder.Ascending

    If btnInclusao.BackColor = Color.Silver Then
        With ListView1
            .Columns.Add("Codigo da Coleta", 120, HorizontalAlignment.Left)
            .Columns.Add("Observações", 120, HorizontalAlignment.Left)
        End With
    End If

    If btnManutencao.BackColor = Color.Silver Then
        With ListView1
            .Columns.Add("Codigo da Coleta", 120, HorizontalAlignment.Left)
            .Columns.Add("Observações", 120, HorizontalAlignment.Left)
            .Columns.Add("Baixa da Coleta", 120, HorizontalAlignment.Left)
        End With
    End If
End Sub

the code that adds the items inside the list:

  Dim newItem As New ListViewItem(CodColeta_txt.Text)
  newItem.SubItems.Add(obsColeta_txt.Text)

I tried to do it that way:

    Private Sub inserirListadeColeta()
     reconect()

    For Each item As ListViewItem In ListView1.Items
        cmd.CommandText = "INSERT INTO ListaParaColeta (fk_ColetaParaMotorista, ListaParaColeta_CodColeta, ListaParaColeta_osbColeta" _
        & "ListaParaColeta_periodo, ListaParaColeta_dataRegistro) VALUES (@fk, @CodColeta, @obsColeta, @periodo, @data) "
        Using conn
            Using cmd
                With cmd
                    .Connection = conn
                    .CommandType = CommandType.Text
                    .Parameters.Add("@fk", MySqlDbType.Int32).Value = codParaMotorista
                    .Parameters.Add("@CodColeta", MySqlDbType.Int32).Value = item.SubItems(0).Text 'Campo do ListViwer Codigo da Coleta
                    .Parameters.Add("@obsColeta", MySqlDbType.VarChar).Value = item.SubItems(1).Text ' Campo do ListViwer OBS
                    .Parameters.Add("@periodo", MySqlDbType.Int32).Value = cmbPeriodo.SelectedIndex
                    .Parameters.Add("@data", MySqlDbType.Date).Value = Format(DataHoje.Value, "yyyyMMdd")
                End With
                Try
                    cmd.ExecuteReader()
                    Clipboard.Clear()
                    Clipboard.SetText(strsql)
                    MsgBox("inserirListadeColeta")

                Catch ex As Exception
                    MsgBox("Erro inserirListadeColeta" & vbCrLf & ex.ToString, MsgBoxStyle.Critical, "Erro")
                    Clipboard.Clear()
                    Clipboard.SetText(strsql)
                    Exit Sub
                End Try
            End Using
        End Using
    Next
End Sub

Thanks in advance!

Answer 1

I resolved...

    Dim qntItens As Integer
    Dim qntLops As Integer
    Dim objlistViwer
    reconect()
    qntItens = ListView1.Items.Count
    If Not ListView1.Items.Count = 0 Then
        Do Until qntLops = ListView1.Items.Count
            objlistViwer = ListView1.Items.Item(qntLops)
            With objlistViwer
                cmd.CommandText = "INSERT INTO listaparacoleta (fk_ColetaParaMotorista, ListaParaColeta_CodColeta,ListaParaColeta_obsColeta, ListaParaColeta_periodo,ListaParaColeta_DataRegistro) VALUES" _
                    & "('" & codParaMotorista & "','" & lvitem.subitems(0).text & "','" & lvitem.subitems(1).text & "','" & cmbPeriodo.SelectedIndex & "','" & Format(DataHoje.Value.ToString("yyyy-MM-dd")) & "')"
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("Erro inserirListadeColeta" & vbCrLf & ex.ToString, MsgBoxStyle.Critical, "Erro")
                    Exit Sub
                End Try
            End With
            qntLops = qntLops + 1
            objlistViwer = Nothing
        Loop
        MsgBox("Dados Enviados!")
    End If

to retrieve them:

    criarLista()
    reconect()
    Dim dr_temp As MySqlDataReader
    strsql = "Comando SQL que ID seja = '" & codFKColeta & "'"
    Dim objCommand As New MySqlCommand(strsql, conn)
    Try
        dr_temp = objCommand.ExecuteReader()
    Catch ex As Exception
        Exit Sub
    End Try
    ListView1.BeginUpdate()
    While (dr_temp.Read())
        Dim codigos As String = dr_temp.Item(1)
        Dim ls As New ListViewItem(codigos, 0)
        ls.SubItems.Add(dr_temp.Item(2))
        ls.SubItems.Add(dr_temp.Item(3))
        ListView1.Items.Add(ls)
    End While
    ListView1.EndUpdate()