Penjelasan

Database ini digunakan untuk mengumpulkan data penanggung jawab jadwal touring konser, berikut contohnya

1. Membuat TRIGGER
CREATE TRIGGER TambahJadwal on JadwalKonser
for insert
as
update data set data.TanggalKonser = data.TanggalKonser + mdl.TanggalKonser
from JadwalKonser mdl join inserted data on data.KodePenanggungJawab = mdl.KodePenanggungJawab

2. Membuat STORED PROCEDURE
CREATE PROCEDURE TambahPenanggungJawab
@KodePenanggungJawab Number(50),@Nama Short Text(50),@Alamat Short Text(18,0),@Jabatan Short Text (18,0)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DataPenanggungJawab
(
KodePenanggungJawab, Nama, Alamat, Jabatan
)
VALUES
(
@KodePenanggungJawab, @Nama, @Alamat, @Jabatan
)
END

                                                UTS Lab. SMBD Normalisasi Database

1 NF
Faktur(No Faktur, Kode Supplier, Nama Supplier, Kode Barang, Nama Barang, Quantitas, Harga, Jumlah,).

2 NF
TableSupplier(Kode Supplier, Nama Supplier)
TableBarang(Kode Barang, Nama barang, harga)
TableFaktur(No Faktur, Kode Supplier, Kode barang, Quantitas)

SOURCE CODE Web Form Asp.net

Imports System.Data
Imports System.Data.OleDb

Public Class TransaksiBarang
    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 Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim sql As String = "INSERT INTO TableBarang VALUES ('" & KodeBarang.Text & "','" & NamaBarang.Text & "','" & Harga.Text & "')"
        Dim oCmd As New OleDbCommand
        oConn.Open()
        oCmd.Connection = oConn
        oCmd.CommandText = sql
        oCmd.ExecuteNonQuery()
    End Sub

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

        Dim sql As String = " UPDATE TableBarang set Nama_Barang  ='" & NamaBarang.Text & "', Harga = '" & Harga.Text & "' where Kode_Barang = '" & KodeBarang.Text & "'"

        Dim oCmd As New OleDbCommand
        oConn.Close()
        oConn.Open()
        oCmd.Connection = oConn
        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

    Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim hapus = MsgBox("Konfirmasi", MsgBoxStyle.YesNo, "Hapus")

        If hapus = vbYes Then
            oConn.Close()
            oConn.Open()
            Dim delet As String = "DELETE FROM TableBarang where Kode_Barang ='" + KodeBarang.Text + "'"
            Dim oCmd As New OleDbCommand
            oConn.Close()
            oConn.Open()
            oCmd.Connection = oConn
            oCmd.CommandText = delet
            oCmd.ExecuteNonQuery()
            MsgBox("Sudah terhapus", vbArchive)

            KodeBarang.Text = ""
            NamaBarang.Text = ""
            Harga.Text = ""


        End If
    End Sub

    Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

        oConn.Close()
        oConn.Open()
        Dim Cmd As New OleDbCommand("SELECT * FROM TableBarang where Kode_Barang ='" + KodeBarang.Text + "'", oConn)
        xReader = Cmd.ExecuteReader
        If xReader.HasRows Then
            xReader.Read()
            KodeBarang.Text = xReader("Kode_Barang")
            NamaBarang.Text = xReader("Nama_Barang")
            Harga.Text = xReader("Harga")


        Else
            MsgBox("Kode Barang Tidak Ada")
            KodeBarang.Text = ""
            NamaBarang.Text = ""
            Harga.Text = ""
            Exit Sub
        End If
        xReader.Close()
    End Sub
End Class

Dalam kesempatan ini, saya akan membuat aplikasi database penjualan tiket cinema. Aplikasi ini dapat menginput, merubah, dan menghapus data yang berada didalamnya, sehingga akan memudahkan penjual untuk keep in track dengan penjualannya.

Berikut adalah bentuk formnya;

