Rekayasa Perangkat Lunak Tugas UTS With Database Access

Project Aplikasi Reservasi Bandara

Analisa Kebutuhan :

• Aplikasi ini berfungsi untuk menyimpan dan Penjualan Tikel Online.
• Mempermudah dalam memasukan data Ke server..

Rancangan Database Name db_project321.mdb

• Table 

1. tbl_info ( ID, kode_pesawat, nama_pesawat, tujuan, harga_tiket, jumlah_penumpang, tanggal_pesan )
2. tbl_user( user_name, pass_word )

Interface Login User

Interface Input Data

frmLogin

Imports System.Data.OleDb Public Class frmLogin Private Sub loginUser() If Len(Trim(txtUsername.Text)) = 0 Then MessageBox.Show("Please enter user name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtUsername.Focus() Exit Sub End If If Len(Trim(txtPassword.Text)) = 0 Then MessageBox.Show("Please enter password", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtPassword.Focus() Exit Sub End If Try Dim myConnection As OleDbConnection myConnection = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=..\db_project321.mdb;Persist Security Info=false;") Dim myCommand As OleDbCommand myCommand = New OleDbCommand("SELECT user_name,pass_word FROM tbl_user WHERE user_name = @user_name AND pass_word = @pass_word", myConnection) Dim uName As New OleDbParameter("@user_name", SqlDbType.VarChar) Dim uPassword As New OleDbParameter("@pass_word", SqlDbType.VarChar) uName.Value = txtUsername.Text uPassword.Value = txtPassword.Text myCommand.Parameters.Add(uName) myCommand.Parameters.Add(uPassword) myCommand.Connection.Open() Dim myReader As OleDbDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection) Dim Login As Object = 0 If myReader.HasRows Then myReader.Read() Login = myReader(Login) End If If Login = Nothing Then MsgBox("Login is Failed...Try again !", MsgBoxStyle.Critical, "Login Denied") txtUsername.Clear() txtPassword.Clear() txtUsername.Focus() Else MsgBox("Successfully Login", MsgBoxStyle.Information) txtUsername.Text = "" txtPassword.Text = "" Me.Hide() frmMain.Show() End If myCommand.Dispose() myConnection.Close() Catch ex As Exception MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click loginUser() End Sub Private Sub txtUsername_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUsername.KeyPress If Asc(e.KeyChar) = 13 Then loginUser() ' or form2.show whatever you form name is End If End Sub Private Sub txtPassword_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtPassword.KeyPress If Asc(e.KeyChar) = 13 Then loginUser() ' or form2.show whatever you form name is End If End Sub Private Sub frmLogin_Load(sender As Object, e As EventArgs) Handles MyBase.Load End Sub End Class



                                                          frmMain

