Pertemuan 13 | 18 Mei 2015 | Koneksi ke database menggunakan Acces dengan vb.net

Koneksi ke database menggunakan Acces dengan vb.net 

• Langkah pertama adalah buka program microsoft visual studio 2008 dengan cara klik Start > All program > Microsoft Visual Studio 2008. 
• Langkah Kedua klik pada create project untuk memulai project baru, atau masuk ke menu File > New Project. 
• Langkah ketiga pilih Windows Forms Application, anda bisa merubah nama project anda pada textbox di bagian bawah. Klik OK. 
• Langkah keempat buatlah desain seperti gambar di bawah ini. Lalu ubahlah propertiesnya seperti pada tabel berikut:

  

• Langkah kelima masukkan koding berikut 

  Public Class form1 Dim cnn As New OleDb.OleDbConnection Dim provider As String Dim dataFile As String Dim connString As String Public myConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection Public dr As OleDb.OleDbDataReader 'button close Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click Me.Close() End Sub ' button add Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim cmd As New OleDb.OleDbCommand If Not cnn.State = ConnectionState.Open Then 'open connection if it is not yet open cnn.Open() End If cmd.Connection = cnn 'check whether add new or update If Me.txtstdID.Tag & "" = "" Then 'add new 'add data to table cmd.CommandText = "INSERT INTO student(stdid, stdname, gender, phone, address) " & _ " VALUES(" & Me.txtstdID.Text & ",'" & Me.txtStdName.Text & "','" & _ Me.cboGender.Text & "','" & Me.txtPhone.Text & "','" & _ Me.txtAddress.Text & "')" cmd.ExecuteNonQuery() Else 'update data in table cmd.CommandText = "UPDATE student " & _ " SET stdid=" & Me.txtstdID.Text & _ ", stdname='" & Me.txtStdName.Text & "'" & _ ", gender='" & Me.cboGender.Text & "'" & _ ", phone='" & Me.txtPhone.Text & "'" & _ ", address='" & Me.txtAddress.Text & "'" & _ " WHERE stdid=" & Me.txtstdID.Tag cmd.ExecuteNonQuery() End If 'refresh data in list RefreshData() 'clear form Me.btnClear.PerformClick() 'close connection cnn.Close() End Sub ' button clear Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click Me.txtstdID.Text = "" Me.txtStdName.Text = "" Me.txtPhone.Text = "" Me.txtAddress.Text = "" Me.txtstdID.Tag = "" 'enable button edit Me.btnEdit.Enabled = True 'set button add to add label Me.btnAdd.Text = "Add" ' Me.txtstdID.Focus() End Sub ' button edit Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click 'check for the selected item in list If Me.dgvData.Rows.Count > 0 Then If Me.dgvData.SelectedRows.Count > 0 Then Dim intStdID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value 'get data from database followed by student id 'open connection If Not cnn.State = ConnectionState.Open Then cnn.Open() End If 'get data into datatable Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM student " & _ " WHERE stdid=" & intStdID, cnn) Dim dt As New DataTable da.Fill(dt) Me.txtstdID.Text = intStdID Me.txtStdName.Text = dt.Rows(0).Item("stdname") Me.cboGender.Text = dt.Rows(0).Item("gender") Me.txtPhone.Text = dt.Rows(0).Item("phone") Me.txtAddress.Text = dt.Rows(0).Item("address") ' 'hide the id to be edited in TAG of txtstdid in case id is changed Me.txtstdID.Tag = intStdID 'change button add to update Me.btnAdd.Text = "Update" 'disable button edit Me.btnEdit.Enabled = False 'close connection cnn.Close() End If End If End Sub ' button delete Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click 'check for the selected item in list If Me.dgvData.Rows.Count > 0 Then If Me.dgvData.SelectedRows.Count > 0 Then Dim intStdID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value 'open connection If Not cnn.State = ConnectionState.Open Then cnn.Open() End If 'delete data Dim cmd As New OleDb.OleDbCommand cmd.Connection = cnn cmd.CommandText = "DELETE FROM student WHERE stdid=" & intStdID cmd.ExecuteNonQuery() 'refresh data Me.RefreshData() 'close connection cnn.Close() End If End If End Sub ' formload Private Sub frmStudent_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load cnn = New OleDb.OleDbConnection cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\data.mdb" ' 'get data into list Me.RefreshData() End Sub End Class

• Langkah keenam untuk mendapatkan hasil dari coding di atas kita tekan F5 pada keyboard anda
• Langkah ketujuh,  jika telah selesai klik tombol exit untuk mengakhiri aplikasi.