MS Access : Source Code Lengkap Input & Edit Data

Imports System.Data.OleDb

Imports System.Data

Public Class Form1

    Dim idpelanggan As String

    Dim namapelanggan As String

    Dim alamatpelanggan As String

    Dim oledbKoneksi As OleDbConnection = New OleDbConnection

    Dim oledbPerintah As OleDbCommand = New OleDbCommand

    Dim lokasiFile As String = "D:\fileAccess\"

    Dim namaFile As String = "Database1.accdb"

    Dim stringKoneksi As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & lokasiFile & namaFile & ";" & "Persist Security Info=False;"

    Dim adapter As New OleDbDataAdapter


    Sub tampilIsiGrid()

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "Select * From datapelanggan"

        adapter.SelectCommand = oledbPerintah

        Dim ds As DataSet

        ds = New DataSet

        adapter.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

        oledbKoneksi.Close()


    End Sub


    Sub autoID()

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "SELECT * FROM datapelanggan ORDER BY idpelanggan desc"

        Dim baca As OleDbDataReader = oledbPerintah.ExecuteReader

        baca.Read()

        If Not baca.HasRows Then

            TextBox1.Text = "DP" + "0001"

        Else

            TextBox1.Text = Val(Microsoft.VisualBasic.Mid(baca.Item("idpelanggan").ToString, 4, 3)) + 1

            If Len(TextBox1.Text) = 1 Then

                TextBox1.Text = "DP000" & TextBox1.Text & ""

            ElseIf Len(TextBox1.Text) = 2 Then

                TextBox1.Text = "DP00" & TextBox1.Text & ""

            ElseIf Len(TextBox1.Text) = 3 Then

                TextBox1.Text = "DP0" & TextBox1.Text & ""


            End If
        End If

        oledbKoneksi.Close()


    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        'Simpan Data

        idpelanggan = TextBox1.Text

        namapelanggan = TextBox2.Text

        alamatpelanggan = TextBox3.Text

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "INSERT INTO datapelanggan(idpelanggan,namapelanggan,alamatpelanggan) values" & "('" & idpelanggan & "', '" & namapelanggan & "', '" & alamatpelanggan & "')"

        oledbPerintah.ExecuteNonQuery()

        oledbKoneksi.Close()

        TextBox2.Text = ""

        TextBox3.Text = ""

        autoID()

        DataGridView1.Refresh()

        tampilIsiGrid()



    End Sub

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

        autoID()


        tampilIsiGrid()


    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

        'Data Baru

        autoID()

        TextBox2.Text = ""

        TextBox3.Text = ""



    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        'Tampil data dari datagridview ke textbox

        Dim row As DataGridViewRow = DataGridView1.CurrentRow

        Me.TextBox1.Text = row.Cells(0).Value.ToString

        Me.TextBox2.Text = row.Cells(1).Value.ToString

        Me.TextBox3.Text = row.Cells(2).Value.ToString


    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click

        'tombol untuk mengupdate data

        idpelanggan = TextBox1.Text

        namapelanggan = TextBox2.Text

        alamatpelanggan = TextBox3.Text

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "UPDATE datapelanggan SET namapelanggan = '" & namapelanggan & "', alamatpelanggan = '" & alamatpelanggan & "' WHERE idpelanggan = '" & idpelanggan & "'"

        oledbPerintah.ExecuteNonQuery()

        oledbKoneksi.Close()

        TextBox2.Text = ""

        TextBox3.Text = ""

        autoID()

        DataGridView1.Refresh()

        tampilIsiGrid()

    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click

        'Hapus Data

        idpelanggan = TextBox1.Text

        namapelanggan = TextBox2.Text

        alamatpelanggan = TextBox3.Text

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "DELETE * FROM datapelanggan WHERE idpelanggan = '" & idpelanggan & "'"

        oledbPerintah.ExecuteNonQuery()

        oledbKoneksi.Close()

        TextBox2.Text = ""

        TextBox3.Text = ""

        autoID()

        DataGridView1.Refresh()

        tampilIsiGrid()


    End Sub
End Class