Лабораторная работа №5 обработка нормализованной многотабличной структуры бд.
Цель работы:
1. Изучить работу с многотабличными базами данных в приложениях созданных на VB
2. Изучить приемы обработки БД с помощью технологии DAO.
Ход работы:
В качестве БД для разрабатываемого на лабораторной работе приложения возьмем за основу созданную на предыдущей лабораторной работе БД.
Откройте БД «Компьютерная школа» и изучите структуру таблицы Spisok. Обратите внимание, в том случае если некоторые студенты записались на изучение только одного или двух предметов, то образуются неиспользуемые поля. Проведем нормализацию созданной базы данных. Для этого создайте две дополнительных таблицы и отредактируйте таблицу Spisok в соответствие с указанными далее примерами.
Создайте связи между таблицами: между Spisok и Ocenki связь один-ко-многим, между Predmet и Ocenki связь один-ко-многим.
Заполните таблицы данными.
Обратите внимание, в таблице Ocenki теперь хранятся только наборы кодов, комбинация двух кодов однозначно определяет студента и изучаемую им дисциплину. Данной комбинации ставится в соответствие только одно поле – оценка.
Запустите Visual Basic, создайте новый проект.
Разместите на форме элементы, так как показано на рисунке.
Обратите внимание, что на форме нет объекта Data. В данной лабораторной работе мы рассмотрим альтернативный способ доступа к БД. Навигатор для перемещения по записям БД мы создадим с помощью объектов CommandButton и запрограммируем их работу самостоятельно.
Для размещенных на форме объектов задайте имена, например, txtFam, txtName, txtOtch, txtGrup. Т.к. объекты Label (надпись) Вы не будете использовать в программном коде имена им можно оставить без изменения. Для кнопок: Добавить (cmbAdd), Редактировать (mbEdit), Удалить (cmbDel), Сохранить (cmbSave), Отмена (cmbCancel), << (cmbFirst), < (cmbPrew), > (cmbNext), >> (cmbLast).
Для работы с БД в данном приложении воспользуемся технологией DAO. Создайте следующий программный код:
Public dbsData As Database
Public rstSpisok As Recordset
Private Sub Form_Load()
Set dbsData = OpenDatabase("dbLaba.mdb")
Set rstSpisok = dbsData.OpenRecordset("Spisok", dbOpenDynaset)
End Sub
В инструкции OpenDatabase может потребоваться указать полный путь к открываемой БД, по умолчанию указанный файл открывается из текущей директории. В инструкции OpenRecordset указано имя открываемой таблицы, здесь можно разместить и SQL-запрос. Результатом выполнения данной инструкции, в любом случае, будет являться формирование некоторого набора записей в памяти компьютера и установление связи с ним через переменную rstSpisok.
Для отображения данных на форме создадим процедуру пользователя, которую будем вызывать каждый раз, когда необходимо будет обновить содержание полей формы.
Public Sub FormRefresh()
If rstSpisok.EOF Or rstSpisok.BOF Then
txtFam.Text = ""
txtName.Text = ""
txtOtch.Text = ""
txtGrup.Text = ""
Exit Sub
End If
txtFam.Text = rstSpisok.Fields("Familie").Value
txtName.Text = rstSpisok.Fields("Imja").Value
txtOtch.Text = rstSpisok.Fields("Otch").Value
txtGrup.Text = rstSpisok.Fields("Grup").Value
End Sub
Теперь запрограммируем работу навигатора. Создайте следующий программный код.
Private Sub cmbFirst_Click()
rstSpisok.MoveFirst
FormRefresh
End Sub
Private Sub cmbLast_Click()
rstSpisok.MoveLast
FormRefresh
End Sub
Private Sub cmbNext_Click()
rstSpisok.MoveNext
If rstSpisok.EOF Then rstSpisok.MovePrevious
FormRefresh
End Sub
Private Sub cmbPrew_Click()
rstSpisok.MovePrevious
If rstSpisok.BOF Then rstSpisok.MoveNext
FormRefresh
End Sub
Кнопки будут отвечать за переход по записям таблицы. Чтобы при запуске формы отображалась первая по порядку запись, введите программный код.
Private Sub Form_Activate()
rstSpisok.MoveFirst
FormRefresh
End Sub
Сохраните проект. Запустите приложение и проверьте работу навигатора.
Теперь запрограммируем работу с записями таблицы. Реализуем на примере данной лабораторной работы функции защиты приложения от неправильных действий пользователя. Как правило, для этого приложение программируют так, чтобы пользователь в каждый конкретный момент работы программы мог нажимать кнопки и редактировать данные только доступные в данный момент. Измените предыдущую процедуру и добавьте новые.
Private Sub Form_Activate()
fl = False
rstSpisok.MoveFirst
FormRefresh
cmbAdd.Enabled = True
cmbEdit.Enabled = True
cmbDel.Enabled = True
cmbSave.Enabled = False
cmbCancel.Enabled = False
txtFam.Locked = True
txtName.Locked = True
txtOtch.Locked = True
txtGrup.Locked = True
End Sub
Private Sub cmbAdd_Click()
fl = True
cmbAdd.Enabled = False
cmbEdit.Enabled = False
cmbDel.Enabled = False
cmbSave.Enabled = True
cmbCancel.Enabled = True
txtFam.Text = ""
txtName.Text = ""
txtOtch.Text = ""
txtGrup.Text = ""
txtFam.Locked = False
txtName.Locked = False
txtOtch.Locked = False
txtGrup.Locked = False
End Sub
Private Sub cmbCancel_Click()
rstSpisok.MoveFirst
FormRefresh
cmbAdd.Enabled = True
cmbEdit.Enabled = True
cmbDel.Enabled = True
cmbSave.Enabled = False
cmbCancel.Enabled = False
txtFam.Locked = True
txtName.Locked = True
txtOtch.Locked = True
txtGrup.Locked = True
End Sub
Private Sub cmbSave_Click()
If txtFam = "" Then
MsgBox ("Укажите фамилию студента!!!")
txtFam.SetFocus
End If
If txtName = "" Then
MsgBox ("Укажите имя студента!!!")
txtName.SetFocus
End If
If txtOtch = "" Then
MsgBox ("Укажите отчество студента!!!")
txtOtch.SetFocus
End If
If txtGrup = "" Then
MsgBox ("Укажите группу студента!!!")
txtGrup.SetFocus
End If
If fl = True Then
rstSpisok.AddNew
Else
rstSpisok.Edit
End If
rstSpisok.Fields("Familie").Value = txtFam.Text
rstSpisok.Fields("Imja").Value = txtName.Text
rstSpisok.Fields("Otch").Value = txtOtch.Text
rstSpisok.Fields("Group").Value = txtGrup.Text
rstSpisok.Update
If fl = True Then rstSpisok.MoveLast
FormRefresh
cmbAdd.Enabled = True
cmbEdit.Enabled = True
cmbDel.Enabled = True
cmbSave.Enabled = False
cmbCancel.Enabled = False
txtFam.Locked = True
txtName.Locked = True
txtOtch.Locked = True
txtGrup.Locked = True
End Sub
Private Sub cmbEdit_Click()
fl = False
cmbAdd.Enabled = False
cmbEdit.Enabled = False
cmbDel.Enabled = False
cmbSave.Enabled = True
cmbCancel.Enabled = True
txtFam.Locked = False
txtName.Locked = False
txtOtch.Locked = False
txtGrup.Locked = False
End Sub
Private Sub cmbDel_Click()
If MsgBox("Действительно удалить текущую запись?", vbYesNo, "Внимание !") = vbYes Then
rstSpisok.Delete
rstSpisok.MoveFirst
FormRefresh
End If
End Sub
Переменную fl необходимо объявить, поместив следующую строку в самое начало программного кода (до всех процедур):
Dim fl As Boolean
Сохраните проект. Проверьте работу приложения. Обратите внимание, что при нажатии кнопки Удалить в тексте программы мы производим удаление только записи о студенте из таблицы Spisok, однако, благодаря тому, что в самой БД между таблицами установлены связи и включены настройки каскадного обновления и каскадного удаления, будут удаляться также данные об оценках удаляемого студента, т.е. все связанные с ним по коду записи.
На следующем этапе отредактируем созданное приложение так, чтобы одновременно с данными о студентах отображались их оценки. Разместите на форме объект ListBox (lstOcenki). Измените процедуру FormRefresh.
Public Sub FormRefresh()
Dim rstOcenki As Recordset
Dim strSQL As String
If rstSpisok.EOF Or rstSpisok.BOF Then
txtFam.Text = ""
txtName.Text = ""
txtOtch.Text = ""
txtGrup.Text = ""
lstOcenki.Clear
Exit Sub
End If
txtFam.Text = rstSpisok.Fields("Familie").Value
txtName.Text = rstSpisok.Fields("Imja").Value
txtOtch.Text = rstSpisok.Fields("Otch").Value
txtGrup.Text = rstSpisok.Fields("Group").Value
lstOcenki.Clear
strSQL = "SELECT Predmet.NamePredm, Ocenki.Ocenka " & _
"FROM Predmet INNER JOIN Ocenki ON Predmet.Cod = Ocenki.CodPredm " & _
"WHERE (((Ocenki.CodStud)=" + CStr(rstSpisok.Fields("Cod").Value) + "));"
Set rstOcenki = dbsData.OpenRecordset(strSQL, dbOpenDynaset)
rstOcenki.Requery
If rstOcenki.EOF And rstOcenki.BOF Then Exit Sub
rstOcenki.MoveLast
rstOcenki.MoveFirst
While Not rstOcenki.EOF
lstOcenki.AddItem rstOcenki.Fields("NamePredm").Value + " " + CStr(rstOcenki.Fields("Ocenka").Value)
rstOcenki.MoveNext
Wend
End Sub
Сохраните проект. Проверьте работу приложения.
Вызвав Tools – Menu Editor, сформируйте контекстное меню Вашего приложения: на основном уровне (размещайте стрелками) Поиск (mnuPoisk), Отчеты (mnuReport), Статистика (mnuStat) и Выход (mnuExit). Подуровни для Поиск: по фамилии (mnuPoiskFam) и по имени (mnuPoiskName), для Отчеты: список студентов (mnuRepSpisok).
Запрограммируем кнопки меню:
Private Sub mnuExit_Click()
End
End Sub
Private Sub mnuPoiskFam_Click()
Dim strFind As String
Dim v As Variant
strFind = InputBox("Введите фамилию для поиска:", "Поиск", "")
If strFind = "" Then Exit Sub
rstSpisok.FindFirst «Familie Like '» + strFind + «*'»
If rstSpisok.NoMatch Then
MsgBox ("Искомое значение не найдено!")
Exit Sub
End If
FormRefresh
v = MsgBox("Значение найдено, продолжить поиск с тем же критерием?", vbYesNo, "Внимание !")
While v = vbYes
rstSpisok.FindNext «Familie Like '» + strFind + «*'»
If rstSpisok.NoMatch Then
MsgBox ("Искомое значение не найдено!")
Exit Sub
End If
FormRefresh
v = MsgBox("Значение найдено, продолжить поиск с тем же критерием?", vbYesNo, "Внимание !")
Wend
End Sub
Private Sub mnuRepSpisok_Click()
frmPrint.Show
End Sub
Private Sub mnuStat_Click()
frmStat.Show
End Sub
Реализуйте самостоятельно поиск по имени студента. Для этого необходимо создать процедуру аналогичную процедуре с именем Sub mnuPoiskFam_Click. Новая процедура должна обрабатывать событие выбора пункта меню «Поиск -> по имени». Изменения необходимо внести в команды FindFirst и FindNext.
Запрограммируем работу отчетов. Для формирования списка студентов используем самый простой вариант, предлагаемый VB, для вывода информации на принтер. В этом случае не используется специальных дизайнеров, не предоставляется возможности предварительного просмотра документа, нет графических возможностей. Для печати используется прямая отправка текстовой информации в печатающее устройство. Такой вариант организации печати удобно использовать для вывода на принтер большого количества текстовой информации (например, номенклатуры товаров магазина и данных об остатках), к которой не предъявляется специальных требований по оформлению. Для формирования отчетов в соответствии с определенными формами или с применением графического оформления, более предпочтительно использовать дизайнеры отчетов (см. предыдущую лабораторную работу).
Создайте новую форму с именем frmPrint.
Разместите на ней объекты:
ListBox (lstPrint), CommandButton (cmиPrint и cmbCancel).
Добавьте следующий программный код для формы frmPrint:
Public dbsData As Database
Public rstSpisok As Recordset
Private Sub cmbCancel_Click()
frmPrint.Hide
End Sub
Private Sub cmbPrint_Click()
Dim strPrint As String
Dim i As Integer
Set Printer = Printers(0)
Printer.Font.Name = "Times New Roman"
Printer.Font.Size = 14
For i = 0 To lstPrint.ListCount - 1
Printer.Print lstPrint.List(i)
Next i
Printer.EndDoc
End Sub
Private Sub Form_Activate()
Dim i As Long
lstPrint.Clear
If rstSpisok.EOF And rstSpisok.BOF Then Exit Sub
lstPrint.AddItem "СПИСОК УЧАЩИХСЯ КОМПЬЮТЕРНОЙ ШКОЛЫ"
lstPrint.AddItem ""
i = 1
rstSpisok.MoveLast
rstSpisok.MoveFirst
While Not (rstSpisok.EOF)
lstPrint.AddItem CStr(i) + ". " + rstSpisok.Fields("Familie") + " " + rstSpisok.Fields("Imja") + " группа: " + CStr(rstSpisok.Fields("Group").Value)
i = i + 1
rstSpisok.MoveNext
Wend
End Sub
Private Sub Form_Load()
Set dbsData = OpenDatabase("dbLaba.mdb")
Set rstSpisok = dbsData.OpenRecordset("Spisok", dbOpenDynaset)
End Sub
Сохраните проект. Проверьте работу приложения. В момент запуска формы frmPrint на экране отображаются данные в том виде, в каком они будут отправлены на печать. Уточните у преподавателя или лаборанта возможность распечатки списка, или дождитесь, пока преподаватель продемонстрирует работу данной функции всем студентам.
Запрограммируем работу пункта меню «Статистика». В данной части работы рассмотрим различные способы обработки числовой информации из БД. Создайте новую форму (frmStat) и разместите на ней элементы (txtKol, txtSrBall, txtWord, txtExel, txtAccss, cmbOk).
Следующий программный код реализует подсчет заданных значений.
Public dbsData As Database
Public rst As Recordset
Private Sub cmbOk_Click()
frmStat.Hide
End Sub
Private Sub Form_Activate()
Dim i As Long
Dim s As Long
Dim n As Long
Dim strSQL As String
Set rst = dbsData.OpenRecordset("Spisok", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
If rst.EOF And rst.BOF Then
txtKol.Text = ""
txtSrBall.Text = ""
txtWord.Text = ""
txtExel.Text = ""
txtAccess.Text = ""
Exit Sub
End If
' общее количество студентов соответствует количеству записей в таблице Spisok
txtKol.Text = rst.RecordCount
rst.Close
' для подсчета общего балла достаточно взять все оценки из таблицы Ocenki
Set rst = dbsData.OpenRecordset("Ocenki", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
If rst.EOF And rst.BOF Then
txtSrBall.Text = "0"
End If
s = 0
For i = 1 To rst.RecordCount
s = s + rst.Fields("Ocenka").Value
rst.MoveNext
Next i
' при подсчете среднего получится вещественное число, представим его в форматированном виде
txtSrBall.Text = Format(s / rst.RecordCount, "0.00")
rst.Close
' подсчитаем оценки с кодом предмета 1 (Word)
Set rst = dbsData.OpenRecordset("Ocenki", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
If rst.EOF And rst.BOF Then
txtWord.Text = "0"
Else
s = 0
n = 0
For i = 1 To rst.RecordCount
If rst.Fields("CodPredm").Value = 1 Then
s = s + rst.Fields("Ocenka").Value
n = n + 1
End If
rst.MoveNext
Next i
If n = 0 Then
txtWord.Text = "0"
Else
txtWord.Text = Format(s / n, "0.00")
End If
End If
rst.Close
' сформируем запрос который будет содержать только оценки с кодом предмета 2 (Exel)
Set rst = dbsData.OpenRecordset("Select * From Ocenki Where CodPredm = 2", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
If rst.EOF And rst.BOF Then
txtExel.Text = "0"
Else
s = 0
For i = 1 To rst.RecordCount
s = s + rst.Fields("Ocenka").Value
rst.MoveNext
Next i
txtExel.Text = Format(s / rst.RecordCount, "0.00")
End If
rst.Close
' и самый простой вариант - подсчет произвести с помощью SQL запроса
strSQL = "SELECT Sum([Ocenki]![Ocenka])/Count([Ocenki]![Ocenka]) AS Access " & _
"From Ocenki WHERE (((Ocenki.CodPredm)=3));"
Set rst = dbsData.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
If IsNull(rst.Fields("Access")) Then
txtAccess.Text = "0"
Else
txtAccess.Text = Format(rst.Fields("Access"), "0.00")
End If
rst.Close
End Sub
Private Sub Form_Load()
Set dbsData = OpenDatabase("dbLaba.mdb")
End Sub
Протестируйте приложение полностью, проверьте работу всех функциональных возможностей.
По окончании скомпилируйте exe-файл своей работы (File – Make Project1.exe), указав свое имя проекта и предоставьте преподавателю для защиты.