Insert ListView contents into the database (MySQL)

Question

Insert ListView contents into the database (MySQL)

Navigation

#1 by (2 votes)

2

Hi, I'm developing an application in vb and would like to know how to pass a list created in the ListViw control to the database? so far I have built this:

  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 within the list:

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

I tried to do this:

    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

Thank you in advance!

vb.net

asked by anonymous 11.05.2015 / 17:47

1 answer

How to replace an attribute of an HTML snippet that is as a string? Error updating data

score 2 · Accepted Answer

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 recover 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()