#Region "树形菜单 和 DataGridView1 操作"
'修改标题
Private Sub chineseTitle()
'Grid使用的標題
Dim columnNameGrid() As String = {"员工编号", "员工名称", "性别", "身份证号码", "出生日期", "年龄", "部门编号", "部门名称", "上级部门"}
'將欄位名稱改成中文
For i As Integer = 1 To columnNameGrid.Length
Me.DataGridView1.Columns(i - 1).HeaderText = columnNameGrid(i - 1)
Next i
End Sub
'點選行資料,塞入輸入區
Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
Try
'将值塞入输入区
If DataGridView1.CurrentCellAddress.Y >= 0 Then
Me.txtEmpID.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("EmpID").Value.ToString.Trim
Me.txtEmpName.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("EmpName").Value.ToString.Trim
Dim sex As String
'性别处理
sex = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("EmpSex").Value.ToString.Trim
If sex = "1" Then
Me.cmbEmpSex.Text = "男"
Else
Me.cmbEmpSex.Text = "女"
End If
Me.txtEmpPID.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("EmpPID").Value.ToString.Trim
Me.dtpEmpBirthday.Value = (DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("EmpBirthday").Value.ToString.Trim)
Me.txtEmpAge.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("EmpAge").Value.ToString.Trim
Me.cmbDepID.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("DepID").Value.ToString.Trim
Me.cmbDepName.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("DepName").Value.ToString.Trim
Me.cmbPDepID.Text = DataGridView1.Rows(DataGridView1.CurrentCellAddress.Y).Cells("PDepID").Value.ToString.Trim
End If
Catch ex As Exception
'Throw ex
End Try
End Sub
'执行查询
Private Sub GetEmployee()
'校驗密碼和用戶名的SQL語句
Try
Dim strSql As String = "SELECT * FROM VWEmployees WHERE 1=1 " & GetWhere() & " order by EmpID"
Dim ds As DataSet = GetDataSet(strSql)
If ds.Tables.Item(0).Rows.Count = 0 Then
MessageBox.Show("资料不存在")
Else
'显示数据
Me.DataGridView1.DataSource = ds.Tables.Item(0).DefaultView
'中文标题
chineseTitle()
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
'where查询条件
Private Function GetWhere() As String
Dim strWhere As String = ""
If Me.txtEmpID.Text.Trim.Length > 0 Then
strWhere = strWhere & " AND EmpID ='" + Me.txtEmpID.Text.Trim + "' "
ElseIf Me.txtEmpName.Text.Trim.Length > 0 Then
strWhere = strWhere & " AND EmpName LIKE '%" + Me.txtEmpName.Text.Trim + "%' "
ElseIf Me.cmbEmpSex.Text.Trim.Length > 0 Then
If cmbEmpSex.Text.Trim = "男" Then
strWhere = strWhere & " AND EmpSex LIKE '%" + "1" + "%' "
Else
strWhere = strWhere & " AND EmpSex LIKE '%" + "0" + "%' "
End If
ElseIf Me.txtEmpPID.Text.Trim.Length > 0 Then
strWhere = strWhere & " AND EmpPID LIKE '%" + Me.txtEmpPID.Text.Trim + "%'"
ElseIf Me.txtEmpAge.Text.Trim.Length > 0 Then
strWhere = strWhere & " AND EmpAge LIKE '%" + Me.txtEmpAge.Text.Trim + "%'"
ElseIf Me.cmbDepID.Text.Trim.Length > 0 Then
strWhere = strWhere & "AND DepID LIKE '%" + Me.cmbDepID.Text.Trim + "%'"
ElseIf Me.cmbDepName.Text.Trim.Length > 0 Then
If cmbDepName.Text.Trim = "事业一部" Then
strWhere = strWhere & "AND DepID LIKE '%" + "D01" + "%'"
ElseIf cmbDepName.Text.Trim = "事业二部" Then
strWhere = strWhere & "AND DepID LIKE '%" + "D02" + "%'"
Else
strWhere = strWhere & "AND DepID LIKE '%" + "D020" + "%'"
End If
ElseIf Me.cmbPDepID.Text.Trim.Length > 0 Then
strWhere = strWhere & "AND PDepID LIKE '%" + Me.cmbPDepID.Text.Trim + "%'"
End If
Return strWhere
End Function
'查询员工信息
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
GetEmployee()
End Sub
'新增员工信息
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim strSQL As String = ""
If txtEmpID.Text.Trim = "" Or cmbEmpSex.Text.Trim = "" Or txtEmpPID.Text.Trim = "" Or cmbDepID.Text.Trim = "" Then
MessageBox.Show("红色为必输项！")
Else
'生日处理
Dim Birthday As String = txtEmpPID.Text.Trim.ToString.Substring(6, 4) + "-" + txtEmpPID.Text.Trim.ToString.Substring(10, 2) + "-" + txtEmpPID.Text.Trim.ToString.Substring(12, 2)
'性别处理
Dim sex As String
If cmbEmpSex.Text.Trim = "男" Then
sex = "1"
Else
sex = "0"
End If
'年龄处理
Dim age As String
age = CInt(Now().ToString.Substring(0, 4) - txtEmpPID.Text.Trim.ToString.Substring(6, 4) + 1)
Try
strSQL = "INSERT INTO Employees(EmpID,EmpName,EmpSex,EmpPID,EmpBirthday,EmpAge,DepID)" & _
"VALUES('" & txtEmpID.Text.Trim & "','" & txtEmpName.Text.Trim & "','" & sex & "','" & txtEmpPID.Text.Trim & "','" & Birthday & "','" & age & "','" & cmbDepID.Text.Trim & "')"
If ExecuteSQL(strSQL) > 0 Then
btnUserClear_Click(sender, e)
MessageBox.Show("員工信息新增成功！")
End If
Catch ex As Exception
MessageBox.Show("員工信息新增失败！")
End Try
End If
End Sub
'修改员工信息
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
'判斷員工ID是否為空
If Me.txtEmpID.Text = "" Then
MessageBox.Show("請選擇需要修改的資料！")
Else
'生日处理
Dim Birthday As String = txtEmpPID.Text.Trim.ToString.Substring(6, 4) + "-" + txtEmpPID.Text.Trim.ToString.Substring(10, 2) + "-" + txtEmpPID.Text.Trim.ToString.Substring(12, 2)
'性别处理
Dim Sex As String
If cmbEmpSex.Text.Trim = "男" Then
Sex = "1"
Else
Sex = "0"
End If
'年龄处理
Dim Age As Integer
Age = CInt(Now().ToString.Substring(0, 4) - txtEmpPID.Text.Trim.ToString.Substring(6, 4) + 1)
'部门表资料同步处理
Dim strSQL As String = "update Employees" & " SET EmpName='" & txtEmpName.Text.Trim & "',EmpSex='" & Sex & "',EmpPID=" & txtEmpPID.Text.Trim & ",EmpBirthday='" & Birthday & "',EmpAge='" & Age & "',DepID='" & cmbDepID.Text.Trim & "'" & _
" where EmpID = '" & txtEmpID.Text.Trim & "'"
If ExecuteSQL(strSQL) > 0 Then
btnClear_Click(sender, e)
btnSelect_Click(sender, e)
MessageBox.Show("員工信息修改成功！")
End If
End If
End Sub
