保存DataGridView数据到Access数据库

Question

我正在尝试将DataGridView数据保存到Access数据库(数据库已经连接到Visual Studio)。

下面是我的代码:

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

Answer 1

需要处置Connections和Commands，以便它们可以释放未管理的资源。Using...End Using块是处理这个问题的最好方法，因为即使有错误，也会这样做。在此代码1中，连接和命令都包含在Using块中。注意Using第一行末尾的逗号。

将CommandText和Connection直接传递给命令的构造函数。我注意到你漏掉了字段列表中的一个参数。我还注意到你键入了"插入"。我相信一定是"插入"。没有必要设置CommandType，因为CommandType.Text是默认的。

在OleDb中，参数在sql字符串中出现的顺序必须与它们添加到parameters集合中的顺序相匹配。

在循环外构建参数集合。只有参数的值在循环中改变。

在循环外打开连接一次。

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

有更简单的方法可以做到这一点。如果您有一个DataTable绑定到DataGridView，您可以使用DataAdpater.Update方法。