Penggunaan Store Procedure dan Trigger pada SQL Server (UAS Lab. SMBD)

Baiklah, untuk kesempatan kali ini saya akan membagi sebuah ide sekaligus menyajikan program simpel untuk bisnis penjualan spesialisasi barang antik khususnya miniatur. Jadi disini saya akan bagikan cara pembuatan program yang bisa mendukung bisnis ini. Kebetulan saya disini hanya menggunakan tiga aplikasi yaitu Microsoft Access 2012, Microsoft SQL Server 2012, dan Microsoft Visual Studio 2012. Selamat Mencoba!

Database dan Tabel-Tabel Normalisasi

1. Tabel Pelanggan

2. Tabel Pesanan









3. Tabel Barang
















Store Procedure dan Trigger

Penggunaan dua istilah ini dimaksudkan untuk pengupdatean data dari bisnis ini seperti stok barang yang berkurang maupun bertambah :

ALTER TRIGGER [dbo].[kurangstokbarang] on [dbo].[TabelPesanan]
for insert
as
update brg set brg.Stok = brg.Stok-jual.Jumlah
from TabelBarang brg join inserted jual on brg.KodeBarang=jual.KodeBarang


ALTER PROCEDURE [dbo].[Prosedur]

 @KodeBarang Number,@NamaBarang Text,@HargaBarang Number,@KodeKategori Text,@Stok Number
AS
BEGIN
SET NOCOUNT ON
INSERT INTO TabelBarang 
(
KodeBarang,NamaBarang,HargaBarang,KodeKategori,Stok
)
VALUES
(
@KodeBarang,@NamaBarang,@HargaBarang,@KodeKategori,@Stok
)
END

Design dan Source Code












Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page
    Public constring As String = ConfigurationManager.ConnectionStrings("DBShopConnectionString").ConnectionString
    Public oconn As New SqlConnection(constring)
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ocmd As New SqlCommand
        oconn.Open()
        ocmd.Connection = oconn
        ocmd.CommandText = "Prosedur"
        ocmd.CommandType = CommandType.StoredProcedure
        ocmd.Parameters.Add("@KodeBarang", SqlDbType.Number).Value = TextBox1.Text
        ocmd.Parameters.Add("@NamaBarang", SqlDbType.Text).Value = TextBox2.Text
        ocmd.Parameters.Add("@HargaBarang", SqlDbType.Number).Value = TextBox3.Text
        ocmd.Parameters.Add("@KodeKategori", SqlDbType.Text).Value = TextBox4.Text
        ocmd.Parameters.Add("@Stok", SqlDbType.Number).Value = TextBox5.Text
        ocmd.ExecuteNonQuery()
        MsgBox("Berhasil")
    End Sub
End Class

Terimakasih!

Alfath Dilon

1406629490

Database Normalisasi (UTS Lab. SMBD)

Tahapan Normalisasi

1NF

Table_SewaMusik (ID_Penyewa, Nama_Penyewa, ID_AlatMusik, Nama_AlatMusik, Tipe_Harga, Sewa_Perhari)

2NF

Table_SewaMusik (ID_Penyewa, Nama_Penyewa, ID_AlatMusik, Tipe_Harga)

Table_Merk (ID_AlatMusik, Nama_AlatMusik)

Table_TipeHarga (Tipe_Harga, Sewa_Perhari)

*note: 3NF sama dengan 2NF

Berikut Screenshot:

Table_SewaMusik

Table_Merk

Table_TipeHarga

Table_Penyewaan

Relationships

Source Code

Imports System.Data

Imports System.Data.OleDb

