学生成绩管理系统（数据库）

1、数据库系统课程设计班级：0404101姓名：日期：2007-1-10学生成绩管理系统一，简介本程序为学生信息管理程序，有如下几大模块构成：学生信息管理，课程信息管理，学生成绩管理，以及数据库的备份与恢复，用户密码的修改等。主要实现学生信息，课程信息，成绩信息的添加，修改与删除以及查询功能。后台使用SQL Server 2000数据库，开发工具为。为保证程序的正确运行，需要先建立“成绩管理”数据库，其中包括三个表学生名单，课程信息，学生成绩。并建立SQL Server登录帐户db4，密码为lsc。A.先简要介绍各个窗口的功能：是登录窗口，实现用户的登录，其中用户的用户名及密码也是保存在数据库中的

2、。是学生信息管理窗口，实现学生信息的管理，其中包括浏览，查询，修改，删除等操作。是课程信息管理窗口,实现课程信息的管理，其中包括浏览，查询，修改删除等操作。是成绩信息管理窗口，实现成绩的浏览，查询，删除及修改等操作。其中操作学生名单表，操作课程信息表，操作成绩记录表。其中成绩记录表与前两个表具有约束关系。在前两个表中删除数据时将级联删除成绩记录表中的数据。如下图：实现对学生名单中记录的查询，实现对课程信息中记录的查询，实现对成绩记录中信息的查询。实现用户密码的修改功能。B.程序中使用的技术简介：本程序使用ADO来连接数据库，在查询的时候使用DataGrid控件来显示查询结果。要在Visual

