如何将每个DataGridView行插入到MYSQL DB中 [英] How to Insert each DataGridView Row into MYSQL DB
问题描述
我想做的是以某种方式循环遍历每一行并将其插入到一个mysql数据库中
这里是我当前的代码:
Public Sub addResBulk()
Dim cmdAddRes作为新的MySqlCommand
Dim addResQry As String
'字段备注留空,直到过程完成
使用cmdAddRes
.Parameters.AddWithValue(@ ctrlno,ctrlNo)
.Parameters.AddWithValue(@ resdate,dtp_resDate.Value)
.Parameters.AddWithValue (@timestart,cbo_start.SelectedValue)
.Parameters.AddWithValue(@ timeend,cbo_end.SelectedValue)
.Parameters.AddWithValue(@ claimdate,claimdate)
。参数.AddWithValue(@ borrowerid,tb_borrowerID.Text)
.Parameters.AddWithValue(@ resloc,tb_location.Text)
结束
对于行As Integer = 0 To dgv_bulk.Rows.Count - 1
尝试
addResQry =INSERT INTO tbl_test(ControlNo,bCo de,数量,resDate,timeSTART,timeEND,claimDate,borrowerID,resLocation)VALUES_
+(@ctrlno,@bcode,@qty,@resdate,@timestart,@timeend,@claimdate,@borrowerID, @resloc)
如果conn.State = ConnectionState.Open然后
conn.Close()
conn.Open()
Else
conn。 Open()
End If
'dgv_bulk.Item(1,o).Value
使用cmdAddRes
.Parameters.AddWithValue(@ bcode ,dgv_bulk.Item(1,row).Value)
.Parameters.AddWithValue(@ qty,dgv_bulk.Item(2,row).Value)
qryRes = .ExecuteNonQuery
结束
conn.Close()
Catch ex As Exception
MsgBox(ex.Message.ToString)
结束尝试
下一行
End Sub
然而,一个错误阻碍了成功插入每个行进入数据库。
它告诉我参数@ctrlno已经定义。另一个错误告诉我,我需要一个有效和开放的连接...
任何想法?
谢谢提前!
如果您要将Datagridview中手动输入的数据插入到Mysql数据库中,请尝试此方法。 p>
希望这将有助于您。
public Sub addResBulk()
''创建连接
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
conn.Open()
Dim comm As MySqlCommand = New MySqlCommand()
comm.Connection = conn
''将数据逐行插入到
Dim ctrlno,bcode,resdate,timestart ,timeend,claimdate,borrowerID,resloc As Object
Dim qty As Double
Dim tbl_test As New DataTable
For i = 0 To DataGridView1.Rows.Add - 1步骤1
ctrlno = DataGri dView1.Rows(i).Cells(0).Value()
bcode = DataGridView1.Rows(i).Cells(1).Value()
qty = DataGridView1.Rows(i).Cells (2).Value()
resdate = DataGridView1.Rows(i).Cells(3).Value()
timestart = DataGridView1.Rows(i).Cells(4).Value()
timeend = DataGridView1.Rows(i).Cells(5).Value()
claimdate = DataGridView1.Rows(i).Cells(6).Value()
borrowerID = DataGridView1.Rows (i).Cells(7).Value()
resloc = DataGridView1.Rows(i).Cells(8).Value()
comm.CommandText =insert into tbl_test ControlNo,bCode,Qty,resDate,timeSTART,timeEND,claimDate,borrowerID,resLocation)values('& ctrlno& ,& bcode& ,&数量& ,&恢复和,&时间和时间,&时间和时间,&声明和,&借款人& ,& resloc& ')
comm.ExecuteNonQuery()
下一个
conn.Close()
Me.Close()
如果
End Sub
I have this datagridview whose rows are manually added on a click of a button. What I wanted to do was to somehow loop through each row and insert it in a mysql database
here is my current code:
Public Sub addResBulk()
Dim cmdAddRes As New MySqlCommand
Dim addResQry As String
'field remarks left empty until process complete
With cmdAddRes
.Parameters.AddWithValue("@ctrlno", ctrlNo)
.Parameters.AddWithValue("@resdate", dtp_resDate.Value)
.Parameters.AddWithValue("@timestart", cbo_start.SelectedValue)
.Parameters.AddWithValue("@timeend", cbo_end.SelectedValue)
.Parameters.AddWithValue("@claimdate", claimdate)
.Parameters.AddWithValue("@borrowerid", tb_borrowerID.Text)
.Parameters.AddWithValue("@resloc", tb_location.Text)
End With
For row As Integer = 0 To dgv_bulk.Rows.Count - 1
Try
addResQry = "INSERT INTO tbl_test(ControlNo, bCode, Qty, resDate, timeSTART, timeEND, claimDate, borrowerID, resLocation) VALUES " _
+ "(@ctrlno, @bcode, @qty, @resdate, @timestart, @timeend, @claimdate, @borrowerID, @resloc)"
If conn.State = ConnectionState.Open Then
conn.Close()
conn.Open()
Else
conn.Open()
End If
'dgv_bulk.Item(1, o).Value
With cmdAddRes
.Parameters.AddWithValue("@bcode", dgv_bulk.Item(1, row).Value)
.Parameters.AddWithValue("@qty", dgv_bulk.Item(2, row).Value)
qryRes = .ExecuteNonQuery
End With
conn.Close()
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
Next row
End Sub
However, an error gets in the way of successfully inserting each row into the database. it tells me that parameter @ctrlno is already defined. and another error telling me that i need a valid and open connection...
Any ideas?
Thanks in advance!
Try this if you want to insert the data that you manually input in the Datagridview into Mysql database.
Hope this will help you.
Public Sub addResBulk()
''create connection
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
conn.Open()
Dim comm As MySqlCommand = New MySqlCommand()
comm.Connection = conn
''insert data to sql database row by row
Dim ctrlno, bcode, resdate, timestart, timeend, claimdate, borrowerID, resloc As Object
Dim qty As Double
Dim tbl_test As New DataTable
For i = 0 To DataGridView1.Rows.Add - 1 Step 1
ctrlno = DataGridView1.Rows(i).Cells(0).Value()
bcode = DataGridView1.Rows(i).Cells(1).Value()
qty = DataGridView1.Rows(i).Cells(2).Value()
resdate = DataGridView1.Rows(i).Cells(3).Value()
timestart = DataGridView1.Rows(i).Cells(4).Value()
timeend= DataGridView1.Rows(i).Cells(5).Value()
claimdate = DataGridView1.Rows(i).Cells(6).Value()
borrowerID = DataGridView1.Rows(i).Cells(7).Value()
resloc = DataGridView1.Rows(i).Cells(8).Value()
comm.CommandText = "insert into tbl_test(ControlNo,bCode,Qty,resDate,timeSTART,timeEND,claimDate,borrowerID,resLocation ) values('" & ctrlno & "','" & bcode & "','" & qty & "','" & resdate & "','" & timestart & "','" & timeend & "','" & claimdate & "','" & borrowerID & "','" & resloc & "')"
comm.ExecuteNonQuery()
Next
conn.Close()
Me.Close()
End If
End Sub
这篇关于如何将每个DataGridView行插入到MYSQL DB中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!