Tuesday, May 23, 2017

[VISUAL BASIC.NET] Manipulasi Data MS.ACCESS dan VB.Net

1. Buat project dengan nama Database
2. Buat database dengan nama DBInventory.accdb menggunakan Microsoft Access 2010, simpan database tersebut di folder Database\Database\Database\bin\Debug pada project yang anda buat
3. Buat Tabel dengan nama TblBarang
Field Name
Data Type
Field Size
Primary Key
KodeBarang
Text
4
*
NamaBarang
Text
100

KodeJenis
Text
4

HargaNet
Currency


HargaJual
Currency


Stok
Number
Integer


4. Isi record TblBarang
KodeBarang
NamaBarang
KodeJenis
HargaNet
HargaJual
Stok
0001
Pulpen
J001
1000
1500
25
0002
Pensil
J001
1200
1700
30
0003
LED TV
J002
2000000
2500000
3
0004
Antena
J002
90000
120000
10

5. Buat Tabel dengan nama TblJenis
Field Name
Data Type
Field Size
Primary Key
KodeJenis
Text
4
*
Jenis
Text
60


6. Isi record TblJenis
KodeJenis
Jenis
J001
Alat Tulis Kantor
J002
Elektronik
J003
Alat Rumah Tangga

7. Desain User Interface sebagai berikut :


8. Source code lengkap :

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
Imports System.Data.OleDb

Public Class Form1
    Public koneksi As String
    Public sql1, sql2 As String
    Public conn As OleDbConnection
    Public cmd As OleDbCommand
    Public RS As OleDbDataReader
    Public dtadapter As New OleDbDataAdapter
    Public dttable As New DataTable

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        koneksi = "provider=microsoft.ace.oledb.12.0;data source=DBInventory.accdb"
        conn = New OleDbConnection(koneksi)
        conn.Open()
        daftar()
        isiCombo()
        posisiTambah(False)
    End Sub

    Sub daftar()
        sql2 = "SELECT * FROM tblBarang"
        dtadapter = New OleDbDataAdapter(sql2, conn)
        Dim BRG As New DataTable
        BRG.Clear()
        dtadapter.Fill(BRG)
        dgvBarang.DataSource = BRG
    End Sub

    Sub bersihkan()
        txtKDBarang.Text = ""
        txtNamaBarang.Text = ""
        cbJenis.Text = ""
        txtHargaNet.Text = ""
        txtHargaJual.Text = ""
        txtStok.Text = ""
        lblNamaJenis.Text = "Nama Jenis Barang"
    End Sub

    Sub posisiTambah(ByVal posisi As Boolean)
        txtKDBarang.Enabled = posisi
        txtNamaBarang.Enabled = posisi
        cbJenis.Enabled = posisi
        txtHargaNet.Enabled = posisi
        txtHargaJual.Enabled = posisi
        txtStok.Enabled = posisi
        btnBaru.Enabled = Not posisi
        btnBatal.Enabled = posisi
        btnSimpan.Enabled = posisi
    End Sub

    Sub simpan()
        sql1 = "INSERT INTO TBLBARANG VALUES('" & txtKDBarang.Text & "','" & txtNamaBarang.Text & "','" &
            cbJenis.Text & "','" & txtHargaNet.Text & "','" & txtHargaJual.Text & "','" &
            txtStok.Text & "')"
        cmd = New OleDbCommand(sql1)
        cmd.Connection = conn
        cmd.ExecuteNonQuery()
    End Sub

    Private Sub btnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
        simpan()
        daftar()
        bersihkan()
        posisiTambah(False)
        MsgBox("Data Sudah Disimpan", MsgBoxStyle.Information, "Perhatian")
    End Sub

    Private Sub btnBatal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBatal.Click
        bersihkan()
        posisiTambah(False)
        MsgBox("Transaksi dibatalkan", MsgBoxStyle.Information, "Perhatian")
    End Sub

    Private Sub btnBaru_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBaru.Click
        bersihkan()
        posisiTambah(True)
        txtKDBarang.Focus()
    End Sub

    Private Sub btnKeluar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKeluar.Click
        Close()
    End Sub

    Sub isiCombo()
        sql2 = "SELECT KODEJENIS FROM tblJenis"
        cmd = New OleDbCommand(sql2, conn)
        RS = cmd.ExecuteReader
        cbJenis.Items.Clear()
        Do While RS.Read()
            cbJenis.Items.Add(RS("KodeJenis"))
        Loop
    End Sub

    Private Sub cbJenis_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbJenis.SelectedIndexChanged
        sql2 = "SELECT * FROM tblJenis WHERE KODEJENIS ='" & cbJenis.Text & "'"
        cmd = New OleDbCommand(sql2, conn)
        RS = cmd.ExecuteReader
        RS.Read()
        If RS.HasRows Then
            lblNamaJenis.Text = RS("JENIS")
        End If
    End Sub

    Private Sub dgvBarang_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvBarang.Click
        Dim i = dgvBarang.CurrentRow.Index
        txtKDBarang.Text = dgvBarang.Item(0, i).Value
        txtNamaBarang.Text = dgvBarang.Item(1, i).Value
        cbJenis.Text = dgvBarang.Item(2, i).Value
        txtHargaNet.Text = dgvBarang.Item(3, i).Value
        txtHargaJual.Text = dgvBarang.Item(4, i).Value
        txtStok.Text = dgvBarang.Item(5, i).Value
    End Sub
End Class


9. Hasil eksekusi


on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)