vb.net 如何将每个 DataGridView 行插入到 MYSQL DB 中

How to Insert each DataGridView Row into MYSQL DB

提问by user2784251

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

我有这个 datagridview，它的行是通过单击按钮手动添加的。我想做的是以某种方式遍历每一行并将其插入到 mysql 数据库中

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...

但是，将每一行成功插入数据库时​​会出现错误。它告诉我参数@ctrlno 已经定义。另一个错误告诉我我需要一个有效且开放的连接......

Any ideas?

有任何想法吗？

Thanks in advance!

提前致谢！

回答by Johnbosco Adam

This will Also work

这也将起作用

For x As Integer = 0 To yourdatagridviewname.Rows.Count - 1

Dim str1 = "INSERT INTO [expensesTB]([amount],[description],[expensesDate])values(@amount,@description,@expensesDate)"
            Dim com As New OleDb.OleDbCommand(str1, con)
            com.Parameters.AddWithValue("@amount", yourdatagridviewname.Rows(x).Cells(2).Value)
            com.Parameters.AddWithValue("@description", yourdatagridviewname.Rows(x).Cells(1).Value)
            com.Parameters.AddWithValue("@expensesDate", thisyear.ToString("yyyy/MM/dd"))
            com.ExecuteNonQuery()
            com.Dispose()
        Next

回答by Riski Febriansyah

Try this if you want to insert the data that you manually input in the Datagridview into Mysql database.

如果你想把你在Datagridview中手动输入的数据插入到Mysql数据库中，试试这个。

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