Using a gridview to submit data to mysql database using paramters but with extra sql statatement "on duplicate key update"

Question

I am having trouble constructing code that will enable me to insert gridview data into mysql database with on duplicate key update sql statement.

The gridview is just a normal gridview which is connected to a browse and upload button.This enables me to upload excel spread sheets to my gridview - which is not databound.

I have done this in VB but am finding it an arduous task writing it in vb.net. Here is the code in VB.

Dim connectionString As String = "Server=*****;Database=****;Uid=****;Pwd=r*****;allow user variables=true"
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles Button2.Click

    Dim i As Integer = 0
    Dim student id,name,age,adress dbSQL_query As String
    Dim dbSQL_cmd As New SqlClient.SqlCommand
    Dim dbSQL_con As New SqlClient.SqlConnection




    Using SQLConnection As New MySqlConnection(connectionString)

        SQLConnection.Open()

        For i = 0 To DataGridView1.RowCount - 1
            Using sqlCommand As New MySqlCommand()
                student id = DataGridView1.Item(0, i).Value.ToString
                name = DataGridView1.Item(1, i).Value.ToString
                age = DataGridView1.Item(2, i).Value.ToString
               adress = DataGridView1.Item(3, i).Value.ToString.ToLower
                dbSQL_query = "INSERT INTO student)  VALUES  (" + addQoute(student_id) + ", " + _
                      addQoute(name) + ", " + _
                      addQoute(age) + ", " + _
                      addQoute(adress) + ", " + _

                      addQoute(status) + ") on duplicate key update name=(" + addQoute(name) + "),age=(" + addQoute(age) + "),adress=(" + addQoute(adress) + ")"
                With sqlCommand

                    .CommandText = dbSQL_query
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text

                End With
                sqlCommand.ExecuteNonQuery()
                dbSQL_cmd = New SqlClient.SqlCommand(dbSQL_query, dbSQL_con)


            End Using

        Next



        'MessageBox.Show(DataGridView1.RowCount & " student details has been inserted")
        SQLConnection.Close()
    End Using

End Sub



Public Function addQoute(ByVal str As String) As String
    str = Trim(str)
    Return "'" + str + "'"
End Function

score 0 · Accepted Answer · edited May 23 '17 at 12:18

I have changed your code to use MySql objects and to add MySqlParameter for each of your values (except the not found field status).

Dim i As Integer = 0 
Dim student_id,name,age,adress, dbSQL_query As String 
Dim dbSQL_cmd As MySqlCommand 
Dim dbSQL_con As MySqlConnection 

Using dbSQL_con As New MySqlConnection(connectionString) 
    dbSQL_con.Open() 

    For i = 0 To DataGridView1.RowCount - 1 
        Using dbSQL_cmd As New MySqlCommand() 
            student_id = DataGridView1.Item(i, 0).Value.ToString 
            name = DataGridView1.Item(i, 1).Value.ToString 
            age = DataGridView1.Item(i, 2).Value.ToString 
            adress = DataGridView1.Item(i, 3).Value.ToString.ToLower 
            dbSQL_query = "INSERT INTO student  VALUES  (?id, ?name, ?age, ?adress) " + 
                   " on duplicate key update name=(name=?name, age=?age, adress=?adress)"
            With dbSQL_cmd
                .Parameters.AddWithValue("?id", student_id)
                .Parameters.AddWithValue("?name", name)
                .Parameters.AddWithValue("?age", age)
                .Parameters.AddWithValue("?adress", adress)
                ' .Parameters.AddWithValue("?status", ?????)
                .CommandText = dbSQL_query 
                .Connection = dbSQL_con 
                .CommandType = CommandType.Text 
            End With 
            dbSQL_cmd.ExecuteNonQuery() 
        End Using 
    Next 
    'MessageBox.Show(DataGridView1.RowCount & " student details has been inserted") 
End Using

Please, never use strings to create an sql command, always use parameters.
This is important because your code could be used to create Sql Injection Attacks, moreover, you don't need to create special functions to parse string for sql delimiters like your AddQuote.

Last thing, the Using statement dispose the objects that implements the IDisposable interface like SqlCommand and SqlConnection, in the disposing process the MySqlConnection will be automatically closed.

is there a way to get an msgbox show how many studens went in as updates and how many are new entries? — user1712552, Oct 21 '12 at 16:14

Using a gridview to submit data to mysql database using paramters but with extra sql statatement "on duplicate key update"

1 Answers1