3、Basic中使用ADO对象来访问数据库，必须先建立对ADO对象的引用。在Visual Basic中，可使用下面的语句来建立和断开SQL Server数据库连接：DSCn.Provider=”SQLOLEDB”Cn.ConnectionString=”database=成绩管理;server=(local);Integrated Security=SSPI”Cn.connectionTimeout=30CCSet cn=nothing使用Execute方法执行T-SQL语句：无返回值的用法，例如：Cn.Execute “create table 成绩(学号 char(8),语文 numeric(

4、3,1)”Cn.Execute “insert 成绩 values(20040001,85)”带返回值的用法：Set rs=cn.Execute(“select * from 成绩”)使用Recordset对象：1 添加记录R2删除记录Rs.Delete AffectRecords例如，可通过下面的方法来删除记录。RR3 修改和更新记录在使用Recordset对象时，可使用Fields方法来访问字段。Fields方法可使用字段名称或序号来访问指定字段。例如：Rs.Fields(“成绩”)=80Rs.Fields(1)=80二，程序功能图程序模块图：E-R图：三，源程序登录窗体（）：Option

5、 ExplicitPublic LoginSucceeded As BooleanDim objCn As ConnectionDim objSysLogin As RecordsetPrivate Sub cmdCancel_Click() LoginSucceeded = False '设置全局变量为 false，不提示失败的登录 End '我自己加的退出语句.End SubPrivate Sub cmdOK_Click() '检查正确的密码 Set objCn = New Connection With objCn连接到数据库 .Provider = "

6、SQLOLEDB" .ConnectionString = "User ID=db4;PWD=lsc;Data Source=(local);" & _ "Initial Catalog=成绩管理" .Open End With Set objSysLogin = New Recordset With objSysLogin Set .ActiveConnection = objCn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockOp

7、timistic .Open "select * from Systemlogin" End With If txtPassword = Trim(objSysLogin.Fields("pass") And txtUserName = Trim(objSysLogin.Fields("user") Then '将代码放在这里传递 '设置全局变量时最容易的 LoginSucceeded = True Else MsgBox "无效的用户名或密码，请重试!", vbCritical, "登录

8、" SendKeys "Home+End" End IfEnd Sub主窗体（）：Dim dbBR As Boolean 这个变量是在数据库恢复时用到的，为真则表明已经备份过数据库Private Sub cmdAbout_Click()End SubPrivate Sub cmdChangeUserInfo_Click() frmChangePwd.txtOld.SetFocus '这个可以用来设置取得焦点。End SubPrivate Sub cmdRestore_Click() '数据库恢复 Call killDP在数据库恢复之前，必须先杀死数

9、据库进程，否则会出错。 Dim restore As String con.ConnectionString = "Provider=SQLOLEDB;server=(local);UID=sa;PWD=lsc;" restore = "restore database 成绩管理 from disk='d:backupmydb.bak'" con.Execute "use master" If dbBR = False Then MsgBox "您尚未备份数据库，请先备份数据库。", vbCriti

10、cal, "错误" Else con.Execute (restore) '这行代码有很大的问题。(已修复.) MsgBox "数据库恢复成功!", vbInformation, "数据库恢复" End IfEnd SubPrivate Sub Cmdback_Click() '数据库备份 conn.ConnectionString = "Provider=SQLOLEDB;server=(local);UID=db4;PWD=lsc;"Timeout = 30 rec.CommandText =

11、"BACKUP DATABASE 成绩管理 TO DISK = 'd:Backupmydb.bak'" rec.CommandType = 1 Set rec.ActiveConnection = conn MsgBox "数据库备份成功! 请按时备份数据库。", vbInformation, "数据库备份" dbBR = TrueEnd SubPrivate Sub cmdSmanage_Click()End SubPrivate Sub cmdCmanage_Click()End SubPrivate Sub cm

12、dSCmanage_Click()End SubPrivate Sub cmdExit_Click() EndEnd SubPrivate Sub killDP() '杀死准备恢复的数据库的进程 Dim isql As String, connString As String connString = "Provider=SQLOLEDB;server=(local);UID=sa;PWD=lsc;" iDb.Open connString isql = "select spid from master.sysprocesses where dbid=db

13、_id('成绩管理')" iRe.Open isql, iDb, adOpenKeyset, adLockReadOnly While iRe.EOF = False用循环杀死进程 isql = "kill " & iRe(0) iDb.Execute isql WendEnd SubPrivate Sub Form_Load() dbBR = FalsedbBR为假表明尚未备份数据库End SubPrivate Sub muA_Click()End SubPrivate Sub muC_Click()End SubPrivate Sub

14、muE_Click() EndEnd SubPrivate Sub muG_Click()End SubPrivate Sub muN_Click()End SubPrivate Sub muP_Click()End Sub学生信息管理窗体（）：Dim isadding As BooleanDim objStudent As RecordsetDim objCn As ConnectionPrivate Sub cmdAdd_Click() txtRecNo = "添加新记录" txtNum = "" txtName = "" txt

15、Sex = "" txtBirth = "" txtClass = "" isadding = TrueEnd SubPrivate Sub cmdDelete_Click() If isadding Then isadding = False If objStudent.BOF = objStudent.EOF Then txtRecNo = "记录：无" Else ShowData End If Else If objStudent.RecordCount > 0 Then If MsgBox("

16、;是否删除当前记录？", vbYesNo + vbQuestion, "学生名单管理") = vbYes Then cmdMove(2).Value = True Else ShowData End If End If End IfEnd SubPrivate Sub cmdExit_Click() Unload MeEnd SubPrivate Sub cmdSave_Click() Dim objCopy As New Recordset With objCopy If .RecordCount > 0 Then .MoveFirst .Find &qu

17、ot;学号='" & Trim(txtNum) & "'" If (isadding And Not .EOF) Or _ (Not isadding And Not .EOF And .AbsolutePosition <> objStudent.AbsolutePosition) Then MsgBox "学号：" & Trim(txtNum) & "已被使用，请修改。", vbCritical, "学生名单管理" txtNum.SelSta

18、rt = 0 txtNum.SelLength = Len(txtNum) Else End If Else End If objStudent.Fields("学号") = Trim(txtNum) objStudent.Fields("姓名") = Trim(txtName) objStudent.Fields("性别") = Trim(txtSex) objStudent.Fields("出生日期") = Trim(txtBirth) objStudent.Fields("班级") = T

19、rim(txtClass) MsgBox "数据保存成功", vbInformation, "学生名单管理" isadding = False txtRecNo = "当前记录：共" & objStudent.AbsolutePosition & "条/第" & objStudent.RecordCount & "条" End WithEnd SubPrivate Sub cmdSea_Click()这样可以设置焦点，使程序更人性化，更易用。End SubPriv

20、ate Sub Form_Load() Set objCn = New Connection With objCn连接数据库 .Provider = "SQLOLEDB" .ConnectionString = "User ID=db4;PWD=lsc;Data Source=(local);" & _ "Initial Catalog=成绩管理" .Open End With Set objStudent = New Recordset With objStudent Set .ActiveConnection = objC

21、n .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockOptimistic .Open "SELECT * FROM 学生名单" End With cmdMove(0).Value = TrueEnd SubPrivate Sub cmdMove_Click(Index As Integer)浏览功能的实现。 With objStudent Select Case Index Case 0 If .RecordCount > 0 And Not .BOF Then .M

22、oveFirst Case 1 If .RecordCount > 0 And Not .BOF Then .MovePrevious If .BOF Then .MoveFirst End If Case 2 If .RecordCount > 0 And Not .EOF Then .MoveNext If .EOF Then .MoveLast End If Case 3 If .RecordCount > 0 And Not .EOF Then .MoveLast End Select If .RecordCount < 1 Then txtRecNo = &q

23、uot;记录：No" txtNum = "" txtName = "" txtSex = "" txtBirth = "" txtClass = "" Else ShowData End If End With If isadding Then isadding = FalseEnd SubPrivate Sub ShowData()显示记录子程序 With objStudent txtNum = .Fields("学号") txtName = .Fields(&q

24、uot;姓名") txtSex = .Fields("性别") txtBirth = .Fields("出生日期") txtClass = .Fields("班级") txtRecNo = "当前记录：第" & .AbsolutePosition & "条/共" & .RecordCount & "条" End WithEnd SubPrivate Sub Form_Unload(Cancel As Integer) Set objC

25、n = Nothing Set objStudent = NothingEnd Sub查询学生信息窗体（）：Private Sub cmdClose_Click() Unload MeEnd SubPrivate Sub cmdQuery_Click() Dim objRs As New Recordset, objSource As New Recordset Dim objCn As New Connection, strSQL As String strSQL = "SELECT * FROM 学生名单 WHERE 学号 LIKE '" & Trim(

26、txtNum) & "%' AND 姓名 LIKE '" & _ Trim(txtName) & "%' and 性别 LIKE '" & Trim(txtSex) & "%' and 班级 LIKE '" & Trim(txtClass) & "%'" With objCn '创建数据库连接 .Provider = "SQLOLEDB" .ConnectionString =

27、 "Provider=MSDASQL.1;Persist Security Info=False;" & _ "Data Source=DB4" .Open End With With objRs '执行查询 Set .ActiveConnection = objCn .CursorLocation = adUseClient .CursorType = adOpenStatic .Open strSQL End With Set dGrid.DataSource = objRs '显示查询结果End SubPrivate Sub

28、 Form_resize() dGrid.Width = Me.ScaleWidth - dGrid.Left - 300 dGrid.Height = Me.ScaleHeight - dGrid.Top - 200End Sub课程信息管理窗体（）Dim isadding As Boolean '定义布尔变量Dim objCourse As RecordsetDim objCn As ConnectionPrivate Sub cmdAdd_Click() txtrecno = "添加新记录" txtName = "" txtTeacher

29、= "" txtRoom = "" txtTime = "" isadding = TrueEnd SubPrivate Sub cmdDelete_Click() If isadding Then isadding = False If objCourse.BOF = objCourse.EOF Then txtrecno = "记录：无" Else ShowData End If Else If objCourse.RecordCount > 0 Then If MsgBox("是否删除当前记录

30、？", vbYesNo + vbQuestion, "课程信息管理") = vbYes Then cmdMove(2).Value = True Else ShowData End If End If End IfEnd SubPrivate Sub cmdExit_Click() Unload MeEnd SubPrivate Sub cmdSave_Click() objCourse.Fields("课程") = Trim(txtName) objCourse.Fields("教师") = Trim(txtTeacher

31、) objCourse.Fields("教室") = Trim(txtRoom) objCourse.Fields("时间") = Trim(txtTime) MsgBox "数据保存成功", vbInformation, "课程信息管理" txtrecno = "记录：End SubPrivate Sub cmdSea_Click()单击“查询”按钮End SubPrivate Sub Form_Load() Set objCn = New Connection With objCn连接数据库 .Pro

32、vider = "SQLOLEDB" .ConnectionString = "User ID=db4;PWD=lsc;Data Source=(local);" & _ "Initial Catalog=成绩管理" .Open End With Set objCourse = New Recordset With objCourse Set .ActiveConnection = objCn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType

33、= adLockOptimistic .Open " SELECT * FROM 课程信息" End With cmdMove(0).Value = TrueEnd SubPrivate Sub cmdMove_Click(Index As Integer)浏览功能的实现 With objCourse Select Case Index Case 0 If .RecordCount > 0 And Not .BOF Then .MoveFirst Case 1 If .RecordCount > 0 And Not .BOF Then .MovePrevious

34、 If .BOF Then .MoveFirst End If Case 2 If .RecordCount > 0 And Not .EOF Then .MoveNext If .EOF Then .MoveLast End If Case 3 If .RecordCount > 0 And Not .EOF Then .MoveLast End Select If .RecordCount < 1 Then txtrecno = "记录：无" txtName = "" txtTeacher = "" txtRoo

35、m = "" txtTime = "" Else ShowData End If End With If isadding Then isadding = FalseEnd SubPrivate Sub ShowData() With objCourse txtName = .Fields("课程") txtTeacher = .Fields("教师") txtRoom = .Fields("教室") txtTime = .Fields("时间") txtrecno = &q

36、uot;当前记录：第" & .AbsolutePosition & "条/共" & .RecordCount & "条" End WithEnd SubPrivate Sub Form_Unload(Cancel As Integer) Set objCn = Nothing Set objCourse = NothingEnd Sub查询课程详细信息（）：Private Sub cmdClose_Click() Unload MeEnd SubPrivate Sub cmdStart_Click() Dim o

37、bjRs As New Recordset Dim objCn As New Connection, strSQL As String strSQL = "select * from 课程信息 where 课程 like '" & Trim(txtCourse) & "%' and 教师 like '" & Trim(txtTeacher) & "%' and 时间 like '" & Trim(txtTime) & "%' an

38、d 教室 like '" & Trim(txtRoom) & "%' " With objCn .Provider = "SQLOLEDB" .ConnectionString = "User ID=db4;PWD=lsc;Data Source=(local);" & _ "Initial Catalog=成绩管理" .Open End With With objRs Set .ActiveConnection = objCn .CursorLocation =

39、adUseClient .CursorType = adOpenStatic .Open strSQL End With Set dGrid.DataSource = objRsEnd SubPrivate Sub Form_resize()End Sub课程成绩管理（）Dim objCourse As RecordsetDim objStudent As RecordsetDim objScore As RecordsetDim objCn As ConnectionDim isadding As BooleanPrivate Sub cmdAdd_Click() txtName = &qu

40、ot;姓名不可修改"由于设计的原因，这里姓名设定为不能修改。 txtNum = "" txtCourse = "" txtScore = "" isadding = True txtrecno = "记录添加中" objScore.Fields("学号") = Trim(txtNum) objStudent.Find "学号='" & objScore.Fields("学号") & "'"End

41、 SubPrivate Sub cmdDelete_Click() If isadding Then isadding = False If objScore.BOF = objScore.EOF Then txtrecno = "当前记录：没有记录" Else ShowData End If Else If objScore.RecordCount > 0 Then If MsgBox("是否删除当前记录？", vbYesNo + vbQuestion, "学生成绩管理") = vbYes Then cmdMove(2).Va

42、lue = True Else ShowData End If End If End IfEnd SubPrivate Sub cmdExit_Click() Unload Me卸载窗体End SubPrivate Sub cmdSave_Click() objScore.Fields("学号") = Trim(txtNum) objScore.Fields("课程") = Trim(txtCourse) objScore.Fields("成绩") = Val(txtScore) MsgBox "成绩保存成功",

43、vbInformation, "成绩管理" isadding = False txtrecno = "记录：End SubPrivate Sub cmdSea_Click()End SubPrivate Sub Form_Load() Set objCn = New Connection With objCn .Provider = "SQLOLEDB" .ConnectionString = "User ID=db4;PWD=lsc;Data Sourse=(local);" & _ "Initial C

44、atalog=成绩管理" .Open End With Set objScore = New Recordset With objScore Set .ActiveConnection = objCn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockOptimistic .Open "SELECT * FROM 成绩记录" End With Set objCourse = New Recordset With objCourse Set .ActiveConnec

45、tion = objCn .CursorLocation = adUseClient .Open "SELECT * FROM 课程信息" End With Set objStudent = New Recordset With objStudent Set .ActiveConnection = objCn .CursorLocation = adUseClient .Open "SELECT * FROM 学生名单" End With cmdMove(0).Value = TrueEnd SubPrivate Sub cmdMove_Click(In

46、dex As Integer) With objScore Select Case Index Case 0 If .RecordCount > 0 And Not .BOF Then .MoveFirst Case 1 If .RecordCount > 0 And Not .BOF Then .MovePrevious If .BOF Then .MoveFirst End If Case 2 If .RecordCount > 0 And Not .BOF Then .MoveNext If .EOF Then .MoveLast End If Case 3 If .R

47、ecordCount > 0 And Not .EOF Then .MoveLast End Select If .RecordCount < 1 Then txtrecno = "当前记录：没有" txtName = "" txtNum = "" txtCourse = "" txtScore = "" Else ShowData End If End WithEnd SubPrivate Sub ShowData() With objScore '根据学号显示姓名 txt

48、Num = .Fields("学号") objStudent.Find "学号='" & .Fields("学号") & "'" txtName = objStudent.Fields("姓名") '根据课程编号显示课程名称 objCourse.Find "课程='" & .Fields("课程") & "'" txtCourse = objCourse.Field

49、s("课程") If IsNull(.Fields("成绩") Then txtScore = "No" Else txtScore = .Fields("成绩") End If txtrecno = "当前记录：第" & .AbsolutePosition & "条/共" & .RecordCount & "条" End WithEnd SubPrivate Sub Form_Unload(Cancel As Integer) Set objCn = Nothing Set objScore = Nothing Set objCourse = Nothing Set objStudent = NothingEnd Sub成绩详细信息查询（）：Private Sub cmdClose_Click() Unload