I am trying to save a DataGridView data to an Access database (database is already connected to Visual Studio).

Here's my code:

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

    Try

        Dim cn As OleDbConnection
        cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|\GEFORSERVI V2.1.accdb;")

        cn.Open()

        Dim cmd As New OleDbCommand

        cmd.Connection = cn

        cmd.CommandType = Data.CommandType.Text

        Dim Strcommandtext As String = "inserto into Factura(Designacao,PrecoUnitario,Qtd,Total,Nome,Entidade,NIF,Telefone,Morada,CodigoProduto,DataEmissao) VALUES(@Servico_Produto,@Valor,@Qtd,@Total,@Nome,@Entidade,@NIF,@Telemovel,@Bairro,@Data_de_Emissao)"

        Dim values As String = ""

        For i As Integer = 0 To Factura2DataGridView.Rows.Count - 1
            values = Strcommandtext & Factura2DataGridView.Rows(i).Cells(11).Value & ")"

            cmd.CommandText = values

            cmd.ExecuteNonQuery()

        Next i

        cmd = Nothing

        cn.Close()

        MsgBox("Your Record Inserted Successfully ")

    Catch myException As Exception

        MsgBox("No Record Inserted"   myException.ToString())

    Finally

        'MsgBox("Closing Connection")

    End Try

End Sub

CodePudding user response：

Connections and Commands need to be disposed so they can release unmanaged resources. Using...End Using blocks are the best way to handle this since the will do this even if there is an error. In this code I both the connection and command are included in the Using block. Note the comma at the end of the first Using line.

Pass the CommandText and the Connection directly to the constructor of the command. I noticed you had missed one of the parameters that was in the field list. I also noticed the you typed "inserto". I believe it must be "Insert". It is not necessary to set CommandType since CommandType.Text is the default.

In OleDb the order that the parameters appear in the sql string must match the order that they are added to the Parameters collection.

Build the parameters collection once outside the loop. It is only the Values of the parameters that change in the loop.

Open the connection once, outside the loop.

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Try
        Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|\GEFORSERVI V2.1.accdb;"),
                cmd As New OleDbCommand("Insert into Factura(Designacao,PrecoUnitario,Qtd,Total,Nome,Entidade,NIF,Telefone,Morada,CodigoProduto,DataEmissao) 
                VALUES(@Servico_Produto,@Valor,@Qtd,@Total,@Nome,@Entidade,@NIF,@Telemovel,@Bairro,@CodigoProduto,@Data_de_Emissao)", cn)
            With cmd.Parameters
                .Add("@Servico_Produto", OleDbType.Integer)
                .Add("@Valor", OleDbType.VarWChar)
                .Add("@Qtd", OleDbType.Integer)
                .Add("@Total", OleDbType.Decimal)
                .Add("@Nome", OleDbType.VarWChar)
                .Add("@Entidade", OleDbType.VarWChar)
                .Add("@NIF", OleDbType.VarWChar)
                .Add("@Telemovel", OleDbType.VarWChar)
                .Add("@Bairro", OleDbType.VarWChar)
                .Add("@CodigoProduto", OleDbType.Integer)
                .Add("@Data_de_Emissao", OleDbType.Date)
            End With
            cn.Open()
            For i As Integer = 0 To Factura2DataGridView.Rows.Count - 1
                cmd.Parameters("@Servico_Produto").Value = Factura2DataGridView.Rows(i).Cells(0).Value
                cmd.Parameters("@Valor").Value = Factura2DataGridView.Rows(i).Cells(1).Value
                cmd.Parameters("@Qtd").Value = Factura2DataGridView.Rows(i).Cells(2).Value
                cmd.Parameters("@Total").Value = Factura2DataGridView.Rows(i).Cells(3).Value
                cmd.Parameters("@Nome").Value = Factura2DataGridView.Rows(i).Cells(4).Value
                cmd.Parameters("@Entidade").Value = Factura2DataGridView.Rows(i).Cells(5).Value
                cmd.Parameters("@NIF").Value = Factura2DataGridView.Rows(i).Cells(6).Value
                cmd.Parameters("@Telemovel").Value = Factura2DataGridView.Rows(i).Cells(7).Value
                cmd.Parameters("@Bairro").Value = Factura2DataGridView.Rows(i).Cells(8).Value
                cmd.Parameters("@CodigoProduto").Value = Factura2DataGridView.Rows(i).Cells(9).Value
                cmd.Parameters("@Data_de_Emissao").Value = Factura2DataGridView.Rows(i).Cells(10).Value
                cmd.ExecuteNonQuery()
            Next
        End Using
        MsgBox("Your Record Inserted Successfully ")
    Catch myException As Exception
        MsgBox("No Record Inserted"   myException.Message)
    End Try
End Sub

There are easier ways to do this. If you have a DataTable bound to the DataGridView you can use a DataAdpater.Update method.