Public Class frmCoffee
Dim cnn As New OleDb.OleDbConnection
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Me.txtEmpId.Text = ""
Me.txtEmpName.Text = ""
Me.txtPhone.Text = ""
Me.txtAddress.Text = ""
Me.txtEmpId.Tag = ""
'Enable button edit
Me.btnEdit.Enabled = True
'Set button add to add label
Me.btnAdd.Text = "Add"
'
Me.txtEmpId.Focus()
End Sub
Private Sub RefreshData()
If Not cnn.State = ConnectionState.Open Then
'Open connection
cnn.Open()
End If
Dim da As New OleDb.OleDbDataAdapter("SELECT empid as [ID], " & _
"empname as [Name], Gender, Phone, Address " & _
"FROM employee ORDER BY empid", cnn)
Dim dt As New DataTable
'Fill data to dataTable
da.Fill(dt)
'Offer data in data table into datagridview
Me.dgvData.DataSource = dt
'Close connection
cnn.Close()
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim cmd As New OleDb.OleDbCommand
If 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.txtEmpId.Tag & "" = "" Then
'Add new
'Add data to table
cmd.CommandText = "INSERT INTO Employee (empid, empname, gender, phone, address) " & _
"VALUES(" & Me.txtEmpId.Text & ",'" & Me.txtEmpName.Text & "','" & _
Me.cboGender.Text & "','" & Me.txtPhone.Text & "','" & _
Me.txtAddress.Text & "')"
cmd.ExecuteNonQuery()
Else
'Update dato in table
cmd.CommandText = "UPDATE employee " & _
" SET empid=" & Me.txtEmpId.Text & _
", empname='" & Me.txtEmpName.Text & "'" & _
", gender='" & Me.cboGender.Text & "'" & _
", address='" & Me.txtAddress.Text & "'" & _
" WHERE empid=" & Me.txtEmpId.Tag
cmd.ExecuteNonQuery()
End If
'Refresh data in list
RefreshData()
'Clear form
Me.btnClear.PerformClick()
'close connection
cnn.Close()
End Sub
Private Sub frmCoffee_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cnn = New OleDb.OleDbConnection
cnn.ConnectionString = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=" & Application.StartupPath & "\CoffeeProject.accdb"
'
'Get data into list
Me.RefreshData()
End Sub
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 intEmpID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
'Get data from database followed by employee id
'Open connection
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
'Get data into database
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM employee " & _
"WHERE empid=" & intEmpID, cnn)
Dim dt As New DataTable
da.Fill(dt)
Me.txtEmpId.Text = intEmpID
Me.txtEmpName.Text = dt.Rows(0).Item("empname")
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 txtempid
Me.txtEmpId.Tag = intEmpID
'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
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 intEmpID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
'Open connection
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
'Dalete data
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "DELETE FROM employee WHERE empid=" & intEmpID
cmd.ExecuteNonQuery()
'Refresh data
Me.RefreshData()
'Close connection
cnn.Close()
End If
End If
End Sub
End Class
Private Sub RefreshData()
If Not cnn.State = ConnectionState.Open Then
'Open connection
cnn.Open()
End If
Dim sqlQuery as String = " SELECT empid as ID, " & _
" empname as NAME, gender, phone, address " & _
" FROM employee ORDER BY empid "
Dim da As New OleDb.OleDbDataAdapter(sqlQuery , cnn)
Dim dt As New DataTable
'Fill data to dataTable
da.Fill(dt)
'Offer data in data table into datagridview
Me.dgvData.DataSource = dt
'Close connection
cnn.close
End Sub