Sesi 13 : Pengenalan Pemrograman Database

Pemrograman III (Visual)

Danang Junaedi

Sesi 13 : Pengenalan Pemrograman Database 1. Bwt folder sesi13 kemudian dalam folder tersebut bwt database dengan menggunakan Microsoft Office Access a. Aktifkan Microsoft Office Acces, simpan database dengan nama dbSesi13.mdb b. Pilih Table Design untuk membwt tabel, atur tabel dengan struktur sebagai berikut: Table 1 Struktur Tabel Mahasiswa

No 1

Field Name No_Urut

Data Type Filed Size Keterangan AutoNumber Berisi Nomor Urut Mahasiswa (nomor urut dibwt secara otomatis oleh MSAccess) 2 Nama Text 25 Berisi Nama Mahasiswa 3 No_HP_Telp Text 16 Berisi Nomor HP/Telepon Mahasiswa 4 Alamat Text 100 Berisi Alamat Mahasiswa 5 Kota Text 20 Berisi Nama Kota 6 Propinsi Text 25 Berisi Nama Propinsi 7 Kode_pos Text 5 Berisi Kode Pos Simpan tabel tersebut dengan nama Mahasiswa Open tabel tersebut dan isi dengan data, contoh Table 2 Contoh Isi Tabel Mahasiswa

No No_Urut Nama 1 1 Danang

No_HP_Telp Alamat Kota Propinsi Kode_pos 7278860 Jl. Cikutra Bandung Jawa 40125 No.204-A Barat 2 2 Junaedi 7278860 Jl. Cikutra Bandung Jawa 40125 No.204-A Barat Simpan dan tutup tabel tersebut 2. Buka VB.Net dan bwt aplikasi untuk pengelolaan database, simpan pada folder sesi13 3. Bwt desain form berikut ini

IF-UTAMA

Ver/Rev:0/0

Halaman: 1

Pemrograman III (Visual)

Danang Junaedi

Gambar 1 Form Pengolahan Data Mahasiswa

4. Atur properties dari masing-masing kontrol dalam form pada form di atas sebagai berikut Table 3 Properties Gambar 1

No

Kontrol

1

Form

2

Label

IF-UTAMA

Properties Name Font MaximizeBox MinimizeBox StartPosition Text Name Text Name Text Name Text Name Text Name Text Name Text Name Ver/Rev:0/0

Value frmOlahMhs Arial,12 False False CenterScreen Pengolahan Data Mahasiswa lblNPM NPM lblNamaMhs Nama Mahasiswa lblNoHP_Telp Nomor HP/Telepon lblAlamat Alamat lblKota Kota lblPropinsi Propinsi lblKode_Pos Halaman: 2

Pemrograman III (Visual)

No

Kontrol

3

TextBox

4

ComboBox

5

Button

6

OpenFileDialog

Danang Junaedi

Properties Text Name Text Name Text Name Name Name Name Name Name Name Name Name Name Text Name Text Name Text Name Text Name Text Name Text Name

Value KodePos lblPathDB Lokasi Database lblInfoError Pesan Error txtPath txtError txtNMMhs txtNoHP_Telp txtAlamat txtKota txtPropinsi txtKode_Pos cboNPM btnBrowse &Browse btnCancel &Cancel btnNew &New btnSave &Save btnEdit &Edit btnDelete &Delete OpenFileDialog1

