Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.OleDb
Public Class frmMember
Inherits System.Windows.Forms.Form
Const DATABASE_FILE As String = "C:\\Database\\MemberDB.mdb"
Dim strConnection As String = " Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DATABASE_FILE
Dim Conn As New OleDbConnection(strConnection)
Dim da As OleDbDataAdapter
Dim ds As DataSet = New DataSet
Dim dt As DataTable
Dim CurrentRecord As Integer = 0
Dim RecordCount As Integer = 0
Dim ActionFlag As String = ""
Dim tmpFileName As String = ""
Dim IsFind As Boolean = False
Dim ToClose As Boolean = False
Private Sub frmMember_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Cursor.Current = Cursors.WaitCursor
dtpDateReg.Enabled = False
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Dim sqlMember As String = ""
sqlMember = "SELECT * FROM Member"
sqlMember &= " ORDER BY MemberID"
da = New OleDbDataAdapter(sqlMember, Conn)
da.Fill(ds, "Member")
With cboSearch
.Items.Add("รหัสสมาชิก")
.Items.Add("ชื่อสมาชิก")
.SelectedIndex = 0
End With
ControlStatus(False)
Cursor.Current = Cursors.Default
btnEdit.Enabled = False
btnSave.Visible = False
btnCancel.Visible = False
btnMoveFirst.Enabled = False
btnMovePrevious.Enabled = False
btnMoveNext.Enabled = False
btnMoveLast.Enabled = False
End Sub
Private Sub txtSearch_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtSearch.KeyDown
If txtSearch.Text <> "" Then
If (e.KeyCode = Keys.Enter) Then
Dim sqlMember As String = ""
sqlMember = "SELECT * FROM Member"
If cboSearch.SelectedIndex = 0 Then
sqlMember &= " WHERE (MemberID='" & txtSearch.Text & "')"
ElseIf cboSearch.SelectedIndex = 1 Then
sqlMember &= " WHERE (Name LIKE '%" & txtSearch.Text & "%')"
End If
sqlMember &= " ORDER BY MemberID"
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
If IsFind = True Then
ds.Tables("Member").Clear()
End If
da.SelectCommand.CommandText = sqlMember
da.Fill(ds, "Member")
If ds.Tables("Member").Rows.Count <> 0 Then
IsFind = True
ClearAllBinding()
txtMemberID.DataBindings.Add("Text", ds, "Member.MemberID")
txtTitle.DataBindings.Add("Text", ds, "Member.Title")
txtName.DataBindings.Add("Text", ds, "Member.Name")
txtAddress.DataBindings.Add("Text", ds, "Member.Address")
txtTambon.DataBindings.Add("Text", ds, "Member.Tambon")
txtAmpor.DataBindings.Add("Text", ds, "Member.Ampor")
txtProvince.DataBindings.Add("Text", ds, "Member.Province")
txtTel.DataBindings.Add("Text", ds, "Member.Tel")
dtpDateReg.DataBindings.Add("Text", ds, "Member.DateReg")
dtpDateUnReg.DataBindings.Add("Text", ds, "Member.DateUnReg")
txtNote.DataBindings.Add("Text", ds, "Member.Note")
dt = ds.Tables("Member")
ShowPosition()
btnAdd.Enabled = False
btnEdit.Enabled = True
btnMoveFirst.Enabled = True
btnMovePrevious.Enabled = True
btnMoveNext.Enabled = True
btnMoveLast.Enabled = True
btnEdit.Focus()
Else
IsFind = False
MessageBox.Show("ข้อมูลที่คุณระบุ ไม่มี !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearData()
lblShowPosition.Text = ""
btnAdd.Enabled = True
btnEdit.Enabled = False
btnMoveFirst.Enabled = False
btnMovePrevious.Enabled = False
btnMoveNext.Enabled = False
btnMoveLast.Enabled = False
txtSearch.Focus()
txtSearch.SelectAll()
Exit Sub
End If
End If
End If
End Sub
Private Sub ClearAllBinding()
txtMemberID.DataBindings.Clear()
txtName.DataBindings.Clear()
txtAddress.DataBindings.Clear()
txtTambon.DataBindings.Clear()
txtAmpor.DataBindings.Clear()
txtProvince.DataBindings.Clear()
txtTel.DataBindings.Clear()
dtpDateReg.DataBindings.Clear()
dtpDateUnReg.DataBindings.Clear()
txtNote.DataBindings.Clear()
End Sub
Private Sub ShowPosition()
Try
CurrentRecord = Me.BindingContext(ds, "Member").Position + 1
RecordCount = Me.BindingContext(ds, "Member").Count
lblShowPosition.Text = "ระเบียน : " & CurrentRecord & " / " & RecordCount
Catch
End Try
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
ControlStatus(True)
ActionFlag = "Add"
grbSearch.Enabled = False
grbManage.Enabled = False
dtpDateReg.Enabled = True
btnAdd.Enabled = False
btnEdit.Enabled = False
btnSave.Visible = True
btnCancel.Visible = True
If IsFind = True Then
ds.Tables("Member").Clear()
End If
txtMemberID.Focus()
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
ControlStatus(True)
ActionFlag = "Edit"
txtMemberID.ReadOnly = True
grbSearch.Enabled = False
grbManage.Enabled = False
dtpDateReg.Enabled = True
btnEdit.Enabled = False
btnSave.Visible = True
btnCancel.Visible = True
txtName.Focus()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim sqlMember As String = ""
Dim comProducts As OleDbCommand = New OleDbCommand
Dim tmpMSG As String = ""
If (txtMemberID.Text = "") Or (txtName.Text = "") Or (txtAddress.Text = "") Or (txtTambon.Text = "") Or (txtAmpor.Text = "") Or (txtProvince.Text = "") Or (txtTel.Text = "") Then
MessageBox.Show("ท่านป้อนข้อมูลไม่ครบ กรุณาตรวจสอบและป้อนข้อมูลให้ครบด้วย", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
Try
Select Case ActionFlag
Case "Add"
sqlMember = "INSERT INTO Member (MemberID,Title,Name,Address,Tambon,Ampor,"
sqlMember &= " Province,Tel,DateReg,DateUnReg,"
sqlMember &= " Note)"
sqlMember &= " VALUES ('" & txtMemberID.Text & "',"
sqlMember &= "'" & txtTitle.Text & "',"
sqlMember &= "'" & txtName.Text & "',"
sqlMember &= "'" & txtAddress.Text & "',"
sqlMember &= "'" & txtTambon.Text & "',"
sqlMember &= "'" & txtAmpor.Text & "',"
sqlMember &= "'" & txtProvince.Text & "',"
sqlMember &= "'" & txtTel.Text & "',"
sqlMember &= "'" & dtpDateReg.Value & "',"
sqlMember &= "'" & dtpDateUnReg.Value & "',"
sqlMember &= "'" & txtNote.Text & "')"
Case "Edit"
sqlMember = "UPDATE Member"
sqlMember &= " SET Title='" & txtTitle.Text & "',"
sqlMember &= " SET Name='" & txtName.Text & "',"
sqlMember &= " Address='" & txtAddress.Text & "',"
sqlMember &= " Tambon='" & txtTambon.Text & "',"
sqlMember &= " Ampor='" & txtAmpor.Text & "',"
sqlMember &= " Province='" & txtProvince.Text & "',"
sqlMember &= " Tel='" & txtTel.Text & "',"
sqlMember &= " DateReg='" & dtpDateReg.Value & "',"
sqlMember &= " DateUnReg='" & dtpDateUnReg.Value & "',"
sqlMember &= " Note='" & txtNote.Text & "'"
sqlMember &= " WHERE (MemberID='" & txtMemberID.Text & "')"
End Select
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
If ActionFlag = "Add" Then
tmpMSG = "คุณต้องการเพิ่มข้อมูล ใช่หรือไม่ ?"
ElseIf ActionFlag = "Edit" Then
tmpMSG = "คุณต้องการแก้ไขข้อมูล ใช่หรือไม่ ?"
End If
If MessageBox.Show(tmpMSG, "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
With comProducts
.CommandType = CommandType.Text
.CommandText = sqlMember
.Connection = Conn
.ExecuteNonQuery()
End With
MessageBox.Show("บันทึกลงฐานข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
dtpDateReg.Enabled = False
End If
Catch ex As OleDbException
MsgBox(ex.Message)
MessageBox.Show("คุณป้อนรหัสอุปกรณ์ ซ้ำกัน !!!", "ผลการตรวสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtMemberID.Focus()
txtMemberID.SelectAll()
Exit Sub
Finally
Conn.Close()
End Try
grbSearch.Enabled = True
grbManage.Enabled = True
ControlStatus(False)
btnAdd.Enabled = True
btnEdit.Enabled = False
btnSave.Visible = False
btnCancel.Visible = False
ClearData()
txtMemberID.ReadOnly = False
btnMoveFirst.Enabled = False
btnMovePrevious.Enabled = False
btnMoveNext.Enabled = False
btnMoveLast.Enabled = False
lblShowPosition.Text = ""
txtSearch.Text = ""
txtSearch.Focus()
End Sub
Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
grbSearch.Enabled = True
grbManage.Enabled = True
dtpDateReg.Enabled = False
ControlStatus(False)
btnAdd.Enabled = True
btnEdit.Enabled = False
btnSave.Visible = False
btnCancel.Visible = False
ClearData()
txtMemberID.ReadOnly = False
btnMoveFirst.Enabled = False
btnMovePrevious.Enabled = False
btnMoveNext.Enabled = False
btnMoveLast.Enabled = False
lblShowPosition.Text = ""
txtSearch.Focus()
txtSearch.SelectAll()
End Sub
Private Sub ControlStatus(ByVal CtrlStatus As Boolean)
txtMemberID.Enabled = CtrlStatus
txtName.Enabled = CtrlStatus
txtAddress.Enabled = CtrlStatus
txtProvince.Enabled = CtrlStatus
txtTambon.Enabled = CtrlStatus
txtAmpor.Enabled = CtrlStatus
txtTel.Enabled = CtrlStatus
txtNote.Enabled = CtrlStatus
End Sub
Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
Me.BindingContext(ds, "Member").Position = 0
ShowPosition()
End Sub
Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
Me.BindingContext(ds, "Member").Position -= 1
ShowPosition()
End Sub
Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
Me.BindingContext(ds, "Member").Position += 1
ShowPosition()
End Sub
Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
Me.BindingContext(ds, "Member").Position = ds.Tables("Member").Rows.Count - 1
ShowPosition()
End Sub
Private Sub ClearData()
txtMemberID.Text = ""
txtName.Text = ""
txtAddress.Text = ""
txtProvince.Text = ""
txtTambon.Text = ""
txtAmpor.Text = ""
txtTel.Text = ""
txtNote.Text = ""
End Sub
Private Sub txtRemain_P_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTambon.KeyPress
If e.KeyChar < "0" Or e.KeyChar > "9" Then
e.Handled = True
End If
End Sub
Private Sub txtPriceUnit_P_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAmpor.KeyPress
If e.KeyChar < "0" Or e.KeyChar > "9" Then
e.Handled = True
End If
End Sub
Private Sub txtNum_Check_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTel.KeyPress
If e.KeyChar < "0" Or e.KeyChar > "9" Then
e.Handled = True
End If
End Sub
Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter
End Sub
End Class
Load balance : Server 00
Quote:
