129
If SqlReader.HasRows Then
MsgBox("Ошибка добавления записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "insert into Room (Room_Name) values ('" &
TBRoomAddEdit.Text & "')"
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись добавлена")
DisconnectFromBD()
RoomLoad()
End If
Catch
MsgBox("Ошибка добавления записи")
End Try
End If
DisconnectFromBD()
End Sub
Private Sub BtnRoomEdit_Click(sender As System.Object, e As System.EventArgs)
Handles BtnRoomEdit.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
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
130
If SqlReader.HasRows Then
MsgBox("Ошибка изменения записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "UPDATE [NBHelpDesk].[dbo].[Room] SET [Room_Name] = '" &
TBRoomAddEdit.Text & "'" & " WHERE [Room_ID] = " &
DGVRoom.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись изменена")
DisconnectFromBD()
RoomLoad()
CBRoomEdit.Checked = False
AddEditRoom()
End If
Catch
MsgBox("Ошибка редактирования записи")
End Try
End If
DisconnectFromBD()
End Sub
Private Sub CBRoomEdit_CheckedChanged(sender As System.Object, e As
System.EventArgs) Handles CBRoomEdit.CheckedChanged
AddEditRoom()
End Sub
Private Sub BtnRoomDelete_Click(sender As System.Object, e As System.EventArgs)
Handles BtnRoomDelete.Click
Dim clkyes As Single
clkyes = MessageBox.Show("Вы действительно хотите удалить запись?",
"Удаление записи", MessageBoxButtons.YesNo)
If clkyes = vbYes Then
131
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim sqlcmd As New SqlCommand
strsql = "Delete from Room where Room_ID=" &
DGVRoom.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись удалена")
DisconnectFromBD()
RoomLoad()
Catch
MsgBox("Ошибка удаления записи")
End Try
End If
DisconnectFromBD()
End If
End Sub
' end вкладка помещения
' вкладка должности
Private Sub AddEditPosition()
If CBPositionEdit.Checked Then
BtnPositionAdd.Visible = False
BtnPositionEdit.Left = BtnPositionAdd.Left
BtnPositionEdit.Top = BtnPositionAdd.Top
BtnPositionEdit.Visible = True
DGVPosition.Enabled = False
BtnPositionDelete.Enabled = False
132
TBPositionAddEdit.Text =
DGVPosition.CurrentRow.DataBoundItem(1).ToString
Else
BtnPositionEdit.Visible = False
BtnPositionAdd.Visible = True
DGVPosition.Enabled = True
BtnPositionDelete.Enabled = True
TBPositionAddEdit.Clear()
End If
End Sub
Private Sub PositionLoad()
AddEditPosition()
Dim usertable As DataTable
Dim sqlQuery As String
Dim totalRecords As Integer
Dim connection As New SqlConnection(sConnectionString)
'start
Try
DGVPosition.DataSource = ""
DGVPosition.Refresh()
sqlQuery = "SELECT Position_ID, Position_Name FROM Position"
'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()
133
adapter.Fill(tempDataSet)
totalRecords = tempDataSet.Tables(0).Rows.Count
tempDataSet.Clear()
tempDataSet.Dispose()
adapter.Fill(ds, 0, 1, "Position")
usertable = ds.Tables("Position")
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
DGVPosition.Columns.Add(column)
Next
DGVPosition.Columns.Item(0).Visible = False
DGVPosition.Columns.Item(1).Width = DGVDepartment.Width
DGVPosition.Columns.Item(1).HeaderText = "Название должности"
usertable.Rows.Clear()
adapter.Fill(ds, 0, totalRecords, "Room")
usertable = ds.Tables("Room")
DGVPosition.DataSource = usertable.DefaultView
DGVPosition.AllowUserToResizeColumns = True
Catch ex As Exception
MsgBox(ex.ToString())
End Try
connection.Close()
'end
End Sub
134
Private Sub BtnPositionAdd_Click(sender As System.Object, e As System.EventArgs)
Handles BtnPositionAdd.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Position_Name from Position where Position_Name='" &
TBPositionAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
MsgBox("Ошибка добавления записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "insert into Position (Position_Name) values ('" &
TBPositionAddEdit.Text & "')"
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись добавлена")
DisconnectFromBD()
PositionLoad()
End If
DisconnectFromBD()
Catch
MsgBox("Ошибка добавления записи")
End Try
End If
135
End Sub
Private Sub BtnPositionEdit_Click(sender As System.Object, e As System.EventArgs)
Handles BtnPositionEdit.Click
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Position_Name from Position where Position_Name='" &
TBPositionAddEdit.Text & "'"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
MsgBox("Ошибка изменения записи: Такая запись уже есть")
Else
SqlReader.Close()
strsql = "UPDATE [NBHelpDesk].[dbo].[Position] SET [Position_Name] =
'" & TBPositionAddEdit.Text & "'" & " WHERE [Position_ID] = " &
DGVPosition.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись изменена")
DisconnectFromBD()
PositionLoad()
CBPositionEdit.Checked = False
AddEditPosition()
End If
DisconnectFromBD()
136
Catch
MsgBox("Ошибка редактирования записи")
End Try
End If
DisconnectFromBD()
End Sub
Private Sub CBPositionEdit_CheckedChanged(sender As System.Object, e As
System.EventArgs) Handles CBPositionEdit.CheckedChanged
AddEditPosition()
End Sub
Private Sub BtnPositionDelete_Click(sender As System.Object, e As
System.EventArgs) Handles BtnPositionDelete.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 Position where Position_ID=" &
DGVPosition.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись удалена")
DisconnectFromBD()
PositionLoad()
Catch
MsgBox("Ошибка удаления записи")
137
End Try
End If
DisconnectFromBD()
End If
End Sub
' end вкладка должности
' вкладка сотрудники
Private Sub FormAddEditEmployeeLoadCB(TempForm As FormAddEditEmployee)
TempForm.CBPosition.Items.Clear()
TempForm.CBRoom.Items.Clear()
TempForm.CBDepartment.Items.Clear()
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
strsql = "select Position_Name from Position"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
While SqlReader.Read
TempForm.CBPosition.Items.Add(SqlReader.Item(0).ToString)
End While
SqlReader.Close()
Catch
MsgBox("Ошибка чтения для CBPos")
End Try
strsql = "select Department_Name from Department"
138
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
While SqlReader.Read
TempForm.CBDepartment.Items.Add(SqlReader.Item(0).ToString)
End While
SqlReader.Close()
Catch
MsgBox("Ошибка чтения для CBDep")
End Try
strsql = "select Room_Name from Room"
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
While SqlReader.Read
TempForm.CBRoom.Items.Add(SqlReader.Item(0).ToString)
End While
SqlReader.Close()
Catch
MsgBox("Ошибка чтения для CBRoom")
End Try
End If
DisconnectFromBD()
End Sub
Private Sub EmployeeLoad()
Dim usertable As DataTable
Dim sqlQuery As String
Dim totalRecords As Integer