Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.IO

Public Class Form1
    Dim conn As MySqlConnection
    Dim SQL As String
    Dim s As Integer = 0
    Dim d As Path

    Private Sub Form1_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Load

        Data_Load()

    End Sub
    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=inventaris;"
        Try
            If conn.State = ConnectionState.Closed Then conn.Open()
            SQL = "Select no_pol, type, user, tahun_pembuatan, tanggal_terbit, pajak_berakhir From mobil"

            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 = "NOMOR POLISI"
                .Columns(1).HeaderText = "TYPE"
                .Columns(2).HeaderText = "USER"
                .Columns(3).HeaderText = "TAHUN PEMBUATAN"
                .Columns(4).HeaderText = "TANGGAL TERBIT STNK"
                .Columns(5).HeaderText = "PAJAK BERAKHIR STNK"


                .Columns(0).Width = 100
                .Columns(1).Width = 150
                .Columns(2).Width = 150
                .Columns(3).Width = 100
                .Columns(4).Width = 100
                .Columns(5).Width = 100

            End With


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



    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=inventaris"
        Try
            conn.Open()
            If tbrEdit.Enabled = True Then
                SQL = "INSERT INTO mobil (no_pol, type, user, tahun_pembuatan,tanggal_terbit,pajak_berakhir) VALUES " & _
                      "('" & txtNoPol.Text & "', '" & txtType.Text & "', '" & txtUser.Text & "', '" & txtTahunPembuatan.Text & "', '" & txtTanggalTerbit.Text & "', '" & txtPajakBerakhir.Text & "')"
            Else
                SQL = "UPDATE mobil SET type='" & txtType.Text & "', user='" & txtUser.Text & "',tahun_pembuatan='" & txtTahunPembuatan.Text & "',tanggal_terbit='" & txtTanggalTerbit.Text & "',pajak_berakhir='" & txtPajakBerakhir.Text & "'WHERE no_pol='" & txtNoPol.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

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


        txtNoPol.Text = grdData.CurrentRow.Cells(0).Value
        txtType.Text = grdData.CurrentRow.Cells(1).Value
        txtUser.Text = grdData.CurrentRow.Cells(2).Value
        txtTahunPembuatan.Text = grdData.CurrentRow.Cells(3).Value
        txtTanggalTerbit.Text = grdData.CurrentRow.Cells(4).Value
        txtPajakBerakhir.Text = grdData.CurrentRow.Cells(5).Value

            tbrEdit.Enabled = False
            txtNoPol.ReadOnly = True

    End Sub

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

        grdData_CellMouseDoubleClick(Nothing, Nothing)

    End Sub

    Private Sub tbrCancel_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles tbrCancel.Click
        txtNoPol.Text = String.Empty
        txtType.Text = String.Empty
        txtUser.Text = String.Empty
        txtTahunPembuatan.Text = String.Empty
        txtTanggalTerbit.Text = String.Empty
        txtPajakBerakhir.Text = String.Empty

        tbrEdit.Enabled = True
        txtNoPol.ReadOnly = False
        Data_Load()
    End Sub

    Private Sub tbrDelete_Click(ByVal sender As System.Object, _
     ByVal e 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=inventaris"
        Try
            conn.Open()
            SQL = "DELETE FROM mobil WHERE no_pol = " & _
                  "'" & grdData.CurrentRow.Cells(0).Value & "'"

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

            MsgBox("Data terhapus")

            tbrCancel_Click(Nothing, Nothing)

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




End Class