119
GroupEmp.Visible = False
GroupTech.Visible = False
GroupNoWork.Visible = True
GroupNoWork.Label = "Пользователя нет в системе"
End If
End If
SqlReader.Close()
objConn.Close()
End If
End Sub
Private Sub BtnTechManageDirectorys_Click(sender As System.Object, e As
Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles
BtnTechManageDirectorys.Click
Dim FormMgDic As New FormManageDictionarys
ConnectToBD()
If connectstatus = True Then
FormMgDic.Show()
End If
DisconnectFromBD()
End Sub
End Class
'FormManageDictionarys
Imports System
Imports System.Data
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.IO
120
Imports System.Threading
Imports System.Diagnostics
Imports System.Data.Common
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class FormManageDictionarys
Public Sub New()
' This call is required by the designer.
InitializeComponent()
DepartmentLoad()
RoomLoad()
PositionLoad()
EmployeeLoad()
SpecializationLoad()
TechnicianLoad()
' Add any initialization after the InitializeComponent() call.
End Sub
' Вкладка Отделы
Private Sub AddEditDepartment()
If CBDepartmentEdit.Checked Then
BtnDepartmentAdd.Visible = False
BtnDepartmentEdit.Left = BtnDepartmentAdd.Left
BtnDepartmentEdit.Top = BtnDepartmentAdd.Top
BtnDepartmentEdit.Visible = True
DGVDepartment.Enabled = False
BtnDepDelete.Enabled = False
TBDepartmentAddEdit.Text =
DGVDepartment.CurrentRow.DataBoundItem(1).ToString
Else
121
BtnDepartmentEdit.Visible = False
BtnDepartmentAdd.Visible = True
DGVDepartment.Enabled = True
BtnDepDelete.Enabled = True
TBDepartmentAddEdit.Clear()
End If
End Sub
Private Sub DepartmentLoad()
AddEditDepartment()
Dim usertable As DataTable
Dim sqlQuery As String
Dim totalRecords As Integer
Dim connection As New SqlConnection(sConnectionString)
'start
Try
'usertable.Clear()
DGVDepartment.DataSource = ""
'DGVDepartment.Rows.Clear()
DGVDepartment.Refresh()
sqlQuery = "SELECT Department_ID, Department_Name FROM Department"
'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)
122
'totalRecords = tempDataSet.Tables[0].Rows.Count
totalRecords = tempDataSet.Tables(0).Rows.Count
tempDataSet.Clear()
tempDataSet.Dispose()
adapter.Fill(ds, 0, 1, "Department")
usertable = ds.Tables("Department")
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
DGVDepartment.Columns.Add(column)
Next
DGVDepartment.Columns.Item(0).Visible = False
DGVDepartment.Columns.Item(1).Width = DGVDepartment.Width
DGVDepartment.Columns.Item(1).HeaderText = "Название отдела"
'CreateTempTable(0, int.Parse(cmbNoOfRecords.Text.Trim()));
usertable.Rows.Clear()
adapter.Fill(ds, 0, totalRecords, "Department")
usertable = ds.Tables("Department")
DGVDepartment.DataSource = usertable.DefaultView
DGVDepartment.AllowUserToResizeColumns = True
'end createtemp
Catch ex As Exception
MsgBox(ex.ToString())
End Try
connection.Close()
'end
123
End Sub
Private Sub BtnDepartmentAdd_Click(sender As System.Object, e As
System.EventArgs) Handles BtnDepartmentAdd.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Department_Name from Department where Department_Name='"
& TBDepartmentAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
MsgBox("Ошибка добавления записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "insert into Department (Department_Name) values ('" &
TBDepartmentAddEdit.Text & "')"
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись добавлена")
DisconnectFromBD()
DepartmentLoad()
End If
DisconnectFromBD()
Catch
MsgBox("Ошибка добавления записи")
End Try
124
End If
DisconnectFromBD()
End Sub
Private Sub BtnDepartmentEdit_Click(sender As System.Object, e As
System.EventArgs) Handles BtnDepartmentEdit.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Department_Name from Department where Department_Name='"
& TBDepartmentAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
MsgBox("Ошибка изменения записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "UPDATE [NBHelpDesk].[dbo].[Department] SET
[Department_Name] = '" & TBDepartmentAddEdit.Text & "'" & " WHERE
[Department_ID] = " & DGVDepartment.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись изменена")
DisconnectFromBD()
DepartmentLoad()
CBDepartmentEdit.Checked = False
125
AddEditDepartment()
End If
DisconnectFromBD()
Catch
MsgBox("Ошибка редактирования записи")
End Try
End If
DisconnectFromBD()
End Sub
Private Sub CBDepartmentEdit_CheckedChanged(sender As System.Object, e As
System.EventArgs) Handles CBDepartmentEdit.CheckedChanged
AddEditDepartment()
End Sub
Private Sub BtnDepDelete_Click(sender As System.Object, e As System.EventArgs)
Handles BtnDepDelete.Click
Dim clkyes As Single
clkyes = MessageBox.Show("Вы действительно хотите удалить запись?",
"Удаление записи", MessageBoxButtons.YesNo)
If clkyes = vbYes Then
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim sqlcmd As New SqlCommand
strsql = "Delete from Department where Department_ID=" &
DGVDepartment.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись удалена")
126
DisconnectFromBD()
DepartmentLoad()
Catch
MsgBox("Ошибка удаления записи")
End Try
End If
DisconnectFromBD()
End If
End Sub
' end вкладка отделы
' вкладка помещения
Private Sub AddEditRoom()
If CBRoomEdit.Checked Then
BtnRoomAdd.Visible = False
BtnRoomEdit.Left = BtnRoomAdd.Left
BtnRoomEdit.Top = BtnRoomAdd.Top
BtnRoomEdit.Visible = True
DGVRoom.Enabled = False
BtnRoomDelete.Enabled = False
TBRoomAddEdit.Text = DGVRoom.CurrentRow.DataBoundItem(1).ToString
Else
BtnRoomEdit.Visible = False
BtnRoomAdd.Visible = True
DGVRoom.Enabled = True
BtnRoomDelete.Enabled = True
TBRoomAddEdit.Clear()
End If
End Sub
Private Sub RoomLoad()
AddEditRoom()
127
Dim usertable As DataTable
Dim sqlQuery As String
Dim totalRecords As Integer
Dim connection As New SqlConnection(sConnectionString)
'start
Try
DGVRoom.DataSource = ""
DGVRoom.Refresh()
sqlQuery = "SELECT Room_ID, Room_Name FROM Room"
'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, "Room")
usertable = ds.Tables("Room")
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
128
column.ValueType = dc.DataType
DGVRoom.Columns.Add(column)
Next
DGVRoom.Columns.Item(0).Visible = False
DGVRoom.Columns.Item(1).Width = DGVDepartment.Width
DGVRoom.Columns.Item(1).HeaderText = "Название помещения"
usertable.Rows.Clear()
adapter.Fill(ds, 0, totalRecords, "Room")
usertable = ds.Tables("Room")
DGVRoom.DataSource = usertable.DefaultView
DGVRoom.AllowUserToResizeColumns = True
Catch ex As Exception
MsgBox(ex.ToString())
End Try
connection.Close()
'end
End Sub
Private Sub BtnRoomAdd_Click(sender As System.Object, e As System.EventArgs)
Handles BtnRoomAdd.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Room_Name from Room where Room_Name='" &
TBRoomAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()