5. Ketik program berikut Imports System.Data.OleDb Public Class frmOlahMhs Inherits System.Windows.Forms.Form Dim intAccion As Integer Public Sub newr() Try Dim dbCommand As OleDbCommand Dim DBConn As OleDbConnection Dim strFN, strLN, strAdd, strCty, strSt As String Dim strDept, strPath As String Dim DBInsert As New OleDbCommand() strFN = txtNmMhs.Text strLN = txtNoHP_Telp.Text strAdd = txtAlamat.Text strCty = txtKota.Text strSt = txtPropinsi.Text strDept = txtKode_Pos.Text strPath = txtPath.Text DBConn = New OleDbConnection("Provider = IF-UTAMA

Ver/Rev:0/0

Halaman: 3

Pemrograman III (Visual)

Danang Junaedi

Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & strPath) DBInsert.CommandText = "Insert Into Mahasiswa " &(Nama,No_HP_Telp,Alamat,Kota,Propinsi,Kode_Pos) Values (" & "'" & strFN & "', " & "'" & strLN & "', " & "'" & strAdd & "', " & "'" & strCty & "', " & "'" & strSt & "', " & "'" & strDept & "')" DBInsert.Connection = DBConn DBInsert.Connection.Open() DBInsert.ExecuteNonQuery() DBConn.Close() txtError.Text = "Save." Catch err As System.Exception txtError.Text = err.Message End Try End Sub Public Sub find() Try Dim dbConn As OleDbConnection Dim dbCommand As New OleDbCommand() Dim strPath As String strPath = txtPath.Text dbConn = New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & strPath) dbCommand.CommandText = "SELECT * FROM Mahasiswa WHERE No_Urut=" & cboNPM.Text dbCommand.Connection = dbConn dbConn.Open() Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader While dbDR.Read txtNmMhs.Text = dbDR("Nama".ToString) txtNoHP_Telp.Text = dbDR("No_HP_Telp".ToString) txtAlamat.Text = dbDR("Alamat".ToString) txtKota.Text = dbDR("Kota".ToString) txtPropinsi.Text = dbDR("Propinsi".ToString) txtKode_Pos.Text = dbDR("Kode_Pos".ToString) End While dbConn.Close() btnEdit.Enabled = True btnDelete.Enabled = True Catch err As System.Exception txtError.Text = err.Message End Try End Sub Public Sub loadCBO() Try IF-UTAMA

Ver/Rev:0/0

Halaman: 4

Pemrograman III (Visual)

Danang Junaedi

Dim dbConn As OleDbConnection Dim dbCommand As New OleDbCommand() Dim strPath As String strPath = txtPath.Text dbConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & strPath) dbCommand.CommandText = "SELECT No_Urut FROM Mahasiswa" dbCommand.Connection = dbConn dbConn.Open() Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader cboNPM.Items.Clear() While dbDR.Read cboNPM.Items.Add(dbDR("No_Urut")) End While dbConn.Close() Catch err As System.Exception txtError.Text = err.Message End Try End Sub Public Sub edit() Try Dim DBConn As OleDbConnection Dim dbCommand As New OleDbCommand() Dim strFN, strLN, strAdd, strCty, strSt As String Dim strDept, strPath As String strFN = txtNmMhs.Text strLN = txtNoHP_Telp.Text strAdd = txtAlamat.Text strCty = txtKota.Text strSt = txtPropinsi.Text strDept = txtKode_Pos.Text strPath = txtPath.Text DBConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & strPath) dbCommand.CommandText = "Update Mahasiswa Set " & "Nama='" & strFN & "', " & "No_HP_Telp='" & strLN & "', " & "Alamat='" & strAdd & "', " & "Kota='" & strCty & "', " & "Propinsi='" & strSt & "', " & "Kode_pos='" & strDept & "' " & "WHERE text=" & cboNPM.Text dbCommand.Connection = DBConn dbCommand.Connection.Open() dbCommand.ExecuteNonQuery() IF-UTAMA

Ver/Rev:0/0

Halaman: 5

Pemrograman III (Visual)

Danang Junaedi

DBConn.Close() txtError.Text = "Edit" Catch err As System.Exception txtError.Text = err.Message End Try End Sub Public Sub delete() Try Dim DBConn As OleDbConnection Dim dbCommand As New OleDbCommand() Dim strPath As String strPath = txtPath.Text DBConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & strPath) dbCommand.CommandText = "Delete From Mahasiswa WHERE text=" & cboNPM.Text dbCommand.Connection = DBConn dbCommand.Connection.Open() dbCommand.ExecuteNonQuery() DBConn.Close() txtError.Text = "Delete." Catch err As System.Exception txtError.Text = err.Message End Try End Sub Private Sub EnabledField() txtNmMhs.ReadOnly = False txtNoHP_Telp.ReadOnly = False txtAlamat.ReadOnly = False txtKota.ReadOnly = False txtPropinsi.ReadOnly = False txtKode_Pos.ReadOnly = False End Sub Private Sub DisabledField() txtNmMhs.ReadOnly = True txtNoHP_Telp.ReadOnly = True txtAlamat.ReadOnly = True txtKota.ReadOnly = True txtPropinsi.ReadOnly = True txtKode_Pos.ReadOnly = True End Sub Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click intAccion = 1 txtNmMhs.Text = "" IF-UTAMA

