139
Dim connection As New SqlConnection(sConnectionString)
'start
Try
DGVEmployee.DataSource = ""
DGVEmployee.Refresh()
sqlQuery = "SELECT [Employee_ID]" & _
",[Employee_FirstName]" & _
",[Employee_SecondName]" & _
",[Employee_LastName]" & _
",[Employee_Department_FK]" & _
",[Employee_Room_FK]" & _
",[Employee_Position_FK]" & _
",[Position_ID]" & _
",[Department_ID]" & _
",[Room_ID]" & _
",[Employee_SecondName] + ' ' +[Employee_FirstName] + ' ' +
[Employee_LastName] AS NAME" & _
",[Employee_Login]" & _
",[Employee_Email]" & _
",[Employee_Phone]" & _
",[Employee_Mobile]" & _
",[Position_Name]" & _
",[Department_Name]" & _
",[Room_Name]" & _
",[Employee_Comment]" & _
" FROM Employee" & _
" Left Join Department on Employee_Department_FK=Department_ID" & _
" Left Join Room on Employee_Room_FK=Room_ID" & _
" Left Join Position on Employee_Position_FK=Position_ID"
'setdata object
140
Dim command As New SqlCommand(sqlQuery, connection)
Dim adapter As New SqlDataAdapter(command)
Dim builder As New SqlCommandBuilder(adapter)
Dim ds As New DataSet("MainDataSet")
Dim tempDataSet As New DataSet("TempDataSet")
Dim dc As New DataColumn
'end set
connection.Open()
adapter.Fill(tempDataSet)
totalRecords = tempDataSet.Tables(0).Rows.Count
tempDataSet.Clear()
tempDataSet.Dispose()
adapter.Fill(ds, 0, 1, "Employee")
usertable = ds.Tables("Employee")
For Each dc In usertable.Columns
Dim column As New DataGridViewTextBoxColumn
column.DataPropertyName = dc.ColumnName
column.HeaderText = dc.ColumnName
column.Name = dc.ColumnName
column.SortMode = DataGridViewColumnSortMode.Automatic
column.ValueType = dc.DataType
DGVEmployee.Columns.Add(column)
Next
DGVEmployee.Columns.Item(0).Visible = False
DGVEmployee.Columns.Item(1).Visible = False
DGVEmployee.Columns.Item(2).Visible = False
DGVEmployee.Columns.Item(3).Visible = False
DGVEmployee.Columns.Item(4).Visible = False
DGVEmployee.Columns.Item(5).Visible = False
DGVEmployee.Columns.Item(6).Visible = False
141
DGVEmployee.Columns.Item(7).Visible = False
DGVEmployee.Columns.Item(8).Visible = False
DGVEmployee.Columns.Item(9).Visible = False
DGVEmployee.Columns.Item(10).Width = 200
DGVEmployee.Columns.Item(10).HeaderText = "ФИО"
DGVEmployee.Columns.Item(11).Width = 80
DGVEmployee.Columns.Item(11).HeaderText = "Логин"
DGVEmployee.Columns.Item(12).Width = 150
DGVEmployee.Columns.Item(12).HeaderText = "E-mail"
DGVEmployee.Columns.Item(13).Width = 70
DGVEmployee.Columns.Item(13).HeaderText = "Телефон"
DGVEmployee.Columns.Item(14).Width = 80
DGVEmployee.Columns.Item(14).HeaderText = "Мобильный"
DGVEmployee.Columns.Item(15).Width = 150
DGVEmployee.Columns.Item(15).HeaderText = "Должность"
DGVEmployee.Columns.Item(16).Width = 150
DGVEmployee.Columns.Item(16).HeaderText = "Отдел"
DGVEmployee.Columns.Item(17).Width = 150
DGVEmployee.Columns.Item(17).HeaderText = "Помещение"
DGVEmployee.Columns.Item(18).Width = 150
DGVEmployee.Columns.Item(18).HeaderText = "Комментарий"
usertable.Rows.Clear()
adapter.Fill(ds, 0, totalRecords, "Employee")
usertable = ds.Tables("Employee")
DGVEmployee.DataSource = usertable.DefaultView
DGVEmployee.AllowUserToResizeColumns = True
Catch ex As Exception
MsgBox(ex.ToString())
End Try
connection.Close()
142
'end
End Sub
Private Sub BtnEmployeeAdd_Click(sender As System.Object, e As
System.EventArgs) Handles BtnEmployeeAdd.Click
Dim FormAddEditEmp As New FormAddEditEmployee
FormAddEditEmp.BtnEmployeeAdd.Visible = True
FormAddEditEmp.BtnEmployeeEdit.Visible = False
FormAddEditEmployeeLoadCB(FormAddEditEmp)
FormAddEditEmp.Text = "Добавление записи"
FormAddEditEmp.ShowDialog()
EmployeeLoad()
End Sub
Private Sub BtnEmployeeDelete_Click(sender As System.Object, e As
System.EventArgs) Handles BtnEmployeeDelete.Click
Dim clkyes As Single
clkyes = MessageBox.Show("Вы действительно хотите удалить запись?",
"Удаление записи", MessageBoxButtons.YesNo)
If clkyes = vbYes Then
ConnectToBD()
If connectstatus Then
Dim strsql As String
Dim sqlcmd As New SqlCommand
strsql = "Delete from Employee where Employee_ID=" &
DGVEmployee.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись удалена")
DisconnectFromBD()
EmployeeLoad()
143
Catch
MsgBox("Ошибка удаления записи")
End Try
End If
DisconnectFromBD()
End If
End Sub
Private Sub BtnEditEmployee_Click(sender As System.Object, e As
System.EventArgs) Handles BtnEditEmployee.Click
Dim FormAddEditEmp As New FormAddEditEmployee
FormAddEditEmployeeLoadCB(FormAddEditEmp)
FormAddEditEmp.BtnEmployeeAdd.Visible = False
FormAddEditEmp.BtnEmployeeEdit.Location =
FormAddEditEmp.BtnEmployeeAdd.Location
FormAddEditEmp.BtnEmployeeEdit.Visible = True
FormAddEditEmp.Text = "Редактирование записи"
IdTemp = DGVEmployee.CurrentRow.DataBoundItem(0).ToString
FormAddEditEmp.TBFirstName.Text =
DGVEmployee.CurrentRow.DataBoundItem(1).ToString
FormAddEditEmp.TBSecondName.Text =
DGVEmployee.CurrentRow.DataBoundItem(2).ToString
FormAddEditEmp.TBLastName.Text =
DGVEmployee.CurrentRow.DataBoundItem(3).ToString
FormAddEditEmp.TBLogin.Text =
DGVEmployee.CurrentRow.DataBoundItem(11).ToString
FormAddEditEmp.TBEmail.Text =
DGVEmployee.CurrentRow.DataBoundItem(12).ToString
FormAddEditEmp.TBPhone.Text =
DGVEmployee.CurrentRow.DataBoundItem(13).ToString
FormAddEditEmp.TBMobile.Text =
DGVEmployee.CurrentRow.DataBoundItem(14).ToString
FormAddEditEmp.CBPosition.Text =
DGVEmployee.CurrentRow.DataBoundItem(15).ToString
144
FormAddEditEmp.CBDepartment.Text =
DGVEmployee.CurrentRow.DataBoundItem(16).ToString
FormAddEditEmp.CBRoom.Text =
DGVEmployee.CurrentRow.DataBoundItem(17).ToString
FormAddEditEmp.RTBComment.Text =
DGVEmployee.CurrentRow.DataBoundItem(18).ToString
FormAddEditEmp.ShowDialog()
EmployeeLoad()
End Sub
' end вкладка сотрудники
' вкладка специализации
Private Sub AddEditSpecialization()
If CBSpecializationEdit.Checked Then
BtnSpecializationAdd.Visible = False
BtnSpecializationEdit.Left = BtnSpecializationAdd.Left
BtnSpecializationEdit.Top = BtnSpecializationAdd.Top
BtnSpecializationEdit.Visible = True
DGVSpecialization.Enabled = False
BtnSpecializationDelete.Enabled = False
TBSpecializationAddEdit.Text =
DGVSpecialization.CurrentRow.DataBoundItem(1).ToString
Else
BtnSpecializationEdit.Visible = False
BtnSpecializationAdd.Visible = True
DGVSpecialization.Enabled = True
BtnSpecializationDelete.Enabled = True
TBSpecializationAddEdit.Clear()
End If
End Sub
Private Sub SpecializationLoad()
AddEditSpecialization()
145
Dim usertable As DataTable
Dim sqlQuery As String
Dim totalRecords As Integer
Dim connection As New SqlConnection(sConnectionString)
'start
Try
DGVSpecialization.DataSource = ""
DGVSpecialization.Refresh()
sqlQuery = "SELECT Specialization_ID, Specialization_Name FROM
Specialization"
'setdata object
Dim command As New SqlCommand(sqlQuery, connection)
Dim adapter As New SqlDataAdapter(command)
Dim builder As New SqlCommandBuilder(adapter)
Dim ds As New DataSet("MainDataSet")
Dim tempDataSet As New DataSet("TempDataSet")
Dim dc As New DataColumn
'end set
connection.Open()
adapter.Fill(tempDataSet)
totalRecords = tempDataSet.Tables(0).Rows.Count
tempDataSet.Clear()
tempDataSet.Dispose()
adapter.Fill(ds, 0, 1, "Specialization")
usertable = ds.Tables("Specialization")
For Each dc In usertable.Columns
Dim column As New DataGridViewTextBoxColumn
column.DataPropertyName = dc.ColumnName
column.HeaderText = dc.ColumnName
column.Name = dc.ColumnName
146
column.SortMode = DataGridViewColumnSortMode.Automatic
column.ValueType = dc.DataType
DGVSpecialization.Columns.Add(column)
Next
DGVSpecialization.Columns.Item(0).Visible = False
DGVSpecialization.Columns.Item(1).Width = DGVDepartment.Width
DGVSpecialization.Columns.Item(1).HeaderText = "Название специализации"
usertable.Rows.Clear()
adapter.Fill(ds, 0, totalRecords, "Specialization")
usertable = ds.Tables("Specialization")
DGVSpecialization.DataSource = usertable.DefaultView
DGVSpecialization.AllowUserToResizeColumns = True
Catch ex As Exception
MsgBox(ex.ToString())
End Try
connection.Close()
'end
End Sub
Private Sub BtnSpecializationAdd_Click(sender As System.Object, e As
System.EventArgs) Handles BtnSpecializationAdd.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Specialization_Name from Specialization where
Specialization_Name='" & TBSpecializationAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
147
If SqlReader.HasRows Then
MsgBox("Ошибка добавления записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "insert into Specialization (Specialization_Name) values ('" &
TBSpecializationAddEdit.Text & "')"
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись добавлена")
DisconnectFromBD()
SpecializationLoad()
End If
DisconnectFromBD()
Catch
MsgBox("Ошибка добавления записи")
End Try
End If
End Sub
Private Sub BtnSpecializationEdit_Click(sender As System.Object, e As
System.EventArgs) Handles BtnSpecializationEdit.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Specialization_Name from Specialization where
Specialization_Name='" & TBSpecializationAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
148
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
MsgBox("Ошибка изменения записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "UPDATE [NBHelpDesk].[dbo].[Specialization] SET
[Specialization_Name] = '" & TBSpecializationAddEdit.Text & "'" & " WHERE
[Specialization_ID] = " & DGVSpecialization.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись изменена")
DisconnectFromBD()
SpecializationLoad()
CBSpecializationEdit.Checked = False
AddEditSpecialization()
End If
DisconnectFromBD()
Catch
MsgBox("Ошибка редактирования записи")
End Try
End If
DisconnectFromBD()
End Sub
Private Sub CBSpecializationEdit_CheckedChanged(sender As System.Object, e As
System.EventArgs) Handles CBSpecializationEdit.CheckedChanged
AddEditSpecialization()
End Sub
Private Sub BtnSpecializationDelete_Click(sender As System.Object, e As
System.EventArgs) Handles BtnSpecializationDelete.Click
Dim clkyes As Single