Option Explicit On
Option Strict On
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D
Imports System.IO
Imports System.Drawing
Imports System.Globalization
Public Class frmEmployee
Dim Conn As SqlConnection
Dim com As SqlCommand
Dim dr As SqlDataReader
Dim dtHuman As DataTable
Dim bs As BindingSource = New BindingSource()
Dim tr As SqlTransaction
Dim sb As StringBuilder
Dim CPID As String
Dim CWID As String
Dim Today As DateTime
Dim fs As FileStream
Dim CurrentImage() As Byte
Dim picFileName As String
Dim CBMP As Bitmap
Dim UserName As String
Dim sw As New Stopwatch()
Dim ProcessTime As Double
Dim _ToolStripLabel As New ToolStripLabel
Dim Add As Boolean = False
Dim Edit As Boolean = False
Dim thCul As System.Globalization.CultureInfo = System.Globalization.CultureInfo.GetCultureInfo("th-TH")
Public Sub New(ByRef refToolStripLabel As ToolStripLabel)
InitializeComponent()
_ToolStripLabel = refToolStripLabel
End Sub
Private Sub frmAddHuman_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.WindowState = FormWindowState.Maximized
gbxempp.BackColor = Color.FromArgb(0, 0, 0, 0)
Dim FD As New FormFader
FD.FadeIn(Me, 0.25)
sw.Reset()
sw.Start()
Dim strConn As String
strConn = DBMtech.strcnn
Conn = New SqlConnection()
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
sb = New StringBuilder()
sb.Append("SELECT * FROM TbPosition;")
sb.Append("SELECT * FROM TbDepartment;")
Dim sqlIni As String
sqlIni = sb.ToString()
com = New SqlCommand()
With com
.CommandText = sqlIni
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader()
End With
If dr.HasRows Then
Dim dtPosition As DataTable
dtPosition = New DataTable()
dtPosition.Load(dr)
With cboPosition
.BeginUpdate()
.DisplayMember = "PositionName"
.ValueMember = "PositionID"
.DataSource = dtPosition
.EndUpdate()
End With
Dim dtDepartment As DataTable
dtDepartment = New DataTable()
dtDepartment.Load(dr)
With cbodepartment
.BeginUpdate()
.DisplayMember = "DepartmentName"
.ValueMember = "DepartmentID"
.DataSource = dtDepartment
.EndUpdate()
End With
End If
cbodepartment.ContextMenu = New ContextMenu()
cboPosition.ContextMenu = New ContextMenu()
Today = DateTime.Today
Showdata()
FormatDgvEmployeeList()
Textdisable()
sw.Stop()
ProcessTime = sw.ElapsedMilliseconds / 1000
_ToolStripLabel.Text = "เวลาที่ใช้ : " & ProcessTime.ToString("0.0000") & " วินาที"
End Sub
Private Sub ClearHuman()
txtempid.Text = ""
txtname.Text = ""
txtAddress.Text = ""
txttel.Text = ""
End Sub
Private Sub Textenable()
txtempid.Enabled = True
txtname.Enabled = True
cboInitial.Enabled = True
cboPosition.Enabled = True
cbodepartment.Enabled = True
txtAddress.Enabled = True
txttel.Enabled = True
End Sub
Private Sub Textdisable()
txtempid.Enabled = False
txtname.Enabled = False
cboInitial.Enabled = False
cboPosition.Enabled = False
cbodepartment.Enabled = False
txtAddress.Enabled = False
txttel.Enabled = False
End Sub
Private Sub Showdata()
Dim sqlEmployee As String
sqlEmployee = "SELECT * FROM TbEmployee ORDER BY EmployeeID"
Dim dr As SqlDataReader
Dim dt As DataTable
com = New SqlCommand()
With com
.CommandType = CommandType.Text
.CommandText = sqlEmployee
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dt = New DataTable()
dt.Load(dr)
dgvemplist.DataSource = dt
Else
dgvemplist.DataSource = Nothing
End If
dr.Close()
End With
End Sub
Private Sub FormatDgvEmployeeList()
With dgvemplist
If .RowCount > 0 Then
.Columns(0).HeaderText = "รหัสพนักงาน"
.Columns(1).HeaderText = "คำนำหน้าชื่อ"
.Columns(2).HeaderText = "ชื่อ-นามสกุลพนักงาน"
.Columns(3).HeaderText = "ตำแหน่งงาน"
.Columns(4).HeaderText = "สังกัดฝ่าย"
.Columns(5).HeaderText = "ที่อยู่"
.Columns(6).HeaderText = "เบอร์โทร"
.Columns(0).Width = 80
.Columns(1).Width = 60
.Columns(2).Width = 120
.Columns(3).Width = 100
.Columns(4).Width = 100
.Columns(5).Width = 220
.Columns(6).Width = 100
End If
End With
End Sub
Private Sub dgvemplist_CellMouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvemplist.CellMouseUp
If e.RowIndex = -1 Then Exit Sub
With dgvemplist
txtempid.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString()
cboInitial.SelectedValue = .Rows.Item(e.RowIndex).Cells(1).ValueType.ToString()
txtname.Text = .Rows.Item(e.RowIndex).Cells(2).Value.ToString()
cbodepartment.SelectedValue = .Rows.Item(e.RowIndex).Cells(3).Value.ToString()
cboPosition.SelectedValue = .Rows.Item(e.RowIndex).Cells(4).Value.ToString()
txtAddress.Text = .Rows.Item(e.RowIndex).Cells(5).Value.ToString()
txttel.Text = .Rows.Item(e.RowIndex).Cells(6).Value.ToString()
txtname.Focus()
txtname.SelectAll()
End With
End Sub
Private Sub ClearBinding()
txtempid.DataBindings.Clear()
cbodepartment.DataBindings.Clear()
cboPosition.DataBindings.Clear()
txtname.DataBindings.Clear()
txtAddress.DataBindings.Clear()
txttel.DataBindings.Clear()
End Sub
Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
If (txtname.Text.Trim() = "") OrElse (txtAddress.Text.Trim() = "") OrElse (txttel.Text.Trim() = "") Then
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtname.Focus()
Exit Sub
End If
If cboPosition.Text.Trim() = "" Then
MessageBox.Show("กรุณาคลิกเลือกตำแหน่งงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
If cbodepartment.Text.Trim() = "" Then
MessageBox.Show("กรุณาคลิกเลือกตำแหน่งงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
If Add Then
If MessageBox.Show("คุณต้องการบันทึกข้อมูลพนักงานใหม่ ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
sw.Reset()
sw.Start()
tr = Conn.BeginTransaction()
Dim dtfInfo As DateTimeFormatInfo
dtfInfo = DateTimeFormatInfo.InvariantInfo
Try
sb.Remove(0, sb.Length)
sb.Append(" INSERT INTO TbEmployee (EmployeeID,Initial,EmployeeName,")
sb.Append(" PositionID,DepartmentID,EmployeeAddress,EmployeeTel)")
sb.Append(" VALUES (@EmployeeID,@Initial,@EmployeeName,")
sb.Append(" @PositionID,@DepartmentID,@EmployeeAddress,@EmployeeTel)")
Dim sqlAdd As String
sqlAdd = sb.ToString()
Dim time As String
time = "01/07/2009 9:11:23 pm"
With com
.CommandText = sqlAdd
.CommandType = CommandType.Text
.Connection = Conn
.Transaction = tr
.Parameters.Clear()
.Parameters.Add("@EmployeeID", SqlDbType.NVarChar).Value = txtempid.Text
.Parameters.Add("@Initial", SqlDbType.NVarChar).Value = cboInitial.SelectedItem.ToString()
.Parameters.Add("@EmployeeName", SqlDbType.NVarChar).Value = txtname.Text.Trim()
.Parameters.Add("@EmployeeAddress", SqlDbType.NVarChar).Value = txtAddress.Text.Trim()
.Parameters.Add("@EmployeeTel", SqlDbType.NVarChar).Value = txttel.Text.Trim()
.Parameters.Add("@PositionID", SqlDbType.NVarChar).Value = cboPosition.SelectedValue.ToString
.Parameters.Add("@DepartmentID", SqlDbType.NVarChar).Value = cbodepartment.SelectedValue.ToString
.ExecuteNonQuery()
End With
tr.Commit()
sw.Stop()
ProcessTime = sw.ElapsedMilliseconds / 1000
_ToolStripLabel.Text = "เวลาที่ใช้ : " & ProcessTime.ToString("0.0000") & " วินาที"
MessageBox.Show("บันทึกข้อมูลพนักงานใหม่ เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearHuman()
Showdata()
FormatDgvEmployeeList()
cmdadd.Enabled = True
cmdsave.Enabled = False
cmdedit.Enabled = True
cmdclar.Enabled = True
cmdcancel.Enabled = False
cmddel.Enabled = True
Textdisable()
Catch ex As Exception
MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
tr.Rollback()
End Try
End If
Add = False
End If
If Edit Then
If MessageBox.Show("คุณต้องการ แก้ไขข้อมูลพนักงานใหม่ ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
sw.Reset()
sw.Start()
tr = Conn.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append("UPDATE TbEmployee ")
sb.Append(" SET EmployeeName=@EmployeeName,")
sb.Append("Initial=@Initial,")
sb.Append("PositionID=@PositionID,")
sb.Append("EmployeeAddress=@EmployeeAddress,")
sb.Append("EmployeeTel=@EmployeeTel,")
sb.Append("DepartmentID=@DepartmentID)")
sb.Append(" WHERE (EmployeeID=@EmployeeID)")
Dim sqlEdit As String
sqlEdit = sb.ToString()
With com
.CommandText = sqlEdit
.CommandType = CommandType.Text
.Connection = Conn
.Transaction = tr
.Parameters.Clear()
.Parameters.Add("@Initial", SqlDbType.NVarChar).Value = CStr(cboInitial.SelectedValue)
.Parameters.Add("@EmployeeName", SqlDbType.NVarChar).Value = txtname.Text.Trim()
.Parameters.Add("@EmployeeAddress", SqlDbType.NVarChar).Value = txtAddress.Text.Trim()
.Parameters.Add("@EmployeeTel", SqlDbType.NVarChar).Value = txttel.Text.Trim()
.Parameters.Add("@PositinID", SqlDbType.NVarChar).Value = CStr(cboPosition.SelectedValue)
.Parameters.Add("@DepartmentID", SqlDbType.NVarChar).Value = CStr(cbodepartment.SelectedValue)
'.ExecuteNonQuery()
End With
tr.Commit()
sw.Stop()
ProcessTime = sw.ElapsedMilliseconds / 1000
_ToolStripLabel.Text = "เวลาที่ใช้ : " & ProcessTime.ToString("0.0000") & " วินาที"
MessageBox.Show("บันทึกข้อมูลพนักงานใหม่ เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearHuman()
Showdata()
FormatDgvEmployeeList()
cmdadd.Enabled = True
cmdsave.Enabled = False
cmdedit.Enabled = True
cmdclar.Enabled = True
cmdcancel.Enabled = True
cmddel.Enabled = True
Catch ex As Exception
tr.Rollback()
MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try
End If
Edit = False
End If
txtempid.Focus()
End Sub
Protected Overrides Sub OnPaint(ByVal e As System.Windows.Forms.PaintEventArgs)
Dim lgb As New LinearGradientBrush(ClientRectangle, Color.FloralWhite, Color.Moccasin, LinearGradientMode.Vertical)
e.Graphics.FillRectangle(lgb, ClientRectangle)
lgb.Dispose()
End Sub
Private Sub frmAddHuman_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
If Conn IsNot Nothing Then
Conn.Close()
End If
End Sub
Private Sub cboPosition_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cboPosition.KeyPress
e.Handled = True
End Sub
Private Sub cboProvince_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
e.Handled = True
End Sub
Private Sub cboDepartment_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cbodepartment.KeyPress
e.Handled = True
End Sub
Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
Textenable()
Add = True
cmdadd.Enabled = False
cmdsave.Enabled = True
cmdedit.Enabled = False
cmdclar.Enabled = False
cmddel.Enabled = False
cmdcancel.Enabled = True
txtempid.Focus()
End Sub
Private Sub cmdAutoID_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAutoID.Click
Dim sqlAuto As String
Dim dr As SqlDataReader
Dim temp As String = ""
Dim nextnum As Int32
sqlAuto = "SELECT MAX(EmployeeID) EmployeeID FROM TbEmployee"
com = New SqlCommand()
With com
.CommandType = CommandType.Text
.CommandText = sqlAuto
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
While dr.Read
temp = CStr(dr.Item("EmployeeID"))
temp = temp.Substring(3, 3)
nextnum = CInt(temp)
nextnum += 1
End While
End If
dr.Close()
End With
If nextnum < 10 Then
txtempid.Text = "MT-00" & nextnum
ElseIf nextnum > 9 And nextnum < 100 Then
txtempid.Text = "MT-0" & nextnum
ElseIf nextnum > 99 And nextnum < 1000 Then
txtempid.Text = "MT-" & nextnum
End If
End Sub
Private Sub cmdedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdedit.Click
If (txtname.Text.Trim() = "") OrElse (txtAddress.Text.Trim() = "") OrElse (txttel.Text.Trim() = "") Then
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtname.Focus()
Exit Sub
End If
If cboPosition.Text.Trim() = "" Then
MessageBox.Show("กรุณาคลิกเลือกตำแหน่งงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
If cbodepartment.Text.Trim() = "" Then
MessageBox.Show("กรุณาคลิกเลือกตำแหน่งงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
Edit = True
cmdadd.Enabled = False
cmdsave.Enabled = True
cmdedit.Enabled = False
cmdclar.Enabled = False
cmdcancel.Enabled = True
cmddel.Enabled = False
txtempid.Focus()
End Sub
Private Sub cmdclar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclar.Click
ClearHuman()
txtempid.Focus()
Showdata()
End Sub
Private Sub cmdcancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel.Click
cmdadd.Enabled = True
cmdsave.Enabled = False
cmdedit.Enabled = False
cmdclar.Enabled = False
cmdcancel.Enabled = False
Textdisable()
txtempid.Focus()
ClearHuman()
End Sub
Private Sub cmddel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmddel.Click
If txtempid.Text.Trim() = "" Then
MessageBox.Show("กรุณาป้อนรหัสพนักงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtempid.Focus()
Exit Sub
End If
If MessageBox.Show("คุณต้องการลบข้อมูลพนักงาน ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
sw.Reset()
sw.Start()
tr = Conn.BeginTransaction()
Try
sb = New StringBuilder()
sb.Append("DELETE FROM TbEmployee")
sb.Append(" WHERE (EmployeeID=@EmployeeID)")
Dim sqlDel As String
sqlDel = sb.ToString()
With com
.CommandText = sqlDel
.CommandType = CommandType.Text
.Connection = Conn
.Transaction = tr
.Parameters.Clear()
.Parameters.Add("@EmployeeID", SqlDbType.NVarChar).Value = txtempid.Text.Trim()
Dim result As Integer
result = .ExecuteNonQuery()
If result = 0 Then
tr.Rollback()
MessageBox.Show("รหัสพนักงานที่คุณป้อน ไม่ถูกต้อง !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtempid.SelectAll()
Else
tr.Commit()
sw.Stop()
ProcessTime = sw.ElapsedMilliseconds / 1000
_ToolStripLabel.Text = "เวลาที่ใช้ : " & ProcessTime.ToString("0.0000") & " วินาที"
MessageBox.Show("ลบข้อมูลพนักงาน เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearHuman()
Showdata()
FormatDgvEmployeeList()
cmdadd.Enabled = True
cmdsave.Enabled = True
cmdedit.Enabled = True
cmdclar.Enabled = True
cmdcancel.Enabled = True
cmddel.Enabled = True
Textdisable()
End If
End With
Catch ex As Exception
tr.Rollback()
MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก ข้อมูลที่จะลบถูกใช้งานอยู่ค่ะ " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Try
End If
txtempid.Focus()
End Sub
Private Sub cmdexitform_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdexitform.Click
Me.Close()
End Sub
Private Sub btfind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim cmd As SqlCommand
With Conn
If .State = ConnectionState.Open Then .Close()
Conn.Open()
End With
Dim sqlSelect As String
If Txtenpfind.Text = "" Then
MessageBox.Show("กรุณากรอกข้อมูลที่ต้องการค้นหา", "ตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
Else
sqlSelect = "SELECT EmployeeID,Initial,EmployeeName,PositionID,DepartmentID,EmployeeAddress,EmployeeTel" & " FROM TbEmployee" & _
" WHERE EmployeeName LIKE'" & Txtenpfind.Text & "%'"
End If
cmd = New SqlCommand()
With cmd
.CommandType = CommandType.Text
.CommandText = sqlSelect
.Connection = Conn
dr = .ExecuteReader()
Dim dt As DataTable
If dr.HasRows Then
dt = New DataTable
dt.Load(dr)
dgvemplist.DataSource = dt
Else
dgvemplist.DataSource = Nothing
End If
End With
dr.Close()
If dgvemplist.RowCount > 0 Then
FormatDgvEmployeeList()
Else
FormatDgvEmployeeList()
MessageBox.Show("ไม่พบข้อมูลที่คุณค้นหา", "ตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
Showdata()
End If
Txtenpfind.Clear()
dr.Close()
Conn.Close()
End Sub
End Class
Load balance : Server 03