1. 

Form Log In

2. 

Form Penjualan dan Penyimpanan Database

3. 

Form Database Penjualan Tiket

Source Code

Public Class Form1

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

        If TextBox1.Text = "Lukman" Then

            TextBox2.Text = "lukman"

            Form2.Show()

            Me.Hide()

        End If

    End Sub

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

        Me.Hide()

    End Sub

End Class

Imports System.Data

Imports System.Data.Sql

Imports System.Data.OleDb

Public Class Form2

    Dim connection As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Lukman Hakim\Documents\Lukman.accdb"

    Dim objekkoneksi As New OleDb.OleDbConnection(connection)

    Dim xreader As OleDbDataReader

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

        objekkoneksi.Close()

        objekkoneksi.Open()

        Dim add As String = "INSERT INTO TablePenjualan values('" & TextBox1.Text & "','" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & ComboBox3.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "', '" & TextBox4.Text & "')"

        Dim ocmd = New OleDbCommand(add, objekkoneksi)

        ocmd.ExecuteNonQuery()

        objekkoneksi.Close()

        MsgBox("Berhasil")

        Form3.Show()

    End Sub

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

        Dim DELETE As Integer

        DELETE = MsgBox("Confirmation", MsgBoxStyle.OkCancel)

        If vbOK Then

            objekkoneksi.Close()

            objekkoneksi.Open()

            Dim sql As String = "DELETE from TablePenjualan where NamaPembeli = '" + TextBox1.Text + "'"

            Dim ocmd As New OleDbCommand

            objekkoneksi.Close()

            objekkoneksi.Open()

            ocmd.Connection = objekkoneksi

            ocmd.CommandText = sql

            ocmd.ExecuteNonQuery()

            TextBox1.Text = ""

            ComboBox1.Text = ""

            ComboBox2.Text = ""

            ComboBox3.Text = ""

            TextBox2.Text = ""

            TextBox3.Text = ""

            TextBox4.Text = ""

        End If

    End Sub

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

        Dim Vkode = InputBox("Enter Code")

        objekkoneksi.Close()

        objekkoneksi.Open()

        Dim Cmd As New OleDbCommand("SELECT * FROM TablePenjualan where NamaPembeli ='" + Vkode + "'", objekkoneksi)

        xreader = Cmd.ExecuteReader

        If xreader.HasRows Then

            xreader.Read()

            TextBox1.Text = xreader("NamaPembeli")

            ComboBox1.Text = xreader("NamaFilm")

            ComboBox2.Text = xreader("Ruangan")

            ComboBox3.Text = xreader("JamTayang")

            TextBox2.Text = xreader("NomerKursi")

            TextBox3.Text = xreader("JumlahTiket")

            TextBox4.Text = xreader("Harga")

        Else

            MsgBox("Code not Found")

            TextBox1.Text = ""

            ComboBox1.Text = ""

            ComboBox2.Text = ""

            ComboBox3.Text = ""

            TextBox2.Text = ""

            TextBox3.Text = ""

            TextBox4.Text = ""

            TextBox1.Focus()

            Exit Sub

        End If

        xreader.Close()

    End Sub

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

        Dim sql As String = " Update TablePenjualan set NamaPembeli='" + TextBox1.Text + "', NamaFilm = '" + ComboBox1.Text + "', Ruangan ='" + ComboBox2.Text + "', JamTayang ='" + ComboBox3.Text + "', NomerKursi = '" + TextBox2.Text + "', JumlahTiket ='" + TextBox3.Text + "',  Harga ='" + TextBox4.Text + "'where NamaPembeli = '" + TextBox1.Text + "'"

        Dim oCmd As New OleDbCommand

        objekkoneksi.Close()

        objekkoneksi.Open()

        oCmd.Connection = objekkoneksi

        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

    Private Sub TextBox4_TextChanged(sender As Object, e As EventArgs) Handles TextBox4.TextChanged

        TextBox4.Text = Val(TextBox3.Text) * 35000

    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click

        Form3.Show()

    End Sub

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

        Form4.Show()

    End Sub