Ver/Rev:0/0

Halaman: 6

Pemrograman III (Visual)

Danang Junaedi

txtNoHP_Telp.Text = "" txtAlamat.Text = "" txtKota.Text = "" txtPropinsi.Text = "" txtKode_Pos.Text = "" btnNew.Enabled = False btnEdit.Enabled = False btnDelete.Enabled = False btnSave.Enabled = True btnCancel.Enabled = True txtNmMhs.Focus() cboNPM.Items.Clear() cboNPM.Enabled = False EnabledField() txtError.Text = "" End Sub Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click If MsgBox("Simpan hasil perubahan", MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "") = MsgBoxResult.No Then Exit Sub End If If intAccion = 1 Then newr() ElseIf intAccion = 2 Then edit() End If btnSave.Enabled = False btnCancel.Enabled = False btnNew.Enabled = True txtNmMhs.Text = "" txtNoHP_Telp.Text = "" txtAlamat.Text = "" txtKota.Text = "" txtPropinsi.Text = "" txtKode_Pos.Text = "" cboNPM.Enabled = True loadCBO() DisabledField() End Sub Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click btnSave.Enabled = False btnCancel.Enabled = False btnNew.Enabled = True txtNmMhs.Text = "" txtNoHP_Telp.Text = "" IF-UTAMA

Ver/Rev:0/0

Halaman: 7

Pemrograman III (Visual)

Danang Junaedi

txtAlamat.Text = "" txtKota.Text = "" txtPropinsi.Text = "" txtKode_Pos.Text = "" cboNPM.Enabled = True loadCBO() DisabledField() End Sub Private Sub FEmp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Is necesary change the path of database 'loadCBO() MsgBox("Klik Browse untuk menentukan posisi database", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Browse Path Database") End Sub Private Sub cboId_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboNPM.SelectedIndexChanged find() End Sub Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click intAccion = 2 btnNew.Enabled = False btnEdit.Enabled = False btnDelete.Enabled = False btnSave.Enabled = True btnCancel.Enabled = True cboNPM.Enabled = False EnabledField() txtError.Text = "" End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click If MsgBox("Hapus Data", MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "") = MsgBoxResult.No Then cboNPM.Focus() Exit Sub End If delete() btnSave.Enabled = False btnCancel.Enabled = False btnNew.Enabled = True txtNmMhs.Text = "" txtNoHP_Telp.Text = "" txtAlamat.Text = "" txtKota.Text = "" IF-UTAMA

Ver/Rev:0/0

Halaman: 8

Pemrograman III (Visual)

Danang Junaedi

txtPropinsi.Text = "" txtKode_Pos.Text = "" cboNPM.Enabled = True loadCBO() DisabledField() End Sub Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) OpenFileDialog1.ShowDialog() txtPath.Text = OpenFileDialog1.FileName loadCBO() End Sub Private Sub btnBrowse_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click OpenFileDialog1.ShowDialog() txtPath.Text = OpenFileDialog1.FileName loadCBO() End Sub End Class 6. Jalankan program dan bwt penjelasan serta analisisnya (dalam format file doc/docx). Kirim ke imel saya paling lambat Minggu, 12 Desember jam 24:00. Subject imel dan nama file hasil analisis: Pemrograman III-sesi 13- Program di atas adalah hasil modifikasi dari sumber : Balderrama, Israel, Add, Edit and Delete Recordset in Access Database[online],url: http://www.Planet-SourceCode.com/vb/scripts/ShowCode.asp?txtCodeId=2186&lngWId=10, Tanggal Akses

TUGAS KELOMPOK (MAX 5 ORANG PER KELOMPOK) 1. Donlod program dari http://www.Planet-SourceCode.com/vb/scripts/ShowCode.asp?txtCodeId=1991&lngWId=10 dan http://www.Planet-SourceCode.com/vb/scripts/ShowCode.asp?txtCodeId=7088&lngWId=10 2. Jalankan program dan bwt penjelasan serta analisisnya, kemudian bwt perbandingan dengan program pada sesi ini (dalam format file doc/docx). Kirim ke imel saya paling lambat Minggu, 12 Desember jam 24:00. Subject imel dan nama file hasil analisis: Pemrograman III-Tugas05- - - -

IF-UTAMA

Ver/Rev:0/0

Halaman: 9