Imports System.Data.OleDb
Public Class frm_customer
Dim cnn As New OleDb.OleDbConnection
Private Sub frm_customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
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 * FROM Customer ORDER BY ID_cus", cnn)
Dim dt As New DataTable
'fill data to datatable
da.Fill(dt)
'offer data in data table into datagridview
Me.dgview.DataSource = dt
'close connection
cnn.Close()
End Sub
Private Sub frm_customer(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 & "\database.accdb"
Me.RefreshData()
End Sub
Private Function checkdata()
Dim result As Boolean 'ฟังก์ชั่นการเช็คข้อมูลก่อนบันทึก
If txt_cus_id.Text = "" Then
MessageBox.Show("กรุณากรอก รหัสลูกค้า")
result = False
ElseIf txt_cus_name.Text = "" Then
MessageBox.Show("กรุณากรอก ชื่อ")
result = False
ElseIf txt_cus_lname.Text = "" Then
MessageBox.Show("กรุณากรอก นามสกุล")
result = False
ElseIf txt_cus_no.Text = "" Then
MessageBox.Show("กรุณากรอก รหัสประชาชน")
result = False
ElseIf date_bd.Text = "" Then
MessageBox.Show("กรุณากรอก วันเกิด")
result = False
ElseIf txt_cus_sex.Text = "" Then
MessageBox.Show("กรุณากรอก เพศ")
result = False
ElseIf txt_cus_nation.Text = "" Then
MessageBox.Show("กรุณากรอก สัญชาติ")
result = False
ElseIf txt_cus_rac.Text = "" Then
MessageBox.Show("กรุณากรอก เชื้อชาติ")
result = False
ElseIf txt_cus_add.Text = "" Then
MessageBox.Show("กรุณากรอก ที่อยู่")
result = False
ElseIf txt_cus_home.Text = "" Then
MessageBox.Show("กรุณากรอก บ้าน")
result = False
ElseIf txt_cus_road.Text = "" Then
MessageBox.Show("กรุณากรอก ถนน")
result = False
ElseIf txt_cus_districe.Text = "" Then
MessageBox.Show("กรุณากรอก ตำบล")
result = False
ElseIf txt_cus_canton.Text = "" Then
MessageBox.Show("กรุณากรอก อำเภอ")
result = False
ElseIf txt_cus_province.Text = "" Then
MessageBox.Show("กรุณากรอก จังหวัด")
result = False
ElseIf txt_cus_pcode.Text = "" Then
MessageBox.Show("กรุณากรอก รหัสไปรษณีย์")
result = False
ElseIf txt_cus_phone.Text = "" Then
MessageBox.Show("กรุณากรอก เบอร์โทรศัพท์")
result = False
Else
result = True
End If
Return result
End Function
Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
Dim result As Boolean 'กำหนดตัวแปร result ให้เป็นชนิด Boolean เพื่อใช้ในการเปรียบเทียบ
result = checkdata() 'ทำการเปรียบเทียบ ตัวแปร result ให้เป็นฟังก์ชั่น checkdata
If result = True Then 'ถ้า result เท่ากับ checkdata จริงให้โปรแกรทำการบันทึกข้อมูลลงฐานข้อมูล
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.txt_cus_id.Tag & "" = "" Then
'add new
'add data to table
cmd.CommandText = "INSERT INTO Customer(ID_cus, cus_name, cus_lname, cus_no, cus_bd, cus_sex, cus_nation, cus_race, cus_add, cus_home, cus_road, cus_district, cus_canton, cus_province, cus_pcode, cus_phone) " & _
" VALUES(" & Me.txt_cus_id.Text & ",'" & Me.txt_cus_name.Text & "','" & Me.txt_cus_lname.Text & "','" & Me.txt_cus_no.Text & "','" & Me.date_bd.Text & "','" & Me.txt_cus_sex.Text & "','" & Me.txt_cus_nation.Text & "','" & Me.txt_cus_rac.Text & "','" & Me.txt_cus_add.Text & "','" & Me.txt_cus_home.Text & "','" & Me.txt_cus_road.Text & "','" & Me.txt_cus_districe.Text & "','" & Me.txt_cus_canton.Text & "','" & Me.txt_cus_province.Text & "','" & Me.txt_cus_pcode.Text & "','" & Me.txt_cus_phone.Text & "')"
cmd.ExecuteNonQuery()
Else
'update data in table
cmd.CommandText = "UPDATE student " & _
" SET ID_cus=" & Me.txt_cus_id.Text & _
", cus_name='" & Me.txt_cus_name.Text & "'" & _
", cus_lname='" & Me.txt_cus_lname.Text & "'" & _
", cus_no='" & Me.txt_cus_no.Text & "'" & _
", cus_bd='" & Me.date_bd.Text & "'" & _
", cus_sex='" & Me.txt_cus_sex.Text & "'" & _
", cus_nation='" & Me.txt_cus_nation.Text & "'" & _
", cus_race='" & Me.txt_cus_rac.Text & "'" & _
", cus_add='" & Me.txt_cus_add.Text & "'" & _
", cus_home='" & Me.txt_cus_home.Text & "'" & _
", cus_road='" & Me.txt_cus_road.Text & "'" & _
", cus_district='" & Me.txt_cus_districe.Text & "'" & _
", cis_canton='" & Me.txt_cus_canton.Text & "'" & _
", cus_province='" & Me.txt_cus_province.Text & "'" & _
", cus_pcode='" & Me.txt_cus_pcode.Text & "'" & _
", cus_phone='" & Me.txt_cus_phone.Text & "'" & _
" WHERE ID_cus=" & Me.txt_cus_id.Tag
cmd.ExecuteNonQuery()
'refresh data in list
RefreshData()
'close connection
cnn.Close()
End If
End If
End Sub
Private Sub cmdclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclose.Click
Me.Close()
End Sub
Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click
If Me.dgview.Rows.Count > 0 Then
If Me.dgview.SelectedRows.Count > 0 Then
Dim intID_cus As Integer = Me.dgview.SelectedRows(0).Cells("ID_cus").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 Customer " & _
" WHERE ID_cus=" & intID_cus, cnn)
Dim dt As New DataTable
da.Fill(dt)
Me.txt_cus_id.Text = intID_cus
Me.txt_cus_name.Text = dt.Rows(0).Item("cus_name")
Me.txt_cus_lname.Text = dt.Rows(0).Item("cus_lname")
Me.txt_cus_no.Text = dt.Rows(0).Item("cus_no")
Me.date_bd.Text = dt.Rows(0).Item("cus_bd")
Me.txt_cus_sex.Text = dt.Rows(0).Item("cus_sex")
Me.txt_cus_nation.Text = dt.Rows(0).Item("cus_nation")
Me.txt_cus_rac.Text = dt.Rows(0).Item("cus_race")
Me.txt_cus_add.Text = dt.Rows(0).Item("cus_add")
Me.txt_cus_home.Text = dt.Rows(0).Item("cus_home")
Me.txt_cus_road.Text = dt.Rows(0).Item("cus_road")
Me.txt_cus_districe.Text = dt.Rows(0).Item("cus_district")
Me.txt_cus_canton.Text = dt.Rows(0).Item("cus_canton")
Me.txt_cus_province.Text = dt.Rows(0).Item("cus_province")
Me.txt_cus_pcode.Text = dt.Rows(0).Item("cus_pcode")
Me.txt_cus_phone.Text = dt.Rows(0).Item("cus_phone")
'
'hide the id to be edited in TAG of txtstdid in case id is changed
Me.txt_cus_id.Tag = intID_cus
'change button add to update
Me.cmdadd.Text = "Update"
'disable button edit
Me.cmdupdate.Enabled = False
'close connection
cnn.Close()
End If
End If
End Sub
Private Sub cmddel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmddel.Click
If Me.dgview.Rows.Count > 0 Then
If Me.dgview.SelectedRows.Count > 0 Then
Dim intID_cus As Integer = Me.dgview.SelectedRows(0).Cells("ID_cus").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 Customer WHERE stdid=" & intID_cus
cmd.ExecuteNonQuery()
'refresh data
Me.RefreshData()
'close connection
cnn.Close()
End If
End If
End Sub
Private Sub cmdclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclear.Click
Me.txt_cus_id.Text = ""
Me.txt_cus_name.Text = ""
Me.txt_cus_lname.Text = ""
Me.txt_cus_no.Text = ""
Me.date_bd.Text = ""
Me.txt_cus_sex.Text = ""
Me.txt_cus_nation.Text = ""
Me.txt_cus_rac.Text = ""
Me.txt_cus_add.Text = ""
Me.txt_cus_home.Text = ""
Me.txt_cus_road.Text = ""
Me.txt_cus_districe.Text = ""
Me.txt_cus_canton.Text = ""
Me.txt_cus_province.Text = ""
Me.txt_cus_pcode.Text = ""
Me.txt_cus_phone.Text = ""
'enable button edit
Me.cmdupdate.Enabled = True
'set button add to add label
Me.cmdadd.Text = "Add"
'
Me.txt_cus_id.Focus()
End Sub
Private Sub cmdsearch_ID_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsearch_ID.Click
cnn.Open()
Dim myMS As New IO.MemoryStream
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Customer " & _
" WHERE stdid=" & Me.txt_cus_id.Text, cnn)
Dim dt As New DataTable
da.Fill(dt)
If dt.Rows.Count > 0 Then
Me.txt_cus_id.Text = dt.Rows(0).Item("ID_cus") & ""
Me.cmdadd.Enabled = False
Else
MsgBox("Record not found!")
End If
cnn.Close()
End Sub
End Class
Load balance : Server 00
