Imports System.Data.OleDb
Public Class durable
Dim cnn As New OleDb.OleDbConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.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
Private Sub RefreshData()
If Not cnn.State = ConnectionState.Open Then
'open connection
cnn.Open()
End If
Dim da As New OleDb.OleDbDataAdapter("SELECT id as [id] , " & _
" name as [name], category as [category] , Description as [Description],adess as [adess],date as [date]" & _
" FROM durable ORDER BY id ", 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_AutoSizeChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.AutoSizeChanged
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 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 durable (id, name, category, Description, adess, date) " & _
" VALUES (" & Me.txtstdID.Text & "','" & _
Me.txtStdName.Text & "','" & Me.cboGender.Text & "','" & Me.txtPhone.Text & "','" & _
Me.txtAddress.Text & "','" & Me.tb1.Text & "')"
cmd.ExecuteNonQuery()
Else
'update data in table
cmd.CommandText = " UPDATE durable " & _
" SET id=" & Me.txtstdID.Text & _
",name='" & Me.txtStdName.Text & "'" & _
",category='" & Me.cboGender.Text & "'" & _
",Description='" & Me.txtPhone.Text & "'" & _
",adess='" & Me.txtAddress.Text & "'" & _
",date='" & Me.tb1.Text & "'" & _
" WHERE id =" & Me.txtstdID.Tag
cmd.ExecuteNonQuery()
txtstdID.Enabled = True 'กด Update ข้อมูลเข้าฐานข้อมูลเสร็จเปิดช่อง ID เพื่อเพิ่ม ID ได้
End If
'refresh data in list
RefreshData()
'clear form
Me.btnClear.PerformClick()
'close connection
cnn.Close()
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 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 durable " & _
" WHERE id=" & intStdID, cnn)
Dim dt As New DataTable
da.Fill(dt)
Me.txtstdID.Text = intStdID
Me.txtStdName.Text = dt.Rows(0).Item("name")
Me.cboGender.Text = dt.Rows(0).Item("category")
Me.txtPhone.Text = dt.Rows(0).Item("Description")
Me.txtAddress.Text = dt.Rows(0).Item("adess")
Me.tb1.Text = dt.Rows(0).Item("date")
'
'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
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.txtstdID.Text = ""
Me.txtStdName.Text = ""
Me.txtPhone.Text = ""
Me.txtAddress.Text = ""
Me.cboGender.Text = ""
Me.tb1.Text = ""
Me.txtstdID.Tag = ""
'enable buLton edit
Me.btnEdit.Enabled = True
'set button add to add Label
Me.btnAdd.Text = "Add"
'
Me.txtstdID.Focus()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
'ส่วนลบข้อมูล ยืนยันการลบ
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()
Select Case MsgBox("Are you sure ?", MsgBoxStyle.YesNo, "Delete")
Case MsgBoxResult.Yes
' Do something if yes
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "DELETE FROM durable WHERE id=" & intStdID
cmd.ExecuteNonQuery()
'refresh data
Me.RefreshData()
MsgBox("ลบข้อมูลแล้ว")
'close connection
cnn.Close()
Case MsgBoxResult.No
' Do something if no
Me.RefreshData()
MsgBox("ยกเลิกการลบข้อมูล")
End Select
End If
End If
End Sub
End Class