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


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.
Finally here is complete code. You can freely copy and paste it, but please understand each syntax purpose. Happy coding ^_^v

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class frmClass
 Dim conn As MySqlConnection
 Dim SQL As String

 Sub Data_Load()
    Dim myCommand As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter
    Dim myData As New DataTable

    conn = New MySqlConnection()
    conn.ConnectionString = "server=localhost;user id=root;" & _
                            "password=;database=datapos"
    Try
        If conn.State = ConnectionState.Closed Then conn.Open()
        SQL = "Select classcode, classname From class"

        myCommand.Connection = conn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)
        With grdData
            .DataSource = myData
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .ReadOnly = True

            .Columns(0).HeaderText = "Kode Kelas"
            .Columns(1).HeaderText = "Nama Kelas"
            .Columns(0).Width = 100
            .Columns(1).Width = 250
        End With

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


 Private Sub frmClass_Load(ByVal sender As Object, _
    ByVal As System.EventArgs) Handles Me.Load
    Data_Load()
 End Sub

 Private Sub tbrSave_Click(ByVal sender As System.Object, _
    ByVal 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(NothingNothing)

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


 Private Sub grdData_CellMouseDoubleClick(ByVal sender As Object, _
  ByVal 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

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

    grdData_CellMouseDoubleClick(NothingNothing)

 End Sub

 Private Sub tbrDelete_Click(ByVal sender As System.Object, _
    ByVal As System.EventArgs) Handles tbrDelete.Click

    If MsgBox("Yakin akan menghapus data?", MsgBoxStyle.YesNo, _
              "Konfirmasi") = MsgBoxResult.No Then Exit Sub

    Dim myCommand As New MySqlCommand

    conn = New MySqlConnection()
    conn.ConnectionString = "server=localhost;user id=root;" & _
                            "password=;database=datapos"
    Try
        conn.Open()
        SQL = "DELETE FROM class WHERE classcode = " & _
              "'" & grdData.CurrentRow.Cells(0).Value & "'"

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

        MsgBox("Data terhapus")

        tbrCancel_Click(NothingNothing)

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


 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

 Private Sub
 tbrClose_Click(ByVal sender As System.Object, _
    ByVal As System.EventArgs) Handles tbrClose.Click
    Me.Close()
 End Sub

End Class



This tutorial also available @youtube:


Post a Comment

0 Comments