149
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 Specialization where Specialization_ID=" &
DGVSpecialization.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись удалена")
DisconnectFromBD()
SpecializationLoad()
Catch
MsgBox("Ошибка удаления записи")
End Try
End If
DisconnectFromBD()
End If
End Sub
' end вкладка должности
' вкладка специалисты
Private Sub FormAddEditTechnicianLoadCB(TempForm As
FormAddEditTechnician)
TempForm.CBPosition.Items.Clear()
ConnectToBD()
If connectstatus = True Then
Dim strsql As String
150
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
End If
DisconnectFromBD()
End Sub
Private Sub TechnicianLoad()
Dim usertable As DataTable
Dim sqlQuery As String
Dim totalRecords As Integer
Dim connection As New SqlConnection(sConnectionString)
'start
Try
DGVTechnician.DataSource = ""
DGVTechnician.Refresh()
sqlQuery = "SELECT [Technician_ID]" & _
",[Technician_FirstName]" & _
",[Technician_SecondName]" & _
",[Technician_LastName]" & _
151
",[Technician_Position_FK]" & _
",[Position_ID]" & _
",[Technician_SecondName] + ' ' +[Technician_FirstName] + ' ' +
[Technician_LastName] AS NAME" & _
",[Technician_JobEMail]" & _
",[Technician_PrivateEmail]" & _
",[Technician_Phone]" & _
",[Technician_Mobile]" & _
",[Position_Name]" & _
",[Technician_Comment]" & _
",[Technician_Holiday]" & _
" FROM Technician" & _
" Left Join Position on Technician_Position_FK=Position_ID"
'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, "Technician")
usertable = ds.Tables("Technician")
For Each dc In usertable.Columns
Dim column As New DataGridViewTextBoxColumn
152
column.DataPropertyName = dc.ColumnName
column.HeaderText = dc.ColumnName
column.Name = dc.ColumnName
column.SortMode = DataGridViewColumnSortMode.Automatic
column.ValueType = dc.DataType
DGVTechnician.Columns.Add(column)
Next
DGVTechnician.Columns.Item(0).Visible = False
DGVTechnician.Columns.Item(1).Visible = False
DGVTechnician.Columns.Item(2).Visible = False
DGVTechnician.Columns.Item(3).Visible = False
DGVTechnician.Columns.Item(4).Visible = False
DGVTechnician.Columns.Item(5).Visible = False
DGVTechnician.Columns.Item(6).Width = 200
DGVTechnician.Columns.Item(6).HeaderText = "ФИО"
DGVTechnician.Columns.Item(7).Width = 150
DGVTechnician.Columns.Item(7).HeaderText = "Корпоративная почта"
DGVTechnician.Columns.Item(8).Width = 150
DGVTechnician.Columns.Item(8).HeaderText = "Личная почта"
DGVTechnician.Columns.Item(9).Width = 70
DGVTechnician.Columns.Item(9).HeaderText = "Телефон"
DGVTechnician.Columns.Item(10).Width = 80
DGVTechnician.Columns.Item(10).HeaderText = "Мобильный"
DGVTechnician.Columns.Item(11).Width = 150
DGVTechnician.Columns.Item(11).HeaderText = "Должность"
DGVTechnician.Columns.Item(12).Width = 150
DGVTechnician.Columns.Item(12).HeaderText = "Комментарий"
DGVTechnician.Columns.Item(13).Width = 80
DGVTechnician.Columns.Item(13).HeaderText = "Выходной"
usertable.Rows.Clear()
153
adapter.Fill(ds, 0, totalRecords, "Technician")
usertable = ds.Tables("Technician")
DGVTechnician.DataSource = usertable.DefaultView
DGVTechnician.AllowUserToResizeColumns = True
Catch ex As Exception
MsgBox(ex.ToString())
End Try
connection.Close()
'end
End Sub
Private Sub BtnTechnicianDelete_Click_1(sender As System.Object, e As
System.EventArgs) Handles BtnTechnicianDelete.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 Technician where Technician_ID=" &
DGVTechnician.CurrentRow.DataBoundItem(0).ToString
sqlcmd.CommandText = strsql
Try
sqlcmd.Connection = objConn
sqlcmd.ExecuteNonQuery()
MsgBox("Запись удалена")
DisconnectFromBD()
TechnicianLoad()
Catch
MsgBox("Ошибка удаления записи")
154
End Try
End If
DisconnectFromBD()
End If
End Sub
Private Sub BtnTechnicianEdit_Click(sender As System.Object, e As
System.EventArgs) Handles BtnTechnicianEdit.Click
Dim FormAddEditTech As New FormAddEditTechnician
FormAddEditTechnicianLoadCB(FormAddEditTech)
FormAddEditTech.BtnTechnicianAdd.Visible = False
FormAddEditTech.BtnTechnicianEdit.Location =
FormAddEditTech.BtnTechnicianAdd.Location
FormAddEditTech.BtnTechnicianEdit.Visible = True
FormAddEditTech.Text = "Редактирование записи"
FormAddEditTech.CBHoliday.Visible = True
IdTemp = DGVTechnician.CurrentRow.DataBoundItem(0).ToString
FormAddEditTech.TBFirstName.Text =
DGVTechnician.CurrentRow.DataBoundItem(1).ToString
FormAddEditTech.TBSecondName.Text =
DGVTechnician.CurrentRow.DataBoundItem(2).ToString
FormAddEditTech.TBLastName.Text =
DGVTechnician.CurrentRow.DataBoundItem(3).ToString
FormAddEditTech.TBJobEmail.Text =
DGVTechnician.CurrentRow.DataBoundItem(7).ToString
FormAddEditTech.TBPrivateEmail.Text =
DGVTechnician.CurrentRow.DataBoundItem(8).ToString
FormAddEditTech.TBPhone.Text =
DGVTechnician.CurrentRow.DataBoundItem(9).ToString
FormAddEditTech.TBMobile.Text =
DGVTechnician.CurrentRow.DataBoundItem(10).ToString
FormAddEditTech.CBPosition.Text =
DGVTechnician.CurrentRow.DataBoundItem(11).ToString
FormAddEditTech.RTBComment.Text =
DGVTechnician.CurrentRow.DataBoundItem(12).ToString
155
If (StrComp(DGVTechnician.CurrentRow.DataBoundItem(13).ToString, "False") =
0) Then
FormAddEditTech.CBHoliday.Checked = False
Else
FormAddEditTech.CBHoliday.Checked = True
End If
FormAddEditTech.ShowDialog()
TechnicianLoad()
End Sub
Private Sub BtnTechnicianAdd_Click_1(sender As System.Object, e As
System.EventArgs) Handles BtnTechnicianAdd.Click
Dim FormAddEditTech As New FormAddEditTechnician
FormAddEditTech.BtnTechnicianAdd.Visible = True
FormAddEditTech.BtnTechnicianEdit.Visible = False
FormAddEditTechnicianLoadCB(FormAddEditTech)
FormAddEditTech.CBHoliday.Visible = False
FormAddEditTech.Text = "Добавление записи"
FormAddEditTech.ShowDialog()
TechnicianLoad()
End Sub
' end вкладка сотрудники
End Class