Tugas Akhir Praktek Pemrograman SQL "Kerangka Sistem"

Assalamu'alaikum..

Haloooo.. kali ini kita akan membuat kerangka sebuah sistem puskesmas, berikut merupakan langkah kerangkanya :

1. Desain Tabel dan relasinya

2. Desain HIPO (Hirarki Input Process Output)

Praktek 7 - Praktek Pemrograman SQL

Selasa, 23 Mei 2017

Haloo,, kali ini kita akan membuat tabel retur jual:

Tabel Penjualan

1. Isi data, ambil dari tabel penjualan, 2 record unutk diretur
    Membuat tabel:
    create table retur_jual(
    noretur varchar(10) primary key,
    Nonota varchar(10),
    tglretur date,
    jmlretur int,
    ketcacat varchar(50),
    foreign key(Nonota) references penjualan(Nonota))

    insert into retur_jual
    values
    ('R001','JUAL002','2017/03/19',5,'Kemasannya rusak'),
    ('R002','JUAL001','2017/03/21',3,'Kemasannya rusak')

3. Update stok barang
    update Barang set jumlah=150+50 where kdbrg='007'
    update Barang set jumlah=410+40 where kdbrg='008'

6. Tambahkan 5 data retur
    insert into retur_jual
   values
   ('R003','JUAL003','2017/04/18',10,'Kemasannya rusak'),
   ('R004','JUAL004','2017/05/01',3,'Kemasannya rusak'),
   ('R005','JUAL005','2017/05/07',5,'Kemasannya rusak'),
   ('R006','JUAL006','2017/05/19',5,'Kemasannya rusak'),
   ('R007','JUAL007','2017/06/11',7,'Kemasannya rusak')

7. Update data 3 retur
    update retur_jual set jmlretur=10 where noretur='R001'
    update retur_jual set jmlretur=5 where noretur='R006'
    update retur_jual set jmlretur=5 where noretur='R007'

8. Delete record 1 data retur
    delete from retur_jual where noretur='R007'

9. Tambahkan field jenis_paket
    alter table retur_jual add jenis_paket varchar(20)

10. Isi jenis_paket
     update retur_jual set jenis_paket='JNE' where noretur='R001'
     update retur_jual set jenis_paket='JNE' where noretur='R002'
     update retur_jual set jenis_paket='JNE' where noretur='R003'
     update retur_jual set jenis_paket='JNE' where noretur='R004'
     update retur_jual set jenis_paket='JNE' where noretur='R005'
     update retur_jual set jenis_paket='JNE' where noretur='R006'

Aplikasi Toko Menggunakan vb.net dan SQL Server

Tugas Kelompok

hay guys, kali ini kita akan belajar membuat sebuah aplikasi menggunakan vb.net sebagai bahasa pemrogramannya dan SQL server sebagai databasenya.

Cara mengkoneksikan vb.net dengan database SQL server :

Imports System.Data.SqlClient

Module koneksi

    Public comSQL As New SqlClient.SqlCommand

    Public Sql As String

    Public conn As New SqlConnection

    Public str As String = "Data Source = LENOVO ;initial catalog =201553072 ;trusted_connection = true"

    Public Sub buka_koneksi()

        If conn.State = ConnectionState.Closed Then

            conn.ConnectionString = str

            Try

                conn.Open()

            Catch ex As Exception

                MsgBox("Koneksi gagal :" & ex.ToString)

            End Try

        End If

    End Sub

    Public Sub tutup_koneksi()

        If conn.State = ConnectionState.Open Then

            Try

                conn.Close()

            Catch ex As Exception

                MsgBox("gagal menutup koneksi :" & ex.ToString)

            End Try

        End If

    End Sub

End Module

1. Menu

# Form Menu

Public Class form_menu

    Private Sub KeluarToolStripMenuItem_Click_1(sender As Object, e As EventArgs) Handles KeluarToolStripMenuItem.Click

        Close()

    End Sub

    Private Sub DataBarangToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DataBarangToolStripMenuItem.Click

        form_barang.MdiParent = Me

        form_barang.Show()

        form_supplier.Hide()

    End Sub

    Private Sub DataSupplierToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DataSupplierToolStripMenuItem.Click

        form_supplier.MdiParent = Me

        form_supplier.Show()

        form_pembelian.Hide()

    End Sub

    Private Sub DataPembelianToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DataPembelianToolStripMenuItem.Click

        form_pembelian.MdiParent = Me

        form_pembelian.Show()

        form_barang.Hide()

    End Sub

End Class

2. Form Barang

# Form Barang

-          Save

Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click

        buka_koneksi()

       

        Sql = "insert into barang values ('" & kdbrg.Text & "', '" & nmbrg.Text & "', '" & jnsbrg.Text & "', '" & harga.Text & "', '" & jumlah.Text & "', '" & kadaluarsa.Text & "')"

            comSQL = New SqlCommand(Sql, conn)

            Try

                comSQL.ExecuteNonQuery()

                MsgBox("Data Berhasil Disimpan")

               

            Catch ex As Exception

                MsgBox("Data Gagal Disimpan !!!")

        End Try

        Call updatedata()

        Call bersih()

        tutup_koneksi()

    End Sub