Public Class WebForm1

    Inherits System.Web.UI.Page

    Public constring As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

    Public oConn As New OleDbConnection(constring)

    Public oTbl As New DataTable

    Public xReader As OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

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

        Dim sql As String = "INSERT INTO Table_SewaMusik VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"

        Dim oCmd As New OleDbCommand

        oConn.Open()

        oCmd.Connection = oConn

        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

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

        Dim hapus = MsgBox("Anda Yakin", MsgBoxStyle.YesNo, "Hapus")

        If hapus = vbYes Then

            oConn.Close()

            oConn.Open()

            Dim delet As String = "DELETE FROM Table_SewaMobil where ID_Penyewa =" + TextBox1.Text + ""

            Dim oCmd As New OleDbCommand

            oConn.Close()

            oConn.Open()

            oCmd.Connection = oConn

            oCmd.CommandText = delet

            oCmd.ExecuteNonQuery()

            MsgBox("Sudah terhapus", vbArchive)

            TextBox1.Text = ""

            TextBox2.Text = ""

            TextBox3.Text = ""

            TextBox4.Text = ""

        End If

    End Sub

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

        Dim sql As String = " UPDATE Table_SewaMusik set Nama_Penyewa  ='" & TextBox2.Text & "', ID_AlatMusik = " & TextBox3.Text & ", Tipe_Harga = '" & TextBox4.Text & "' where ID_Penyewa = " & TextBox1.Text & ""

        Dim oCmd As New OleDbCommand

        oConn.Close()

        oConn.Open()

        oCmd.Connection = oConn

        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

End Class

UAS Lab. Pemograman Bisnis 2

APLIKASI KLINIK

Hal inilah yang saya coba buat di paparan blog saya kali ini. Aplikasi ini berfungsi untuk menyimpan semua data yang ada pada sebuah klinik, seperti data-data para dokter, pasien, obat, maupun petugas klinik.
Baiklah, untuk kali ini saya menamai klinik ini dengan nama saya sendiri, Klinik Dilon.

Aplikasi tampilan HOME, berikut juga source codenya:

Public Class Form1

Dim Koneksi AsString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\ \Universitas Indonesia\Term3\Business Programming 2\UAS\Klinik Dilon.accdb"

Dim objekKoneksi AsNew OleDb.OleDbConnection(Koneksi)

    Private Sub DATAPASIENToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DATAPASIENToolStripMenuItem.Click

        Dim Form_pasien As New Form_pasien

        Form_pasien.ShowDialog()

        Form_pasien.Dispose()

    End Sub

    Private Sub LOGOUTToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LOGOUTToolStripMenuItem.Click

    End Sub

    Private Sub KeluarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles KeluarToolStripMenuItem.Click

        Me.Close()

    End Sub

    Private Sub DATAOBATToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DATAOBATToolStripMenuItem.Click

        Dim Form_obat As New Form_obat

        Form_obat.ShowDialog()

        Form_obat.Dispose()

    End Sub

    Private Sub DATAPETUGASToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DATAPETUGASToolStripMenuItem.Click

        Dim Form_petugas As New Form_petugas

        Form_petugas.ShowDialog()

        Form_petugas.Dispose()

    End Sub

    Private Sub DATADOKTERToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DATADOKTERToolStripMenuItem.Click

        Dim Form_dokter As New Form_dokter

        Form_dokter.ShowDialog()

        Form_dokter.Dispose()

    End Sub

End Class

Aplikasi tampilan Form Dokter, berikut juga source code nya:

Public Class Form_dokter

    Dim MyConnection As New AccessData.Databaseconection

    Dim mPosition As Integer

    Dim objConnection As OleDbConnection

    Dim objCommand As OleDbCommand

    Dim objDataAdapter As OleDbDataAdapter

    Dim objdatareader As OleDbDataReader

    Dim strConn, strSQL As String

    Dim objDataSet As New DataSet

    Dim objDataTable As New DataTable

    Dim mProcess As String

    Protected MyConnectiondokter As New AccessData.Accesdokter