Imports System.Data.OleDb Public Class frmMain Dim dbconn As New OleDbConnection Dim adt As New OleDbDataAdapter Dim ds As New DataSet Dim datatable As New DataTable Dim cmd As New OleDbCommand Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load dbconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=..\db_project321.mdb" showData() 'show database values in datagridview customColumnWidth() ' set custom column width End Sub 'Set customColumnWidth Private Sub customColumnWidth() ' set custom column width 'datagridName.Columns(columnNumber or columnName) Dim columnID As DataGridViewColumn = dgvInfo.Columns(0) columnID.Width = 40 'set columnwidth Dim columnSurname As DataGridViewColumn = dgvInfo.Columns(1) columnSurname.Width = 100 'set columnwidth End Sub Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click 'Confirm dialogBox Dim dr As DialogResult dr = MessageBox.Show("Save information?", "Save", MessageBoxButtons.YesNo) If dr = DialogResult.No Then 'NO CONDITION GOES HERE Else 'YES CONDITION GOES HERE 'Form restriction and validation If Len(Trim(txtSurname.Text)) = 0 Then MessageBox.Show("Please enter Surname", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtSurname.Focus() ElseIf Len(Trim(txtEmail.Text)) = 0 Then MessageBox.Show("Please enter Email", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtEmail.Focus() ElseIf Len(Trim(txtAddress.Text)) = 0 Then MessageBox.Show("Please enter Address", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtAddress.Focus() ElseIf Len(Trim(txtContact_no.Text)) = 0 Then MessageBox.Show("Please enter Contact No", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtContact_no.Focus() Else adt = New OleDbDataAdapter("insert into tbl_info (kode_pesawat, nama_pesawat, tujuan, harga_tiket, jumlah_penumpang, tanggal_pesan) values ('" & txtSurname.Text & "', '" & txtEmail.Text & "', '" & txtAddress.Text & "', '" & txtContact_no.Text & "', '" & TextBox1.Text & "', '" & TextBox2.Text & "')", dbconn) adt.Fill(ds) ds = New DataSet showData() ' refresh data in datagridview MsgBox("Saved") clearText() 'clear all text End If End If End Sub Private Sub showData() Dim dbcommand As String dbcommand = "SELECT * FROM tbl_info" adt = New OleDbDataAdapter(dbcommand, dbconn) datatable = New DataTable adt.Fill(datatable) dgvInfo.DataSource = datatable End Sub Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Try Dim sql = "select * from tbl_info where id like " & txtID.Text & " " adt = New OleDbDataAdapter(sql, dbconn) cmd = New OleDbCommand(sql) adt.Fill(ds, "tbl_info") 'ds.tables(tableName).rows(rowNumber)(columnNumber).tostring txtSurname.Text = ds.Tables("tbl_info").Rows(0)(1).ToString txtEmail.Text = ds.Tables("tbl_info").Rows(0)(2).ToString txtAddress.Text = ds.Tables("tbl_info").Rows(0)(3).ToString txtContact_no.Text = ds.Tables("tbl_info").Rows(0)(4).ToString ds = New DataSet Catch ex As Exception MsgBox("No items match your search", MsgBoxStyle.Exclamation) End Try End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click 'Confirm dialogBox Dim dr As DialogResult dr = MessageBox.Show("Update information?", "Update", MessageBoxButtons.YesNo) If dr = DialogResult.No Then 'NO CONDITION GOES HERE Else 'YES CONDITION GOES HERE 'Form restriction and validation If Len(Trim(txtSurname.Text)) = 0 Then MessageBox.Show("Please enter Surname", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtSurname.Focus() ElseIf Len(Trim(txtEmail.Text)) = 0 Then MessageBox.Show("Please enter Email", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtEmail.Focus() ElseIf Len(Trim(txtAddress.Text)) = 0 Then MessageBox.Show("Please enter Address", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtAddress.Focus() ElseIf Len(Trim(txtContact_no.Text)) = 0 Then MessageBox.Show("Please enter Contact No", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtContact_no.Focus() Else adt = New OleDbDataAdapter("update tbl_info set surname='" & txtSurname.Text & "', email='" & txtEmail.Text & "', address='" & txtAddress.Text & "', contact_no='" & txtContact_no.Text & "'where id=" & txtID.Text & "", dbconn) adt.Fill(ds) ds = New DataSet showData() ' refresh data in datagridview MsgBox("Updated") clearText() 'clear all text End If End If End Sub Private Sub dgvInfo_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvInfo.CellClick Dim i As Integer i = dgvInfo.CurrentRow.Index 'txtName = datagridName.Item(columnName or columnNumber, rowNumber).value.tostring txtID.Text = dgvInfo.Item(0, i).Value.ToString txtSurname.Text = dgvInfo.Item(1, i).Value.ToString txtEmail.Text = dgvInfo.Item(2, i).Value.ToString txtAddress.Text = dgvInfo.Item(3, i).Value.ToString txtContact_no.Text = dgvInfo.Item(4, i).Value.ToString End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click 'Confirm dialogBox Dim dr As DialogResult dr = MessageBox.Show("Delete information?", "Delete", MessageBoxButtons.YesNo) If dr = DialogResult.No Then 'NO CONDITION GOES HERE Else 'YES CONDITION GOES HERE 'Form restriction and validation If Len(Trim(txtSurname.Text)) = 0 Then MessageBox.Show("Please enter Surname", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtSurname.Focus() ElseIf Len(Trim(txtEmail.Text)) = 0 Then MessageBox.Show("Please enter Email", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtEmail.Focus() ElseIf Len(Trim(txtAddress.Text)) = 0 Then MessageBox.Show("Please enter Address", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtAddress.Focus() ElseIf Len(Trim(txtContact_no.Text)) = 0 Then MessageBox.Show("Please enter Contact No", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtContact_no.Focus() Else adt = New OleDbDataAdapter("delete surname='" & txtSurname.Text & "', email='" & txtEmail.Text & "', address='" & txtAddress.Text & "', contact_no='" & txtContact_no.Text & "' from tbl_info where id=" & txtID.Text & "", dbconn) adt.Fill(ds) ds = New DataSet showData() ' refresh data in datagridview MsgBox("Deleted") clearText() 'clear all text End If End If End Sub Sub clearText() 'clear all text txtID.Text = "" txtSurname.Text = "" txtEmail.Text = "" txtAddress.Text = "" txtContact_no.Text = "" TextBox1.Text = "" TextBox2.Text = "" End Sub Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click 'Confirm dialogBox Dim dr As DialogResult dr = MessageBox.Show("Clear text?", "Clear", MessageBoxButtons.YesNo) If dr = DialogResult.No Then 'NO CONDITION GOES HERE Else 'YES CONDITION GOES HERE clearText() 'clear all text End If End Sub Private Sub txtContact_no_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtContact_no.KeyPress 'Input number only If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(8) Then e.Handled = True End If End Sub Private Sub txtID_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtID.KeyPress 'Input number only If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(8) Then e.Handled = True End If End Sub 'copyright © 2017 ITGeekSolutions Private Sub txtID_TextChanged(sender As Object, e As EventArgs) Handles txtID.TextChanged End Sub Private Sub txtSurname_TextChanged(sender As Object, e As EventArgs) Handles txtSurname.TextChanged End Sub Private Sub Label7_Click(sender As Object, e As EventArgs) Handles Label7.Click End Sub Private Sub GroupBox2_Enter(sender As Object, e As EventArgs) Handles GroupBox2.Enter End Sub Private Sub dgvInfo_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvInfo.CellContentClick End Sub Private Sub Label6_Click(sender As Object, e As EventArgs) Handles Label6.Click End Sub End Class