VB.NET MySQL: How to Create, Read, Update, Delete (CRUD)

To support you in understanding this topic, please read also these articles:

I divide this topic into 4 parts. Make sure you have read the previous article.

How to Create, Read, Update, Delete (CRUD) - Part 1

Next step, we'll make edit and update data feature. How does users edit data? First, the will pick data row to edit, then double click on its row. Selected data will be displayed in input controls (textboxes). So, we need to write code under grdData_CellMouseDoubleClick event as following:

Private Sub grdData_CellMouseDoubleClick(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
    Handles grdData.CellMouseDoubleClick

    txtCode.Text = grdData.CurrentRow.Cells(0).Value
    txtName.Text = grdData.CurrentRow.Cells(1).Value
    tbrEdit.Enabled = False
    txtCode.ReadOnly = True

End Sub

Set txtCode.ReadOnly = True, to make txtCode not editable because it contains the primary key. Besides double-clicking on the grid, the user can also edit by selecting row data then clicking the Edit button. So we need similar procedure under the tbrEdit_Click event. But I'm too lazy to retype, our procedures can be reused. The trick is to just call the sub-event as below.

Private Sub tbrEdit_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles tbrEdit.Click

    grdData_CellMouseDoubleClick(Nothing, Nothing)

End Sub

Before proceeding with the save procedure, I want to create a handler under tbrCancel_Click
event. This works to clean controls and also cancel editing. This event procedure will be called several times while cleaning input controls, refresh data display, and canceling add new/edit process before it's saved.

Private Sub tbrCancel_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles tbrCancel.Click
    txtCode.Text = String.Empty
    txtName.Text = String.Empty
    tbrEdit.Enabled = True
    txtCode.ReadOnly = False
    Data_Load()
End Sub

Code tbrEdit.Enabled = False as a sign that process is adding or editing data. Disabled are for editing. Because we use the same event for saving data added and edited. We already used tbrSave_Click for adding data, the code below has been modified to adjust edit/update process. Highlight text is to show the modification.

Private Sub tbrSave_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles tbrSave.Click

    Dim myCommand As New MySqlCommand

    conn = New MySqlConnection()
    conn.ConnectionString = "server=localhost;user id=root;" & _
                            "password=;database=datapos"
    Try
        conn.Open()
        If tbrEdit.Enabled = True Then
            SQL = "INSERT INTO class (classcode, classname) VALUES " & _
                  "('" & txtCode.Text & "', '" & txtName.Text & "')"
        Else
            SQL = "UPDATE class SET classname = '" & txtName.Text & "' " & _
                  "WHERE classcode = '" & txtCode.Text & "'"
        End If

        myCommand.Connection = conn
        myCommand.CommandText = SQL
        myCommand.ExecuteNonQuery()

        If tbrEdit.Enabled = True Then
            MsgBox("Data baru tersimpan")
        Else
            MsgBox("Perubahan tersimpan")
        End If

        tbrCancel_Click(Nothing, Nothing)

        conn.Close()
    Catch myerror As MySqlException
        MessageBox.Show("Error: " & myerror.Message)
    Finally
        conn.Dispose()
    End Try
End Sub

Let's try to run and edit/update some data.