#End Region

    Public Sub Displaydata()

        Try

            objDataTable.Clear()

            strSQL = "Select * from DATA_DOKTER Order by KD_Dokter asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_DOKTER")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_DOKTER")

        Catch ex As Exception

            MsgBox("Program error" _

               & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub clear()

        Textdokter.Text = ""

        Textnamadokter.Text = ""

        Combobidang.Text = ""

        Combostatus.Text = ""

        Textalmt.Text = ""

    End Sub

    Private Sub NonAktif_Inputan()

        Textdokter.Enabled = False

        Textnamadokter.Enabled = False

        Combobidang.Enabled = False

        Combostatus.Enabled = False

        Textalmt.Enabled = False

    End Sub

    Private Sub Aktif_Inputan()

        Textdokter.Enabled = True

        Textnamadokter.Enabled = True

        Combobidang.Enabled = True

        Combostatus.Enabled = True

        Textalmt.Enabled = True

    End Sub

    Sub Displaydata2()

        Try

            strSQL = "Select * from DATA_DOKTER Order by KD_Dokter asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_DOKTER")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_DOKTER")

            mPosition = BindingContext(objDataTable).Position

            With objDataTable.Rows(mPosition)

                Textdokter.Text = .Item("KD_Dokter")

                Textnamadokter.Text = .Item("Nama_Dokter").ToString

                Combobidang.Text = .Item("Bidang_Keahlian").ToString

                Combostatus.Text = .Item("Status").ToString

                Textalmt.Text = .Item("Alamat").ToString

            End With

        Catch When Err.Number <> 0

            MsgBox("Tidak dapat membuat hubungan ke database" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub ViewData()

        objCommand = MyConnection.open.CreateCommand

        objCommand.CommandText = "select * from DATA_DOKTER Where No_Rekam_Medis = '" & Trim(Textdokter.Text) & "';"

        objdatareader = objCommand.ExecuteReader

        objdatareader.Read()

        Textdokter.Text = objdatareader.Item("KD_Dokter")

        Textnamadokter.Text = objdatareader.Item("Nama_Dokter").ToString

        Combobidang.Text = objdatareader.Item("Bidang_Keahlian").ToString

        Combostatus.Text = objdatareader.Item("Status").ToString

        Textalmt.Text = objdatareader.Item("Alamat").ToString

        MyConnection.close()

    End Sub

    Sub CekData()

        objDataTable.Clear()

        strSQL = "Select * from DATA_DOKTER Where KD_Dokter = '" & Trim(Textdokter.Text) + "'"

        objCommand = New OleDbCommand

        objCommand.Connection = MyConnection.open

        objCommand.CommandType = CommandType.Text

        objCommand.CommandText = strSQL

        objDataAdapter = New OleDbDataAdapter(objCommand)

        objDataAdapter.Fill(objDataSet, "MDT_DATA_DOKTER")

        MyConnection.close()

        objDataTable = objDataSet.Tables("MDT_DATA_DOKTER")

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Me.Close()

    End Sub

    Private Sub Form_pasien_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try

            Displaydata()

            NonAktif_Inputan()

            clear()

        Catch When Err.Number <> 0

            MsgBox("Program error" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub btnmaju_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmaju.Click

        BindingContext(objDataTable).Position += 1

        Displaydata2()

    End Sub

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

        Try

            mProcess = "ADD"

            btnsimpan.Text = "Save Add"

            Aktif_Inputan()

            btnsimpan.Enabled = True

            clear()

            Textdokter.Focus()

        Catch When Err.Number <> 0

            MsgBox("Program error " _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub Buttonsimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsimpan.Click

        If btnsimpan.Text = "Save Add" Then

            Try

                Dim mYes_No As String = MsgBox("Anda yakin data mau disimpan ?", vbYesNo)

                If mYes_No = vbYes Then

                    MyConnectiondokter.AccesdokterInsert(UCase(Trim(Textdokter.Text)), _

                    StrConv(Trim(Textnamadokter.Text), VbStrConv.ProperCase), _

                    Combobidang.SelectedItem, _

                    Combostatus.SelectedItem, Trim(Textalmt.Text))

                    NonAktif_Inputan()

                    btnsimpan.Enabled = False

                    btnsimpan.Text = "Save"

                    btnAdd.Focus()

                    Displaydata()

                    clear()

                Else

                    NonAktif_Inputan()

                    clear()

                    btnAdd.Focus()

                End If

            Catch When Err.Number <> 0

                MsgBox("Tidak dapat melakukan proses simpan" _

                & vbCrLf & Err.Description)

                MyConnection.close()

            End Try

        ElseIf btnsimpan.Text = "Save Edit" Then

            Try

                Dim mYes_No As String = MsgBox("Anda yakin data mau diupdate ?", vbYesNo)

                If mYes_No = vbYes Then

                    MyConnectiondokter.AccesdokterUpdate(UCase(Trim(Textdokter.Text)), _

                    StrConv(Trim(Textnamadokter.Text), VbStrConv.ProperCase), _

                    Combobidang.SelectedItem, _

                    Combostatus.SelectedItem, Trim(Textalmt.Text))

                    NonAktif_Inputan()

                    btnsimpan.Text = "Save"

                    btnsimpan.Enabled = False

                    btnAdd.Focus()

                    Displaydata()

                    clear()

                Else

                    NonAktif_Inputan()

                    clear()

                    btnAdd.Focus()

                End If

            Catch When Err.Number <> 0

                MsgBox("Tidak dapat melakukan proses update" _

                & vbCrLf & Err.Description)

                MyConnection.close()

            End Try

        End If

    End Sub

End Class

Aplikasi tampilan Form Pasien, berikut juga source code nya:

Public Class Form_pasien

    Public Sub Displaydata()

        Try

            objDataTable.Clear()

            strSQL = "Select * from DATA_PASIEN Order by No_Rekam_Medis asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_PASIEN")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_PASIEN")

        Catch ex As Exception

            MsgBox("Program error" _

               & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub clear()

        txtrekam.Text = ""

        txtnama.Text = ""

        txtttl.Text = ""

        cbostatus.Text = ""

        txtalamat.Text = ""

        txttlp.Text = ""

        cbokatogori.Text = ""

        txtpenanggung.Text = ""

    End Sub

    Private Sub NonAktif_Inputan()

        txtrekam.Enabled = False

        txtnama.Enabled = False

        txtttl.Enabled = False

        cbostatus.Enabled = False

        txtalamat.Enabled = False

        txttlp.Enabled = False

        cbokatogori.Enabled = False

        txtpenanggung.Enabled = False

    End Sub

    Private Sub Aktif_Inputan()

        txtrekam.Enabled = True

        txtnama.Enabled = True

        txtttl.Enabled = True

        cbostatus.Enabled = True

        txtalamat.Enabled = True

        txttlp.Enabled = True

        cbokatogori.Enabled = True

        txtpenanggung.Enabled = True

    End Sub

    Sub Displaydata2()

        Try

            strSQL = "Select * from DATA_PASIEN Order by No_Rekam_Medis asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_PASIEN")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_PASIEN")

            mPosition = BindingContext(objDataTable).Position

            With objDataTable.Rows(mPosition)

                txtrekam.Text = .Item("No_Rekam_Medis")

                txtnama.Text = .Item("Nama_Pasien").ToString

                txtttl.Text = .Item("Tanggal_Lahir").ToString

                cbostatus.Text = .Item("Status").ToString

                txtalamat.Text = .Item("Alamat").ToString

                txttlp.Text = .Item("No Telp / Hp").ToString

                cbokatogori.Text = .Item("Katagori_Pasien").ToString

                txtpenanggung.Text = .Item("Nama_Penanggung").ToString

            End With

        Catch When Err.Number <> 0

            MsgBox("Tidak dapat membuat hubungan ke database" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub ViewData()

        objCommand = MyConnection.open.CreateCommand

        objCommand.CommandText = "select * from DATA_PASIEN Where No_Rekam_Medis = '" & Trim(txtrekam.Text) & "';"

        objdatareader = objCommand.ExecuteReader

        objdatareader.Read()

        txtrekam.Text = objdatareader.Item("No_Rekam_Medis")

        txtnama.Text = objdatareader.Item("Nama_Pasien").ToString

        txtttl.Text = objdatareader.Item("Tanggal_Lahir").ToString

        cbostatus.Text = objdatareader.Item("Status").ToString

        txtalamat.Text = objdatareader.Item("Alamat").ToString

        txttlp.Text = objdatareader.Item("No Telp / Hp").ToString

        cbokatogori.Text = objdatareader.Item("Katagori_Pasien").ToString

        txtpenanggung.Text = objdatareader.Item("Nama_Penanggung").ToString

        MyConnection.close()

    End Sub

    Sub CekData()

        objDataTable.Clear()

        strSQL = "Select * from DATA_PASIEN Where No_Rekam_Medis = '" & Trim(txtrekam.Text) + "'"

        objCommand = New OleDbCommand

        objCommand.Connection = MyConnection.open

        objCommand.CommandType = CommandType.Text

        objCommand.CommandText = strSQL

        objDataAdapter = New OleDbDataAdapter(objCommand)

        objDataAdapter.Fill(objDataSet, "MDT_DATA_PASIEN")

        MyConnection.close()

        objDataTable = objDataSet.Tables("MDT_DATA_PASIEN")

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Me.Close()

    End Sub

    Private Sub Form_pasien_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try

            Displaydata()

            NonAktif_Inputan()

            clear()

        Catch When Err.Number <> 0

            MsgBox("Program error" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub btnmaju_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmaju.Click

        BindingContext(objDataTable).Position += 1

        Displaydata2()

    End Sub

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

        Try

            mProcess = "ADD"

            btnsimpan.Text = "Save Add"

            Aktif_Inputan()

            btnsimpan.Enabled = True

            clear()

            txtrekam.Focus()

        Catch When Err.Number <> 0

            MsgBox("Program error " _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub btnsimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsimpan.Click

        If btnsimpan.Text = "Save Add" Then

            Try

                Dim mYes_No As String = MsgBox("Anda yakin data mau disimpan ?", vbYesNo)

                If mYes_No = vbYes Then

                    MyConnectionpasien.AccesspasienInsert(UCase(Trim(txtrekam.Text)), _

                    StrConv(Trim(txtnama.Text), VbStrConv.ProperCase), _

                    StrConv(Trim(txtttl.Text), VbStrConv.ProperCase), cbostatus.SelectedItem, Trim(txtalamat.Text), _

                    StrConv(Trim(txttlp.Text), VbStrConv.ProperCase), cbokatogori.SelectedItem, Trim(txtpenanggung.Text))

                    NonAktif_Inputan()

                    btnsimpan.Enabled = False

                    btnsimpan.Text = "Save"

                    btnAdd.Focus()

                    Displaydata()

                    clear()

                Else

                    NonAktif_Inputan()

                    clear()

                    btnAdd.Focus()

                End If

            Catch When Err.Number <> 0

                MsgBox("Tidak dapat melakukan proses simpan" _

                & vbCrLf & Err.Description)

                MyConnection.close()

            End Try

        ElseIf btnsimpan.Text = "Save Edit" Then

            Try

                Dim mYes_No As String = MsgBox("Anda yakin data mau diupdate ?", vbYesNo)

                If mYes_No = vbYes Then

                    MyConnectionpasien.AccesspasienUpdate(UCase(Trim(txtrekam.Text)), _

                    StrConv(Trim(txtnama.Text), VbStrConv.ProperCase), _

                    StrConv(Trim(txtttl.Text), VbStrConv.ProperCase), cbostatus.SelectedItem, Trim(txtalamat.Text), _

                    StrConv(Trim(txttlp.Text), VbStrConv.ProperCase), cbokatogori.SelectedItem, Trim(txtpenanggung.Text))

                    NonAktif_Inputan()

                    btnsimpan.Text = "Save"

                    btnsimpan.Enabled = False

                    btnAdd.Focus()

                    Displaydata()

                    clear()

                Else

                    NonAktif_Inputan()

                    clear()

                    btnAdd.Focus()

                End If

            Catch When Err.Number <> 0

                MsgBox("Tidak dapat melakukan proses update" _

                & vbCrLf & Err.Description)

                MyConnection.close()

            End Try

        End If

    End Sub

End Class

Aplikasi tampilan Form Obat, berikut juga source code nya:

Public Class Form_obat

    Public Sub Displaydata()

        Try

            objDataTable.Clear()

            strSQL = "Select * from DATA_OBAT Order by KD_Obat asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_OBAT")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_OBAT")

        Catch ex As Exception

            MsgBox("Program error" _

               & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub clear()

        Textobat.Text = ""

        Textnama.Text = ""

        Combojenis.Text = ""

        Combokatagori.Text = ""

        Textekpired.Text = ""

        Textharga.Text = ""

    End Sub

    Private Sub NonAktif_Inputan()

        Textobat.Enabled = False

        Textnama.Enabled = False

        Combojenis.Enabled = False

        Combokatagori.Enabled = False

        Textekpired.Enabled = False

        Textharga.Enabled = False

    End Sub

    Private Sub Aktif_Inputan()

        Textobat.Enabled = True

        Textnama.Enabled = True

        Combojenis.Enabled = True

        Combokatagori.Enabled = True

        Textekpired.Enabled = True

        Textharga.Enabled = True

    End Sub

    Sub Displaydata2()

        Try

            strSQL = "Select * from DATA_OBAT Order by KD_Obat asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_OBAT")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_OBAT")

            mPosition = BindingContext(objDataTable).Position

            With objDataTable.Rows(mPosition)

                Textobat.Text = .Item("KD_Obat")

                Textnama.Text = .Item("Nama_Obat").ToString

                Combojenis.Text = .Item("Jenis_Obat").ToString

                Combokatagori.Text = .Item("Katagori_Obat").ToString

                Textekpired.Text = .Item("Expired").ToString

                Textharga.Text = .Item("Harga").ToString

            End With

        Catch When Err.Number <> 0

            MsgBox("Tidak dapat membuat hubungan ke database" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub ViewData()

        objCommand = MyConnection.open.CreateCommand

        objCommand.CommandText = "select * from DATA_OBAT Where KD_Obat = '" & Trim(Textobat.Text) & "';"

        objdatareader = objCommand.ExecuteReader

        objdatareader.Read()

        Textobat.Text = objdatareader.Item("KD_Obat")

        Textnama.Text = objdatareader.Item("Nama_Obat").ToString

        Combojenis.Text = objdatareader.Item("Jenis_Obat").ToString

        Combokatagori.Text = objdatareader.Item("Katagori_Obat").ToString

        Textekpired.Text = objdatareader.Item("Expired").ToString

        Textharga.Text = objdatareader.Item("Harga").ToString

        MyConnection.close()

    End Sub

    Sub CekData()

        objDataTable.Clear()

        strSQL = "Select * from DATA_OBAT Where KD_Obat = '" & Trim(Textobat.Text) + "'"

        objCommand = New OleDbCommand

        objCommand.Connection = MyConnection.open

        objCommand.CommandType = CommandType.Text

        objCommand.CommandText = strSQL

        objDataAdapter = New OleDbDataAdapter(objCommand)

        objDataAdapter.Fill(objDataSet, "MDT_DATA_OBAT")

        MyConnection.close()

        objDataTable = objDataSet.Tables("MDT_DATA_OBAT")

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Me.Close()

    End Sub

    Private Sub Form_obat_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try

            Displaydata()

            NonAktif_Inputan()

            clear()

        Catch When Err.Number <> 0

            MsgBox("Program error" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

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

        Try

            mProcess = "ADD"

            btnsimpan.Text = "Save Add"

            Aktif_Inputan()

            btnsimpan.Enabled = True

            clear()

            Textobat.Focus()

        Catch When Err.Number <> 0

            MsgBox("Program error " _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub btnmaju_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmaju.Click

        BindingContext(objDataTable).Position += 1

        Displaydata2()

    End Sub

    Private Sub btnmundur_Click(sender As Object, e As EventArgs) Handles btnmundur.Click

    End Sub

End Class

Aplikasi tampilan Form Petugas, berikut juga source code nya:

Public Class Form_petugas

    Public Sub Displaydata()

        Try

            objDataTable.Clear()

            strSQL = "Select * from DATA_PETUGAS Order by KD_Pegawai asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_PETUGAS")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_PETUGAS")

        Catch ex As Exception

            MsgBox("Program error" _

               & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub clear()

        Textptugs.Text = ""

        Textnama.Text = ""

        Combostatus.Text = ""

        Textalmt.Text = ""

    End Sub

    Private Sub NonAktif_Inputan()

        Textptugs.Enabled = False

        Textnama.Enabled = False

        Combostatus.Enabled = False

        Textalmt.Enabled = False

    End Sub

    Private Sub Aktif_Inputan()

        Textptugs.Enabled = True

        Textnama.Enabled = True

        Combostatus.Enabled = True

        Textalmt.Enabled = True

    End Sub

    Sub Displaydata2()

        Try

            strSQL = "Select * from DATA_PETUGAS Order by KD_Pegawai asc"

            objCommand = New OleDbCommand

            objCommand.Connection = MyConnection.open

            objCommand.CommandType = CommandType.Text

            objCommand.CommandText = strSQL

            objDataAdapter = New OleDbDataAdapter(objCommand)

            objDataAdapter.Fill(objDataSet, "MD_DATA_PETUGAS")

            MyConnection.close()

            objDataTable = objDataSet.Tables("MD_DATA_PETUGAS")

            mPosition = BindingContext(objDataTable).Position

            With objDataTable.Rows(mPosition)

                Textptugs.Text = .Item("KD_Pegawai")

                Textnama.Text = .Item("Nama_Pegawai").ToString

                Combostatus.Text = .Item("Status").ToString

                Textalmt.Text = .Item("Alamat").ToString

            End With

        Catch When Err.Number <> 0

            MsgBox("Tidak dapat membuat hubungan ke database" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub ViewData()

        objCommand = MyConnection.open.CreateCommand

        objCommand.CommandText = "select * from DATA_PETUGAS Where No_Rekam_Medis = '" & Trim(Textptugs.Text) & "';"

        objdatareader = objCommand.ExecuteReader

        objdatareader.Read()

        Textptugs.Text = objdatareader.Item("KD_Pegawai")

        Textnama.Text = objdatareader.Item("Nama_Pegawai").ToString

        Combostatus.Text = objdatareader.Item("Status").ToString

        Textalmt.Text = objdatareader.Item("Alamat").ToString

        MyConnection.close()

    End Sub

    Sub CekData()

        objDataTable.Clear()

        strSQL = "Select * from DATA_PETUGAS Where KD_Pegawai = '" & Trim(Textptugs.Text) + "'"

        objCommand = New OleDbCommand

        objCommand.Connection = MyConnection.open

        objCommand.CommandType = CommandType.Text

        objCommand.CommandText = strSQL

        objDataAdapter = New OleDbDataAdapter(objCommand)

        objDataAdapter.Fill(objDataSet, "MDT_DATA_PETUGAS")

        MyConnection.close()

        objDataTable = objDataSet.Tables("MDT_DATA_PETUGAS")

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Me.Close()

    End Sub

    Private Sub Form_petugas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try

            Displaydata()

            NonAktif_Inputan()

            clear()

        Catch When Err.Number <> 0

            MsgBox("Program error" _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub btnmaju_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmaju.Click

        BindingContext(objDataTable).Position += 1

        Displaydata2()

    End Sub

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

        Try

            mProcess = "ADD"

            btnsimpan.Text = "Save Add"

            Aktif_Inputan()

            btnsimpan.Enabled = True

            clear()

            Textptugs.Focus()

        Catch When Err.Number <> 0

            MsgBox("Program error " _

            & vbCrLf & Err.Description)

            MyConnection.close()

        End Try

    End Sub

    Private Sub btnsimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsimpan.Click

        If btnsimpan.Text = "Save Add" Then

            Try

                Dim mYes_No As String = MsgBox("Anda yakin data mau disimpan ?", vbYesNo)

                If mYes_No = vbYes Then

                    MyConnectiondokter.AccespetugasInsert(UCase(Trim(Textptugs.Text)), _

                    StrConv(Trim(Textnama.Text), VbStrConv.ProperCase), _

                    Combostatus.SelectedItem, Trim(Textalmt.Text))

                    NonAktif_Inputan()

                    btnsimpan.Enabled = False

                    btnsimpan.Text = "Save"

                    btnAdd.Focus()

                    Displaydata()

                    clear()

                Else

                    NonAktif_Inputan()

                    clear()

                    btnAdd.Focus()

                End If

            Catch When Err.Number <> 0

                MsgBox("Tidak dapat melakukan proses simpan" _

                & vbCrLf & Err.Description)

                MyConnection.close()

            End Try

        ElseIf btnsimpan.Text = "Save Edit" Then

            Try

                Dim mYes_No As String = MsgBox("Anda yakin data mau diupdate ?", vbYesNo)

                If mYes_No = vbYes Then

                    MyConnectiondokter.AccespetugasUpdate(UCase(Trim(Textptugs.Text)), _

                    StrConv(Trim(Textnama.Text), VbStrConv.ProperCase), _

                    Combostatus.SelectedItem, Trim(Textalmt.Text))

                    NonAktif_Inputan()

                    btnsimpan.Text = "Save"

                    btnsimpan.Enabled = False

                    btnAdd.Focus()

                    Displaydata()

                    clear()

                Else

                    NonAktif_Inputan()

                    clear()

                    btnAdd.Focus()

                End If

            Catch When Err.Number <> 0

                MsgBox("Tidak dapat melakukan proses update" _

                & vbCrLf & Err.Description)

                MyConnection.close()

            End Try

        End If

    End Sub

   

End Class

Baiklah, itulah contoh aplikasi yang saya buat. Jika masih banyak kekurangan, mohon maaf karena saya juga masih belajar. Terimakasih! :)