Simple not just a words

VB.NET Database Ms. Access

VB.NET Database Ms. Access


Membuat project vb.net menggunakan database Ms. Access, pastikan sudah terinstal software microsoft visual studio dan ms. access nya ya, kali ini saya menggunakan vb.net ver. 2010 dan ms. access 2013,, 


Dalam pembahasan kali ini sudah saya sertakan coding dan juga screen shoot supaya mempermudah dalam membuat projectnya..


Dalam project kali ini saya menggunakan database bernama db_pesan dan dlm tabel pesan


Tampilan program saat running







Sekarang saat nya coding

'Masukkan koding ini sebagai pemanggil database yg ingin di gunakan

Imports System.Windows.Forms.VisualStyles.VisualStyleElement.Menu
Imports System.Data.OleDb
Imports System.IO

Public Class Form1

    Private Conn As OleDbConnection = Nothing
    Private cmd As OleDbCommand = Nothing
    Private sql As String = Nothing
    Private reader As OleDbDataReader = Nothing
    Private da As OleDbDataAdapter = Nothing

    Function Connect()

        If Not Conn Is Nothing Then
            Conn.Close()
        End If
        Conn.Open()
        Return Conn

    End Function

    Function Closedd()

        Conn.Close()
        Return Conn

    End Function

    Sub LoadData()

        Connect()
        sql = "select Kode from pesan"
        cmd = New OleDbCommand(sql, Conn)
        reader = cmd.ExecuteReader()
        Try
            cmbkode.Items.Clear()
            While reader.Read
                cmbkode.Items.Add(reader.GetString(0))
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Closedd()

    End Sub

    Sub Tampil()

        Connect()
        Try
            Dim dt As New DataTable
            da = New OleDbDataAdapter("select * from pesan", Conn)
            da.Fill(dt)
            DataGridView1.DataSource = dt
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

        Closedd()

    End Sub

 Dim txttharga As Object

    'Untuk Menghapus riwayat dari transaksi yang telah di lakukan
    Sub bersih()
        Me.cmbkode.Text = ""
        Me.txtnama.Clear()
        Me.txtharga.Clear()
        Me.txtlama.Clear()
        Me.txttotal.Clear()
        Me.txtjumlah.Clear()
        Me.txtkembali.Clear()
        Me.cblama.Text = "==Waktu Peminjaman=="
        Me.cboffline.Text = "=Nama Game="
        Me.rbrpg.Text = "RPG"

    End Sub

    'Menampilkan DVD game yang bisa dipilih saat combo box di klik setelah pilihan pada radio button di klik

    Sub rpg()
        Me.cboffline.Items.Clear()
        Me.cboffline.Items.Add("GTA SAN")
        Me.cboffline.Items.Add("NARUTO")

    End Sub

    'Menampilkan sub pilihan yang ada didalam tiap kategori pada menu combo box

    Private Sub rbrpg_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbrpg.CheckedChanged
        rpg()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnew.Click
        bersih()
    End Sub

    'Menampilkan jumlah biaya dari perincian lama menyewa saat "combo lama" di klik yang kemudian di akan muncul pada "text box lama"
    Private Sub cblama_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cblama.SelectedIndexChanged
        If Me.cblama.Text = "3 Hari" Then
            Me.txtlama.Text = 10000
        ElseIf Me.cblama.Text = "4 Hari" Then
            Me.txtlama.Text = 12000
        ElseIf Me.cblama.Text = "5 Hari" Then
            Me.txtlama.Text = 14000
        ElseIf Me.cblama.Text = "6 Hari" Then
            Me.txtlama.Text = 16000
        ElseIf Me.cblama.Text = "7 Hari" Then
            Me.txtlama.Text = 18000
        End If

    End Sub


    Private Sub bthitung_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bthitung.Click
        'Untuk Mencari Jumlah dari sub harga = "Harga + Lama waktu penyewaan"
        Dim b1, b2 As New Integer
        Dim h As New Double
        b1 = Val(txttotal.Text)
        b2 = Val(txtjumlah.Text)
        h = b2 - b1
        txtkembali.Text = h

    End Sub


    Private Sub btclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btclose.Click
        Dim pesan As DialogResult
        pesan = MessageBox.Show("Anda Telah Menggunakan aplikasi ini", "TERIMA KASIH", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Me.Close()

    End Sub

    'Untuk Mencari Jumlah dari sub harga = "Harga + Lama waktu penyewaan"
    Private Sub btkembali_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btkembali.Click

        Dim b1, b2 As New Integer
        Dim h As New Double
        b1 = Val(txtharga.Text)
        b2 = Val(txtlama.Text)
        h = b1 + b2
        txttotal.Text = h
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Db_pesanDataSet5.pesan' table. You can move, or remove it, as needed.
        Me.PesanTableAdapter.Fill(Me.Db_pesanDataSet5.pesan)


        Dim ConnString As String

        ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\db_pesan.mdb"
        Try
            Conn = New OleDbConnection(ConnString)
            Conn.Open()
            Me.Text = "dvd"
            LoadData()
            Tampil()
            Conn.Close()
        Catch ex As Exception
            MessageBox.Show("Koneksi Error : " + ex.Message)
        End Try
        Me.cboffline.Items.Add("GTA SAN")
        Me.cboffline.Items.Add("NARUTO")

    End Sub

    Private Sub cboffline_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboffline.SelectedIndexChanged

        If Me.cboffline.Text = "GTA SAN" Then
            Me.txtharga.Text = 10000
        ElseIf Me.cboffline.Text = "NARUTO" Then
            Me.txtharga.Text = 12000
        End If

    End Sub

    Private Sub btsimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btsimpan.Click
        Connect()
        Dim insertquery As String
        Dim Hasil As Integer
        Dim cmd As OleDbCommand
        insertquery = ("insert into pesan (Kode,Nama,Waktu,Nama_Game,Harga,Lama) Values('" & cmbkode.Text & "','" & txtnama.Text & "','" & cblama.Text & "','" & cboffline.Text & "','" & txtharga.Text & "','" & txtlama.Text & "')")
        Try
            cmd = New OleDbCommand(insertquery, Conn)
            Hasil = cmd.ExecuteNonQuery
            If Hasil > 0 Then
                MessageBox.Show("Berhasil Menyimpan", "Sukses", MessageBoxButtons.OK,
    MessageBoxIcon.Information)
                cmbkode.Text = ""
                txtnama.Text = ""
                cblama.Text = ""
                cboffline.Text = ""
                txtharga.Text = ""
                txtlama.Text = ""
                Tampil()
                LoadData()
            End If
        Catch ex As OleDbException
            MessageBox.Show("Failed : " & ex.Message, "Gagal Menyimpan", MessageBoxButtons.OK,
    MessageBoxIcon.Error)
        End Try

        'Refresh data dalam tabel
        Refresh()

        'Tutup Koneksi
        Closedd()
    End Sub

    Private Sub bthapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bthapus.Click
        Connect()
        Dim index As Integer = cmbkode.SelectedIndex
        Dim hasil As Integer
        Dim pesan As DialogResult
        sql = "delete from pesan where Kode='" & cmbkode.Text & "'"
        pesan = MessageBox.Show("Anda Yakin Ingin Menghapus Data Retail " & Chr(10) & "dengan kode " &
               cmbkode.Text & "...?", "Konfirmasi", MessageBoxButtons.YesNo, MessageBoxIcon.Question,
               MessageBoxDefaultButton.Button2)
        cmd = New OleDbCommand(sql, Conn)
        Try
            If pesan = Windows.Forms.DialogResult.Yes = True Then
                hasil = cmd.ExecuteNonQuery
                cmbkode.Text = ""
                txtnama.Text = ""
                cblama.Text = ""
                txtharga.Text = ""
                cboffline.Text = ""
                txttotal.Text = ""
                txtlama.Text = ""
                Tampil()
            End If
        Catch ex As OleDbException
            MsgBox("Failed : " & ex.Message)
        End Try
        Closedd()
    End Sub

    Private Sub btedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btedit.Click
        Connect()
        Dim hasil As Integer
        sql = "Update pesan set Nama='" & txtnama.Text & "',Waktu='" & cblama.Text & "', Nama_Game ='" & cboffline.Text & "', Harga ='" & txtharga.Text & "', where Kode='" & cmbkode.Text & "'"
        cmd = New OleDbCommand(sql, Conn)
        Try
            hasil = cmd.ExecuteNonQuery
            If (hasil > 0) Then
                MessageBox.Show("Data Berhasil diubah", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbkode.Text = ""
                txtnama.Text = ""
                cblama.Text = ""
                cboffline.Text = ""
                txtharga.Text = ""
                txtlama.Text = ""
                txtkembali.Text = ""
                Tampil()
                LoadData()
            End If
        Catch ex As OleDbException
            MessageBox.Show("Failed : " & ex.Message)
        End Try
        Closedd()
    End Sub

    Private Sub cmbkode_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbkode.SelectedIndexChanged

        Connect()

        Dim Kunci As String = cmbkode.Text

        sql = "select * from pesan where Kode='" & Kunci & "'"
        cmd = New OleDbCommand(sql, Conn)
        reader = cmd.ExecuteReader
        Try
            reader.Read()

            txtnama.Text = reader.GetString(1)
            cblama.Text = reader.GetString(2)
            cboffline.Text = reader.GetString(5)
            txtharga.Text = reader.GetString(6)
            txtjumlah.Text = reader.GetString(7)
            txtkembali.Text = reader.GetString(8)

        Catch ex As Exception

            MessageBox.Show("Data Berhasil Di Temukan")

        End Try

        Closedd()

    End Sub

End Class



Untuk download contoh project nya bisa ke sini


Tag : TUTOR VB.NET
0 Komentar untuk "VB.NET Database Ms. Access"

Back To Top