正在将Datagridview数据上载到MySQL数据库

Question

我创建了一个简单的数据从CSV到datagridview的大规模上传，并将所有datagridview行保存到我的MySQL表中，代码运行良好，但当我检查数据库时，它会在我的表中插入一个空值，这就是我的代码。

这是我从Datagridview 添加所有值的按钮

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As MySqlCommand
connection.Open()
Dim i As Integer
For i = 0 To DataGridView1.Rows.Count - 2
Dim row As DataGridViewRow = DataGridView1.Rows(i)
cmd = New MySqlCommand("INSERT INTO tbl_handling(tbl_docnumber,tbl_bpref,tbl_cname) values (@docnumber,@bref,@cname)", connection)
cmd.Parameters.Add("@docnumber", MySqlDbType.Int64).Value = DataGridView1.Rows(i).Cells(0).Value.ToString
cmd.Parameters.Add("@bref", MySqlDbType.VarChar).Value = DataGridView1.Rows(i).Cells(1).Value.ToString
cmd.Parameters.Add("@cname", MySqlDbType.VarChar).Value = DataGridView1.Rows(i).Cells(2).Value.ToString
cmd.ExecuteNonQuery()
Next
connection.Close()
connection.Dispose()

MessageBox.Show("Data All Uploaded")
End Sub

这是我在数据网格视图中插入CSV文件的代码

Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectData.Click
Dim fName As String = ""
OpenFileDialog1.InitialDirectory = "D:TestFile"
OpenFileDialog1.Filter = "CSV files(*.csv)|*.csv"
OpenFileDialog1.RestoreDirectory = True
Dim colespected As Integer = 5
Dim sline As String = ""

If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
fName = OpenFileDialog1.FileName
Dim thereader As New StreamReader(fName, Encoding.Default)
Do
sline = thereader.ReadLine
If sline Is Nothing Then Exit Do
Dim words() As String = sline.Split(",")
DataGridView1.Rows.Add("")
For ix As Integer = 0 To 2
DataGridView1.Rows(DataGridView1.Rows.Count - 1).Cells(ix).Value = words(ix)
Next
Loop
thereader.Close()
End If
End Sub

Answer 1

我假设您的数据在网格中成功显示。所以我只是在谈论Button事件。

命令和连接都需要关闭和处理。Using...End Using块为您完成此操作。(BTWStream对象也应该在Using块中。(数据库对象应该是使用它们的方法的本地对象。如果你.Dispose一个连接，你希望以后如何.Open？命令和参数集合只在循环外创建一次。只有.Value在循环内发生变化。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Using connection As New MySqlConnection(ConStr),
cmd As New MySqlCommand("INSERT INTO tbl_handling(tbl_docnumber,tbl_bpref,tbl_cname) values (@docnumber,@bref,@cname)", connection)
cmd.Parameters.Add("@docnumber", MySqlDbType.Int64)
cmd.Parameters.Add("@bref", MySqlDbType.VarChar)
cmd.Parameters.Add("@cname", MySqlDbType.VarChar)
connection.Open()
For i = 0 To DataGridView1.Rows.Count - 2
cmd.Parameters("@docnumber").Value = CLng(DataGridView1.Rows(i).Cells(0).Value)
cmd.Parameters("@bref").Value = DataGridView1.Rows(i).Cells(1).Value.ToString
cmd.Parameters("@cname").Value = DataGridView1.Rows(i).Cells(2).Value.ToString
cmd.ExecuteNonQuery()
Next
End Using
MessageBox.Show("Data All Uploaded")
End Sub

我有点担心tbl_docnumber是数据库中的一个自动编号(身份(主键。如果是这样，则Insert中应省略此字段。

如果您填写DataTable而不是DataGridView，则此操作会更容易。然后，您可以使用DataAdapter来更新数据库。