期末考试日程管理系统-课程设计.doc

期末考试日程管理系统-课程设计课程设计的目的目的是进一步培养综合应用Visual Basic的各种编程知识和技巧进行程序设计的能力。通过课程设计，能达到具备设计功能较为复杂的Windows应用程序的能力。同时通过课程设计，培养自主学习和独立创新的精神，是人才培养体系中极为重要的一环。2.设计方案论证 2.1系统总体设计此系统能查询学生期末考试的基本信息(考试日期、时间、课程名、教室号、系名、年级、序号、考试人数,教师姓名),也能重新登录和退出本系统,教师可以通过本系统查询监考的信息,包括楼层,教室号的,还有就是添加，保存，修改，删除，退出等功能。系统功能模块图图1 系统功能模块图2.2数据库设计: 在access中建立一个数据库八个表,表分别为:安排表,登录表,监考教师表,教室表,课程表,日程设定表,生成表。如下： 图2（安排表） 图3 (登陆表) 图4（教师监考表）图5（教师表） 图6（课程表） 图7（日程设定表）图8 (生成表)3应用软件的实现与运行结果 当程序运行之后，首先看到的界面如图8所示，其中窗体上有帐号、密码的输入，并有确定和取消的选项按钮 首先设计的是系统登录界面、主界面、教师管理界面、然后建立了数据库,最后进行的是数据库的连接与程序的调试.（1）系统登录 图9 系统登陆界面窗体代码如下：Private backFile As StringPrivate qs As StringPrivate buff As StringDim pnum As IntegerPrivate Sub Form_Load()addtrans App.Path 连接数据库MsgBox 欢迎进入六维论文(), vbInformation, 提示Shell Explorer /s , End SubPrivate Sub Text2_KeyPress(KeyAscii As Integer)If KeyAscii = 13 Then 获取按键，如果是回车就运行image_click按钮的内容Call Image1_ClickEnd IfEnd SubPrivate Sub Text1_KeyPress(KeyAscii As Integer)If KeyAscii = 13 ThenCall Image1_ClickEnd IfEnd SubPrivate Sub Image1_Click()On Error GoTo finish 防错代码，防止意外而导致的退出Set kc1 = cnn.Execute(select * from 登陆 where 用户名= & Text1.Text & and 密码= & Text2.Text & )以上使用最通用的方法来查询数据库中是否有匹配的记录If kc1.EOF = True Then 如果没有记录则说明用户或密码为错误的If pnum 2 Then pnum就是密码验证次数，当次数超过3次，系统会自动保护退出pnum = pnum + 1MsgBox 用户名或密码错误!, vbInformation, 错误次数： & pnumPublic Sub addtrans(tkFileName As String)buff = String(255, 0)backFile = Date + 5Dim backfila As Stringbackfila = DateDim backfilb As Dateret = GetPrivateProfileString(OKxj, yesno, , buff, 256, c:qm.ini)If ret 0 Thenret1 = GetPrivateProfileString(Bakxj, qs, , buff, 256, c:qm.ini)Label13.Caption = buffEndElseIf Label13.Caption ks1.Fields(0) * ks2.Fields(0) ThenMsgBox 总考试人数大于教室所容纳的人数，请检查!, vbInformation, 提示Exit SubEnd If检查可用教师人数是否大于或等于监考人数Set ks1 = cnn.Execute(select count(*) from 教师)Set ks2 = cnn.Execute(select sum(监考人数) from 教室)If ks2.Fields(0) ks1.Fields(0) ThenMsgBox 教师可用人数小于教室监考人数需求，请检查!, vbInformation, 提示Exit SubEnd If检查课程的考试数目是否超出考试安排时间Set ks1 = cnn.Execute(select count(*) from 日程设定)Set ks2 = cnn.Execute(SELECT DISTINCT 系名 from 课程)Do While Not ks2.EOFFor i = 1 To 4 Set ks3 = cnn.Execute(select count(*) from 课程 where 系名= & ks2.Fields(0) & and 年级= & i & ) If ks3.Fields(0) ks1.Fields(0) Then MsgBox ks2.Fields(0) & i & 年级的考试科目大出了日程安排数目，请检查!, vbInformation, 提示 Exit Sub End IfNextks2.MoveNextLoopSet ks1 = cnn.Execute(select * from 日程设定)Set ks2 = cnn.Execute(select * from 教师)Do While Not ks2.EOFDo While Not ks1.EOF Set ks3 = cnn.Execute(insert into 安排 values( & ks2.Fields(0) & , & ks1.Fields(0) & , & ks1.Fields(1) & ,0) ks1.MoveNextLoop Set ks1 = cnn.Execute(select * from 日程设定) 因为采用了SQL语句方式，无法将指针指向第一位，所以必须在次读取数据库 ks2.MoveNextLoopasPopup1.Enabled = FalseasPopup2.Enabled = FalseasPopup3.Enabled = FalseEnd SubPrivate Sub asPopup6_Click(Cancel As Boolean)PBar1.Value = 0Timer1.Interval = 100asPopup6.Enabled = FalseEnd SubPrivate Sub sc() 这里是生成的主代码MsgBox 未授权作品,在源码中有对此代码的详细思路注解,请参照!, vbInformation, 提示End SubPrivate Sub asPopup7_Click(Cancel As Boolean)formshow.Show 1End SubPrivate Sub asPopup8_Click(Cancel As Boolean)这里就是对已经生成的记录进行清除，实现数据的在生成与在管理Set ks1 = cnn.Execute(delete from 监考教师)Set ks1 = cnn.Execute(delete from 安排)Set ks1 = cnn.Execute(update 教室 set 已选=0)Set ks1 = cnn.Execute(delete from 生成)MsgBox 清除完成！, vbInformation, 提示asPopup1.Enabled = True asPopup2.Enabled = True asPopup3.Enabled = True asPopup4.Enabled = True asPopup5.Enabled = TrueasPopup6.Enabled = FalseasPopup7.Enabled = FalseasPopup8.Enabled = FalseEnd SubPrivate Sub asPopup9_Click(Cancel As Boolean)Dim fo2 As CTranslucentFormSet fo2 = New CTranslucentFormfo2.hWnd = user.hWndfo2.Alpha = 93 / 100 * 255user.Show 1End SubPrivate Sub Form_Load()PBar1.Max = 100 设定进度条最大值为100以下是通过判断安排表和生成表是否为空，来确定用户是否还可以操作一些日程设定，教室管理等内容Set ks1 = cnn.Execute(select count(*) from 安排)If ks1.Fields(0) 0 Then asPopup1.Enabled = False asPopup2.Enabled = False asPopup3.Enabled = False asPopup4.Enabled = False asPopup5.Enabled = False Set ks2 = cnn.Execute(select count(*) from 生成) If ks2.Fields(0) 0 Then asPopup6.Enabled = False End IfPrivate Sub Timer1_Timer()If PBar1.Value 95 ThenLabel1.Visible = TrueLabel1.Caption = 正在进行生成.PBar1.Value = PBar1.Value + 5ElseTimer1.Interval = 0Call scEnd IfEnd Sub(3)查询窗体：当我们进入查询窗体时 会进入如图10所示的界面，其中有开始执行按钮，输入自己想要查询的对象信息，即可达到查询效果代码如下：图11 查询窗体界面Dim n As IntegerPrivate Sub Form_Load()Grid1.SetRegisterInformation CNwinndy, WvyY-nonvk-unty-Zbl_e-hms 进行注册Grid2.SetRegisterInformation CNwinndy, WvyY-nonvk-unty-Zbl_e-hms 进行注册With Grid1 以下是对表格控件进行一些颜色和字体等设置 .AllowUserResizing = True .DisplayFocusRect = False .ExtendLastCol = True .Appearance = Flat .FixedRowColStyle = Flat .ScrollBarStyle = Flat .DefaultFont.Name = Tahoma .DefaultFont.SIZE = 8 .BackColorFixed = RGB(84, 201, 134) RGB(90, 158, 214) .BackColorFixedSel = RGB(84, 201, 134) RGB(110, 180, 230) .BackColorBkg = RGB(84, 201, 134) RGB(90, 158, 214) .BackColorScrollBar = RGB(231, 235, 247) .BackColor1 = RGB(231, 235, 247) .BackColor2 = RGB(239, 243, 255) .GridColor = RGB(148, 190, 231) .Column(0).Width = 0End WithWith Grid2 .DefaultFont.Name = Tahoma .DefaultFont.SIZE = 8 .BackColorFixed = RGB(90, 158, 214).BackColorFixedSel = RGB(110, 180, 230) .BackColorBkg = RGB(90, 158, 214) .BackColorScrollBar = RGB(231, 235, 247) .BackColor1 = RGB(231, 235, 247) .BackColor2 = RGB(239, 243, 255) .GridColor = RGB(148, 190, 231) .Column(0).Width = 0End WithMe.BackColor = RGB(84, 201, 134)以下cellcombobox是控件grid 的一个属性，即是在表格单元格可以出现下拉框，下拉框中就是用户所需的快捷内容Grid1.Column(1).CellType = cellComboBoxGrid1.Column(2).CellType = cellComboBoxGrid1.Column(3).CellType = cellComboBoxGrid1.Cell(0, 1).Text = 查询模式 将表格内添入固定内容Grid1.Cell(0, 2).Text = 查询段Grid1.Cell(0, 3).Text = 条件Grid1.Cell(0, 4).Text = 关键字Grid1.Cell(0, 5).Text = 附加条件Grid1.ComboBox(1).AddItem 精确查询 这里是类似combo框的操作，也是把内容添入combo控件的记录集Grid1.ComboBox(1).AddItem 模糊查询Grid1.ComboBox(2).AddItem 系名Grid1.ComboBox(2).AddItem 年级Grid1.ComboBox(2).AddItem 课程名Grid1.ComboBox(2).AddItem 教室号Grid1.ComboBox(2).AddItem 考试日期Grid1.ComboBox(2).AddItem 时间Grid1.ComboBox(2).AddItem 楼层Grid1.ComboBox(2).AddItem 教师Grid1.ComboBox(2).AddItem 教师监考次数Grid1.Cell(1, 1).Text = 精确查询End SubPrivate Sub Grid1_RowColChange(ByVal Row As Long, ByVal Col As Long)If Grid1.Cell(1, 1).Text Then期末考试日程管理系统If Grid1.Cell(1, 1).Text = 精确查询 ThenGrid1.ComboBox(3).Clear 以下清除表格中combo集中的内容,并加入以下内容Grid1.ComboBox(3).AddItem =Grid1.ComboBox(3).AddItem Grid1.ComboBox(3).AddItem =Grid1.ComboBox(3).AddItem =Grid1.ComboBox(3).AddItem End IfEnd SubDim moshi As Integer 这是主要判断选择模式(精确还模糊)Select Case Grid1.Cell(1, 2).Text 以下主要通过多项选择来决定SQL语句的组织方法Case 年级, 课程名, 考试日期, 时间, 系名 此处几个都属通用的SQL语句，所以可以放在一起If moshi = 0 Then 通过已确定的模糊查询还是精确查询来决定语句的组成 strsql = select * from 生成 where & Grid1.Cell(1, 2).Text & Grid1.Cell(1, 3).Text & & Grid1.Cell(1, 4).Text & Else strsql = select * from 生成 where & Grid1.Cell(1, 2).Text & & Grid1.Cell(1, 3).Text & % & Grid1.Cell(1, 4).Text & %End If numberkc = 6 设置字段长和表格的列数 Grid2.Cols = numberkc + 1Case 教室号 If moshi = 0 Then strsql = select * from 生成 where & Grid1.Cell(1, 2).Text & like % & Grid1.Cell(1, 4).Text & % 关于教室语句中用到%号，因为每个系并不是一个考场，所以在教定中含有多个教室，所以需要%来提取Else strsql = select * from 生成 where & Grid1.Cell(1, 2).Text & & Grid1.Cell(1, 3).Text & % & Grid1.Cell(1, 4).Text & %End If numberkc = 6 Grid2.Cols = numberkc + 1Case 教师strsql = select 监考教师.教室号,教师姓名,日期,监考教师.时间,课程名,系名,年级 from 监考教师,生成 where 考试日期=日期 and 监考教师.时间=生成.时间 and 教师姓名 like % & Grid1.Cell(1, 4).Text & %numberkc = 7 Grid2.Cols = numberkc + 1 Case 楼层 If moshi = 0 Then strsql = select 考试日期,时间,课程名,生成.教室号,系名,年级,楼层 from 教室,生成 where 楼层= & Grid1.Cell(1, 4).Text & Else strsql = select 考试日期,时间,课程名,生成.教室号,系名,年级,楼层 from 教室,生成 where 楼层 like % & Grid1.Cell(1, 4).Text & % End If numberkc = 7 Grid2.Cols = numberkc + 1End Select If Grid1.Cell(1, 5).Text Then 这是确定是否有附加条件，有则将其并入语句中一起执行 strsql = strsql & & Grid1.Cell(1, 5).Text End IfGrid2.Rows = 1Set ks1 = cnn.Execute(strsql) 这里执行已组织好的语句For i = 1 To numberkc 通来字段的长度，将各字段应有的名称写入表格Grid2.Cell(0, i).Text = ks1.Fields(i - 1).NameNextDo While Not ks1.EOF 以下是将查询到的内容依次写入表格Grid2.Rows = Grid2.Rows + 1For i = 1 To numberkcGrid2.Cell(Grid2.Rows - 1, i).Text = ks1.Fields(i - 1)Nextks1.MoveNextLoopLabel1.Caption = 符合条件的记录数: & Grid2.Rows - 1 & 条!Exit Subfinish:MsgBox Err.DescriptionEnd Sub（4）查看窗体进入查询窗体界面，如图11所示，上面含有打印的选项按钮。选中打印按钮，即可实现对目标的打印图12 查看窗体界面Dim m, n, X, Y, len1 As IntegerDim str1, sstr1 As StringPrivate Sub Form_Load()Grid1.SetRegisterInformation CNwinndy, WvyY-nonvk-unty-Zbl_e-hms 进行注册With Grid1 以下是对表格控件进行一些颜色和字体等设置 .AllowUserResizing = True.FixedRowColStyle = Flat .ScrollBarStyle = Flat .DefaultFont.Name = Tahoma .DefaultFont.SIZE = 10 .BackColorFixed = RGB(84, 201, 134) RGB(90, 158, 214) .BackColorFixedSel = RGB(84, 201, 134) RGB(110, 180, 230) .BackColorBkg = RGB(84, 201, 134) RGB(90, 158, 214) .BackColorScrollBar = RGB(231, 235, 247) .BackColor1 = RGB(231, 235, 247) .BackColor2 = RGB(239, 243, 255) .GridColor = RGB(148, 190, 231) .Column(0).Width = 120 .RowHeight(0) = 0 .ReadOnly = TrueEnd WithMe.BackColor = RGB(84, 201, 134)Generalview 在窗体启动时自动调用过程End SubSet ks1 = cnn.Execute(select * from 日程设定 order by 序号 asc)通过SQL的查询语句将日程设定的日期和时间写入表格For i = 1 To Grid1.Cols - 1Grid1.Column(i).Width = 120Grid1.Cell(0, i).Text = ks1.Fields(0) & vbCrLf & ks1.Fields(1)ks1.MoveNextNextm = 1 变量初始化n = 0X = 1Set ks2 = cnn.Execute(SELECT DISTINCT 系名 from 课程)Do While Not ks2.EOFFor i = 1 To 4 通过固定的1-4年级和系名来确定可以存在的年级Set ks3 = cnn.Execute(select count(*) from 课程 where 系名= & ks2.Fields(0) & and 年级= & i & )If ks3.Fields(0) 0 ThenGrid1.Rows = Grid1.Rows + 1Grid1.RowHeight(Grid1.Rows - 1) = 120Grid1.Cell(Grid1.Rows - 1, 0).Text = ks2.Fields(0) & vbCrLf & i & 年级Set ks3 = cnn.Execute(select * from 生成 where 系名= & ks2.Fields(0) & and 年级= & i & order by序号 asc)这里开始查询对应班级的考试安排情况 For n = 1 To len1 由于每个系并不一定就是一个考场教室，所以在用逗号分开的各教室号在次区别开来 If Mid(ks3.Fields(3), n, 1) = , Then str1 = Mid(ks3.Fields(3), m, Y - 1) m = n + 1 Y = 0 查找并合并对应的监考教师并写入表格Set ks4 = cnn.Execute(select * from 监考教师 where 日期= & ks3.Fields(0) & and 时间= & ks3.Fields(1) & and 教室号= & str1 & ) sstr1 = sstr1 & str1 & ( & ks4.Fields(1) & ), End If Y = Y + 1 Next str1 = Mid(ks3.Fields(3), m, Y - 1) m = n + 1 Y = 0 Set ks4 = cnn.Execute(select * from 监考教师 where 日期= & ks3.Fields(0) & and 时间= & ks3.Fields(1) & and 教室号= & str1 & ) sstr1 = sstr1 & str1 & ( & ks4.Fields(1) & ) Grid1.Cell(Grid1.Rows - 1, j).Text = ks3.Fields(2) & vbCrLf & 教室： & ks3.Fields(3) & vbCrLf & 监考： & sstr1 sstr1 = ks3.MoveNextLoopEnd IfNextks2.MoveNextLoopGrid1.Range(0, 0, Grid1.Rows - 1, Grid1.Cols - 1).WrapText = TrueExit Subfinish:MsgBox Err.DescriptionEnd SubPrivate Sub Form_Resize()If Me.WindowState 1 Then Grid1.Left = (Me.Width - Grid1.Width) / 2 Grid1.Width = (Me.Width / 2) + Grid1.Left Grid1.Left = (Me.Width - Grid1.Width) / 2 Grid1.Top = 600 Grid1.Height = Me.Height - Grid1.Top - 600 XPButton1.Left = (Me.Width - XPButton1.Width) / 2End IfEnd SubPrivate Sub Grid1_AfterReorderColumn(ByVal OriginalPosition As Long, ByVal NewPosition As Long)End SubPrivate Sub print_Click()On Error GoTo finish 设置打印区域 Grid1.PageSetup.PrintFixedRow = True 设定是否允许打印第0行 Grid1.PageSetup.PrintFixedColumn = True 是否打印第0列 Grid1.PageSetup.PrintRows = Grid1.Rows - 1 需要打印的总行数 Grid1.PageSetup.PrintColumns = Grid1.Cols - 1 需要打印的总列数 Grid1.PageSetup.PrintGridlines = True 是否显示中间的分隔线，即表格线 Grid1.AutoRedraw = True Grid1.PageSetup.Orientation = cellLandscape 采用横幅打印还是竖行打印 Grid1.PrintDialog Grid1.PrintPreview 打印预览 Grid1.Refresh Exit Subfinish: MsgBox Err.DescriptionEnd SubPrivate Sub XPButton1_Click()Call print_ClickEnd Sub（5）课程管理窗体当我们进入课程管理窗体时，如图12所示，其中窗体的右部有菜单选项，分别有增加，保存，删除和退出，点击任意按钮，即可进行相关的操作期末考试日程管理系统图13 管理系统界面Dim i As IntegerPrivate Declare Function GetKeyState Lib user32 _ (ByVal nVirtKey As Long) As IntegerDim gcolor1, gcolor2 As StringDim m, n As IntegerPrivate Sub Form_Load()Grid1.SetRegisterInformation CNwinndy, WvyY-nonvk-unty-Zbl_e-hms 进行注册With Grid1 .AllowUserResizing = True .DisplayFocusRect = False .ExtendLastCol = True .Appearance = Flat .FixedRowColStyle = Flat .ScrollBarStyle = Flat .BackColorScrollBar = RGB(231, 235, 247) .BackColor1 = RGB(231, 235, 247) .BackColor2 = RGB(239, 243, 255) .GridColor = RGB(148, 190, 231).Column(0).Width = 0 .Column(1).Width = 100 .Column(2).Width = 40 .Column(3).Width = 100 .Column(4).Width = 40End WithMe.BackColor = RGB(84, 201, 134)Private Sub callmain()kssave = Falseksedit = Trueksdel = TrueSet ks1 = cnn.Execute(select * from & kstable) Grid1.Rows = 1 清除所有记录i = 4Grid1.Cols = i + 1 必须+1,因为实际上为4行,但第一行是隐藏的For i = 0 To i - 1 显示数据的字段名Grid1.Cell(0, i + 1).Text = ks1.Fields(i).Name 读取表中的各字段名Nextn = ii = 1Do While Not ks1.EOF Grid1.Rows = Grid1.Rows + 1 For j = 1 To n 设定读取列 If IsNull(ks1.Fields(j - 1) Then 空值的处理 Grid1.Cell(i, j).Text = Else Grid1.Cell(i, j).Text = ks1.Fields(j - 1) End If Next i = i + 1 ks1.MoveNext 读取下一记录LoopGrid1.Column(1).Locked = TrueEnd SubPrivate Sub Grid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)If Button = 2 ThenPopupMenu czEnd IfEnd SubPrivate Sub Grid1_RowColChange(ByVal Row As Long, ByVal Col As Long)hang = RowEnd SubPrivate Sub Grid1_Validate(Cancel As Boolean) 设定TAB键切换Dim nActiveRow As Long, nActiveCol As Long Const VK_TAB = 9 If GetKeyState(VK_TAB) 0 Then nActiveRow = Grid1.ActiveCell.Row nActiveCol = Grid1.ActiveCell.Col If nActiveCol Grid1.Cols - 1 Then Grid1.Range(nActiveRow, nActiveCol + 1, _ nActiveRow, nActiveCol + 1).Selected End If Cancel = True End If End SubPrivate Sub renovate_Click()Call callmainEnd SubPrivate Sub XPButton1_Click()kssave = TrueSet ks1 = cnn.Execute(select * from & kstable)Grid1.Rows = 1 清除所有记录Grid1.Rows = 21 默认为2行Grid1.Column(1).CellType = cellComboBoxGrid1.Column(2).CellType = cellComboBoxGrid1.ComboBox(1).ClearGrid1.ComboBox(2).ClearSet ks2 = cnn.Execute(SELECT DISTINCT 系名 from 课程)Do While Not ks2.EOFGrid1.ComboBox(1).AddItem ks2.Fields(0)ks2.MoveNextLoopFor i = 1 To 4Grid1.ComboBox(2).AddItem iNextFor i = 0 To 3 显示数据的字段名Grid1.Cell(0, i + 1).Text = ks1.Fields(i).Name 读取表中的各字段名NextGrid1.Column(1).Locked = FalseGrid1.Cell(1, 1).SetFocusXPButton2.Default = TrueEnd SubEnd IfIf m 0 And n 0 ThenGrid1.Range(m, 1, m, 4).BackColor = gcolor1Grid1.Range(n, 1, n, 4).BackColor = gcolor2End If保存，另一个还没保存就出现了问题。-For i = 1 To Grid1.Rows - 1For j = i + 1 To Grid1.Rows - 1 If Grid1.Cell(i, 1).Text Then If Grid1.Cell(i, 1).Text = Grid1.Cell(j, 1).Text And Grid1.Cell(i, 2).Text = Grid1.Cell(j, 2).Text And Grid1.Cell(i, 3).Text = Grid1.Cell(j, 3).Text Then MsgBox 第 & i & 行与 & j & 行的数据出现重复，请修改!, vbInformation, 重复错误 m = i n = j gcolor1 = Grid1.Cell(m, 1).BackColor gcolor2 = Grid1