How to Insert each DataGridView Row into MYSQL DB

Question

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!

Johnbosco Adam · Accepted Answer · 2015-10-04 09:19:47Z

1

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

answered Oct 4, 2015 at 9:19

Johnbosco Adam

911 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark · Accepted Answer · 2016-03-03 23:35:01Z

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

Collectives™ on Stack Overflow

How to Insert each DataGridView Row into MYSQL DB

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related