-          Edit

Private Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

        buka_koneksi()

        Sql = "UPDATE barang SET nmbrg='" & nmbrg.Text & "', jnsbrg='" & jnsbrg.Text & "', harga='" & harga.Text & "', jumlah='" & jumlah.Text & "', kadaluarsa='" & kadaluarsa.Text & "' WHERE kdbrg='" & kdbrg.Text & "'"

        comSQL = New SqlCommand(Sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            MsgBox("Update berhasil")

            kdbrg.Enabled = True

            btnsave.Enabled = True

        Catch ex As Exception

            MsgBox("Update gagal !!")

        End Try

        Call updatedata()

        Call bersih()

        tutup_koneksi()

    End Sub

-          Delete

Private Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click

        Dim pesan As String

        pesan = MsgBox("Apakah Anda Ingin Menghapus Data ini ?", vbYesNo, "Konfirmasi")

        If pesan = 6 Then

            hapusbarang()

            kdbrg.Enabled = True

            btnsave.Enabled = True

        End If

    End Sub

-          Laporan

Private Sub btnlap_Click(sender As Object, e As EventArgs) Handles btnlap.Click

        laporanbrg.Show()

    End Sub

-          Close

Private Sub btnclose_Click(sender As Object, e As EventArgs) Handles btnclose.Click

        Close()

    End Sub

3. Form Supplier

# Form  Supplier

-          Edit

Imports System.Data.SqlClient

Public Class form_supplier

    Sub updatedata()

        buka_koneksi()

        Sql = "select *from supplier"

        Dim da As New SqlDataAdapter(Sql, conn)

        Dim ds As New DataSet

        da.Fill(ds)

        Dim dt As New DataTable

        For Each dt In ds.Tables

            dgvsupplier.DataSource = dt

        Next

        tutup_koneksi()

    End Sub 

-          Simpan

Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click

        buka_koneksi()

        Sql = "insert into supplier values ('" & kdsup.Text & "', '" & nmsup.Text & "', '" & tgllhr.Text & "', '" & alamat.Text & "', '" & tlpkntr.Text & "', '" & cbjekel.Text & "', '" & email.Text & "', '" & txtjnsproduk.Text & "')"

        comSQL = New SqlCommand(Sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            MsgBox("Data Berhasil Disimpan")

        Catch ex As Exception

            MsgBox("Data Gagal Disimpan !!!")

        End Try

        Call updatedata()

        Call bersih()

        tutup_koneksi()

    End Sub

-          Hapus

Private Sub btndelete_Click(sender As Object, e As EventArgs) Handlesbtndelete.Click

        Dim pesan As String

        pesan = MsgBox("Apakah Anda Ingin Menghapus Data ini ?", vbYesNo, "Konfirmasi")

        If pesan = 6 Then

            hapussupplier()

            kdsup.Enabled = True

            btnsave.Enabled = True

        End If

    End Sub

-          Laporan

Private Sub btnlaporan_Click(sender As Object, e As EventArgs) Handles btnlaporan.Click

        lapsupplier.Show()

  

    End Sub

-          Close

Private Sub btnclose_Click(sender As Object, e As EventArgs) Handles btnclose.Click

        Close()

    End Sub

4. Form Pembelian

# form pembelian

-          Save

Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click

        buka_koneksi()

        Sql = "insert into pembelian values ('" & nonota.Text & "', '" & kodebrg.Text & "', '" & kodesup.Text & "', '" & tglbeli.Text & "', '" & jumlahbeli.Text & "', '" & hargabeli.Text & "', '" & total.Text & "')"

        comSQL = New SqlCommand(Sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            MsgBox("Data Berhasil Disimpan")

        Catch ex As Exception

            MsgBox("Data Gagal Disimpan !!!")

        End Try

        Call updatedata()

        Call bersih()

        tutup_koneksi()

    End Sub

-          Edit

Private Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

        buka_koneksi()

        Sql = "UPDATE supplier SET kdbrg='" & kodebrg.Text & "', kdsup='" & kodesup.Text & "', tanggalbeli='" & tglbeli.Text & "', jumlahbeli='" & jumlahbeli.Text & "', hargabeli='" & hargabeli.Text & "', total='" & total.Text & "' WHERE nonota='" & nonota.Text & "'"

        comSQL = New SqlCommand(Sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            MsgBox("Update berhasil")

            nonota.Enabled = True

            btnsave.Enabled = True

        Catch ex As Exception

            MsgBox("Update gagal !!")

        End Try

        Call updatedata()

        Call bersih()

        tutup_koneksi()

    End Sub

-          Delete

Private Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click

        Dim pesan As String

        pesan = MsgBox("Apakah Anda Ingin Menghapus Data ini ?", vbYesNo, "Konfirmasi")

        If pesan = 6 Then

            hapuspembelian()

            nonota.Enabled = True

            btnsave.Enabled = True

        End If

    End Sub

-          Laporan

Private Sub btnlap_Click(sender As Object, e As EventArgs) Handles btnlap.Click

        lappembelian.Show()

    End Sub

-          Close

Private Sub btnclose_Click(sender As Object, e As EventArgs) Handles btnclose.Click

        Close()

    End Sub