End Class

Saya membuat aplikasi e-tiketing untuk memesan tiket konser, tiket konser yang dipesan akan tercatat dalam excel dan tercatat dalam word. No. Tiket akan menjadi bukti pemesanan yang sah apabila terdapat catatan dalam excel dan word.

bentuk layout nya seperti ini. Nama, Type, No.Tiket akan terisi setelah mengisi program





Form pemesanan tiket



sudah melakukan pemesanan




data untuk penjual tiket

SOURCE KODE

untuk melakukan automation

Public Class Form2

    Dim excelApp As New Microsoft.Office.Interop.Excel.Application

    Dim excelBook As Microsoft.Office.Interop.Excel.Workbook

    Dim wordApp As New Microsoft.Office.Interop.Word.Application

    Dim wordDoc As Microsoft.Office.Interop.Word.Document

untuk melakukan undian angka

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

        Dim acak As Integer

        Randomize()

        acak = (10000 * Rnd()) + 1

        NoTiket.Text = acak

    End Sub

untuk menyimpan data dalam excel dan word

  Private Sub Button3_Click_1(sender As Object, e As EventArgs) Handles Simpan.Click

        wordDoc = wordApp.Documents.Open("C:\Users\Lukman Hakim\Downloads\E-TIKET.docx")

        wordDoc.Bookmarks("Type").Select()

        wordApp.Selection.TypeText(ComboBox1.Text)

        wordDoc.Bookmarks("Nama").Select()

        wordApp.Selection.TypeText(Nama.Text)

        wordDoc.Bookmarks("NomerTiket").Select()

        wordApp.Selection.TypeText(NoTiket.Text)

        wordDoc.SaveAs2("C:\Users\Lukman Hakim\Documents\" + Nama.Text + ".docx")

        wordApp.Visible = True

        excelBook = excelApp.Workbooks.Open("C:\Users\Lukman Hakim\Downloads\DATA TIKET.xlsx")

        excelApp.Range("A2").Value = Nama.Text

        excelApp.Range("B2").Value = Alamat.Text

        excelApp.Range("C2").Value = Email.Text

        excelApp.Range("D2").Value = TanggalLahir.Text

        excelApp.Range("E2").Value = NoHP.Text

        excelApp.Range("F2").Value = ComboBox1.Text

        excelApp.Range("G2").Value = Harga.Text

        excelApp.Range("H2").Value = NoTiket.Text

        excelBook.SaveAs("C:\Users\Lukman Hakim\Documents\" + Nama.Text + ".xlsx")

        excelApp.Visible = True

    End Sub

untuk mengubah gambar sesuai dengan pesanan tiket

Private Sub ComboBox1_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

        If ComboBox1.Text = "Platinum" Then

            Harga.Text = "600000"

        End If

        If ComboBox1.Text = "Platinum" Then

            PictureBox1.Load("C:\Users\Lukman Hakim\Desktop\PLATINUM.JPG")

        End If

        If ComboBox1.Text = "Gold" Then

            Harga.Text = "525000"

        End If

        If ComboBox1.Text = "Gold" Then

            PictureBox1.Load("C:\Users\Lukman Hakim\Desktop\GOLD.JPG")

        End If

        If ComboBox1.Text = "Silver" Then

            Harga.Text = "450000"

        End If

        If ComboBox1.Text = "Silver" Then

            PictureBox1.Load("C:\Users\Lukman Hakim\Desktop\SILVER.JPG")

        End If

        If ComboBox1.Text = "Bronze" Then

            Harga.Text = "400000"

        End If

        If ComboBox1.Text = "Bronze" Then

            PictureBox1.Load("C:\Users\Lukman Hakim\Desktop\BRONZE.JPG")

        End If

    End Sub

End Class