数据库应用技术课程设计报告

学院：高职院

专业：计算机应用

班级：

姓名：

学号：

指导老师：魏老师

第一章系统分析

本学生成绩管理系统可以说是一个综合性的学校学生管理系统，这它集成了学生信息管理、学生成绩管理、用户权限管理等多种功能，因而具有较强的实用性。目前计算机管理在日常生活中的地位变得越来越重要。它能够代替人做各种重复、繁琐的劳动，并且拥有操作简单、可信度好、不易出错等优点，大大减少了不必要的人力消耗，提高个人的工作效率。学生信息查询是每个学校必须面临的问题，所以，如何开发一个应用简单、界面友好、容易操作、数据安全性好的管理系统就成为非常重要的技术问题。

学生成绩管理系统的设计与实现，是在班级学生处数据库基础上，只对学生成绩信息进行管理的一个系统，是对学校教务管理系统的补充，主要是针对辅导员的需要，主要包括二个用户群：普通用户、高级用户。

普通用户，主要的需求有学生成绩查询、学生基本信息查询、普通用户密码修改。

高级用户，主要的需求有学生成绩管理、学生信息管理、普通用户和高级用户密码修改。

第二章系统功能流程图

本系统在执行时，先根据不同的操作人员的需要来进行相应的模块，然后可以输入数据或者进行其它的查询或浏览等操作；总体来说，本系统属于一个权限型管理系统。

数据库应用技术课程设计报告

图2.1 系统功能流程

第三章数据库设计

本系统的数据库是SQL server数据库，在SQL环境下创建数据库学生信息库.mdf文件。该数据库包括学生信息表、成绩信息表、用户登录信息表用于纪录学生的基本信息数据库表结构如下所示：

数据库链接代码

Option Explicit

Dim strGrade As String

Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

'Debug.Print "test"

If Adodc1.Recordset.BOF Or Adodc1.Recordset.EOF Then

Text1.Text = ""

Text2.Text = ""

Text3.Text = ""

Exit Sub

End If

Text1.Text = Adodc1.Recordset("stud_id")

Text2.Text = Adodc1.Recordset("name")

Dim sql As String

sql = "select * from stud_grade where stud_id='" & Text1.Text & "'"

Adodc2.RecordSource = sql

Adodc2.Refresh

Combo2.Clear

With Adodc2.Recordset

Do Until .EOF

Combo2.AddItem .Fields("course_id").Value

.MoveNext

Loop

End With

End Sub

Private Sub Combo1_Click()

Dim sqlN As String

sqlN = "select distinct stud_grade.stud_id,name" _

& " from stud_grade,stud_info " _

& " where stud_info.stud_id=stud_grade.stud_id and class ='" _

& Combo1.Text & "' order by stud_grade.stud_id"

Adodc1.RecordSource = sqlN

Adodc1.Refresh

With DataGrid1

Set .DataSource = Adodc1

End With

End Sub

Private Sub MyLock(ByVal bLock As Boolean)

Text3.Locked = bLock

Combo1.Locked = Not bLock

Combo2.Locked = Not bLock

'Text4.Locked = Not bLock

Command1.Enabled = bLock

Command3.Enabled = Not bLock

Command2.Enabled = Not bLock

DataGrid1.Enabled = bLock

End Sub

Private Sub Combo2_Click()

If Trim$(Text1.Text) = "" Or Trim$(Combo2.Text) = "" Then

Text3.Text = ""

Exit Sub

Else

With Adodc2.Recordset

If .RecordCount ＞ 0 Then .MoveFirst

.Find "course_id='" & Combo2.Text & "'"

If .EOF Then Exit Sub

Text3.Text = .Fields("grade").Value

End With

End If

End Sub

Private Sub Command1_Click()

If Trim$(Text1.Text) = "" Or Trim$(Combo2.Text) = "" Then Exit Sub

strGrade = Text3.Text

Call MyLock(False)

Call FocusBack(Text3)

End Sub

Private Sub Command2_Click()

If Trim$(Text3.Text) = "" Then

MsgBox "请输入成绩", vbExclamation

Call Command3_Click

Text3.SetFocus

Exit Sub

End If

With Adodc2.Recordset

.Fields("grade").Value = Val(Text3.Text)

.Update

End With

Call MyLock(True)

MsgBox "成绩修改成功", vbInformation

End Sub

Private Sub Command3_Click()

Text3.Text = strGrade

Call MyLock(True)

End Sub

Private Sub Command4_Click()

If Trim$(Text1.Text) = "" Or Trim$(Combo2.Text) = "" Then Exit Sub

With Adodc2.Recordset

.Delete

.Update

Text3.Text = ""

MsgBox "成绩删除成功", vbInformation

Adodc1.Refresh

End With

End Sub

Private Sub Command5_Click()

Unload Me

End Sub

Private Sub Form_Load()

Call MyLock(True)

Call AddClassItem(Combo1)

DataGrid1.AllowUpdate = False

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

第四章流程步骤

1.打开SQL SEREVER服务器，点击刷新开始。如图：

2.打开SQL企业管理器，新建名为“test”的数据库.

3.打开查询分析器，输入一下代码，选择test数据库，测试.如图：

4.打开VB 6.0,画出如下窗体：

5.分别在相应窗口下输入代码。

源代码如下：Private Sub addAdminItem_Click()

frmUser.Show

Me.Hide

End Sub

Private Sub addCourse_Click()

add_course.Show

Me.Hide

End Sub

Private Sub addStuInfo_Click()

frmAddEss.Show

Me.Hide

End Sub

Private Sub exitSystem_Click()

Unload Me

End Sub

Private Sub Form_Load()

'根据用户权限确定是否显示用户管理格菜单项

addAdminItem.Visible = gblnPurview

delUser.Visible = gblnPurview

modifypremission.Visible = gblnPurview

Call createconnection '调用标准模块中的过程建立连接

End Sub

Private Sub insertGrade_Click()

Me.Hide

insert_Grade.Show

End Sub

Private Sub modifyCourse_Click()

modify_course.Show

Me.Hide

End Sub

Private Sub modifyGrade_Click()

Me.Hide

modify_grade.Show

End Sub

Private Sub modifypassword_Click()

Me.Hide

modifPassword.Show

End Sub

Private Sub modifyStuInfo_Click()

frmModiEss.Show

Me.Hide

End Sub

Private Sub selectGrade_Click()

select_grade.Show

Me.Hide

End Sub

Private Sub selectStuInfo_Click()

frmQueryEss.Show

Me.Hide

End Sub

Option Explicit

Public conn As ADODB.Connection

Public gstrUser As String '全局变量存用户名

Public gblnPurview As Boolean '全局变量存用户权限

Public pubCnn As New ADODB.Connection '全局连接对象供各模块使用

'建立连接

Public Sub createconnection()

pubCnn.CursorLocation = adUseClient '

pubCnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Initial Catalog=vbtest"

End Sub

Public Function db_link(ByRef conn As ADODB.Connection)

conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated

Security=SSPI;Persist Security Info=False;Initial Catalog=student;Data

Source=f117"

conn.Open

End Function

Public Sub AddClassItem(cboX As ComboBox)

Dim rsTmp As New ADODB.Recordset '临时记录集

'过滤重复记录和空字段

rsTmp.Open "select distinct class_name from class", _

pubCnn, adOpenStatic, adLockBatchOptimistic

cboX.Clear

Do Until rsTmp.EOF

cboX.AddItem rsTmp("class_name")

rsTmp.MoveNext

Loop

Set rsTmp = Nothing

End Sub

Public Sub AddCourseItem(cboX As ComboBox)

Dim rsTmp As New ADODB.Recordset '临时记录集

'过滤重复记录和空字段

rsTmp.Open "select distinct course_id from lesson_info", _

pubCnn, adOpenStatic, adLockBatchOptimistic

cboX.Clear

Do Until rsTmp.EOF

cboX.AddItem rsTmp("course_id")

rsTmp.MoveNext

Loop

Set rsTmp = Nothing

End Sub

'焦点返回文本框并反显内容，参数为文本框对象变量

Public Sub FocusBack(txtX As TextBox)

With txtX

.SelStart = 0

.SelLength = Len(.Text)

.SetFocus

End With

End Sub

Public Sub NewClassItem(cboX As ComboBox)

Dim i As Integer

Dim blnOld As Boolean

Dim sNew As String

blnOld = False

sNew = Trim$(cboX.Text)

For i = 0 To cboX.ListCount - 1

'若列表中已有该班级，设置标志，退出循环

If cboX.List(i) = sNew Or sNew = "" Then

blnOld = True

Exit For

End If

If blnOld = False Then '若列表中无该班级名称，则添加

cboX.AddItem sNew

End If

End Sub

Private Sub Combo1_LostFocus() '班级组合框失去焦点时检查列表，添加新

项

Call NewClassItem(Combo1) '调用标准模块中的过程

End Sub

Private Sub Command1_Click()

If Trim$(Text1.Text) = "" Then

MsgBox "请输入学号", vbExclamation

Text1.SetFocus

Exit Sub

End If

If Trim$(Text2.Text) = "" Then

MsgBox "请输入姓名", vbExclamation

Text2.SetFocus

Exit Sub

End If

If Trim$(ComboGender.Text) = "" Then

MsgBox "请输入性别", vbExclamation

Text3.SetFocus

Exit Sub

End If

If Trim$(Text4.Text) = "" Then

MsgBox "请输入出生日期", vbExclamation

Text4.SetFocus

Exit Sub

End If

If Trim$(Combo1.Text) = "" Then

MsgBox "请选择或输入班级", vbExclamation

Combo1.SetFocus

Exit Sub

End If

adoAdd.Refresh '刷新记录集

'检查是否有重复学号

adoAdd.Recordset.Find ("stud_id='" & Text1.Text & "'")

If Not adoAdd.Recordset.EOF Then

MsgBox "学号重复，请重新输入", vbExclamation

Call FocusBack(Text1) '焦点返回学号框

Exit Sub

End If

If Not IsDate(Text4.Text) Then '出生日期若非日期型，重新输入

MsgBox "出生如期应按日期格式(yyyy-mm-dd)输入", vbExclamation

Call FocusBack(Text4)

Exit Sub

Else

Text4.Text = Format(Text4.Text, "yyyy-mm-dd")

With adoAdd.Recordset

.AddNew

.Fields(0) = Trim$(Text1.Text)

.Fields(1) = Trim$(Text2.Text)

.Fields(2) = Trim$(ComboGender.Text)

.Fields(3) = Trim$(Text4.Text)

.Fields(4) = Trim$(Combo1.Text)

.Update

End With

MsgBox "学籍信息已成功添加", vbInformation

End If

End Sub

Private Sub Command2_Click()

Unload Me

End Sub

Private Sub Form_Load()

Dim sql As String

sql = "select * from stud_info"

adoAdd.RecordSource = sql

adoAdd.Refresh

Call AddClassItem(Combo1)

ComboGender.Clear

ComboGender.AddItem "男"

ComboGender.AddItem "女"

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)

If Not IsNumeric(Chr(KeyAscii)) And KeyAscii ＜＞ 8 Then

KeyAscii = 0

End If

End Sub

Option Explicit

Dim sql As String

Private Sub Command1_Click()

Dim sql1 As String

Dim i As Integer

Dim sqlA(3) As String

sqlA(0) = "stud_id like '%" & Trim(Text1.Text) & "% '"

sqlA(1) = "name like '%" & Trim(Text2.Text) & "%'"

sqlA(2) = "gender = '" & Trim(ComboGender.Text) & "'"

sqlA(3) = "class like '%" & Trim(Combo2.Text) & "%'"

sql1 = "" '用于存放SQL语句中的WHERE字句条件

For i = 0 To Check1.Count - 1

If Check1(i).Value = 1 Then '若复选框被选中

If sql1 = "" Then '若只有一个复选框被选中

sql1 = sqlA(i) '字符串数组加入一个条件

Else '若多个复选框被选中，用AND

符加入多个条件

sql1 = sql1 & " and " & sqlA(i)

End If

'退出循环后，若条件字符串为空，说明未选中任何复选框

If sql1 = "" Then

Call Command2_Click ' 显示全部记录

Exit Sub

End If

sql = "select stud_id,name,gender,birthday,class from stud_info "

& _

"where " & sql1 & " order by stud_id"

Adodc1.RecordSource = sql

Adodc1.Refresh

If Adodc1.Recordset.BOF Then

MsgBox "对不起，没有你所要找的记录", vbInformation

Exit Sub

End If

Set DataGrid1.DataSource = Adodc1

End Sub

Private Sub Command2_Click()

sql = "select stud_id,name,gender,birthday,class from stud_info

order by stud_id"

Adodc1.RecordSource = sql '生成记录集

Adodc1.Refresh

Set DataGrid1.DataSource = Adodc1

End Sub

Private Sub Command3_Click()

Unload Me

End Sub

Private Sub Form_Load()

Call Command2_Click

DataGrid1.AllowUpdate = False

Call AddClassItem(Combo2)

ComboGender.Clear

ComboGender.AddItem "男"

ComboGender.AddItem "女"

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

Option Explicit

Dim sNo As String

Private Sub Command1_Click()

sNo = Trim$(Text1.Text)

Call MyLock(False)

End Sub

Private Sub Command2_Click()

On Error Resume Next

If Trim$(Text1.Text) = "" Then

MsgBox "请输入学号", vbExclamation

Text1.SetFocus

Exit Sub

End If

If Trim$(Text2.Text) = "" Then

MsgBox "请输入姓名", vbExclamation

Text2.SetFocus

Exit Sub

End If

If Trim$(Combo1.Text) = "" Then

MsgBox "请输入性别", vbExclamation

Text3.SetFocus

Exit Sub

End If

If Trim$(Text3.Text) = "" Then

MsgBox "请输入出生日期", vbExclamation

'Text4.SetFocus

Exit Sub

End If

If Trim$(Combo2.Text) = "" Then

MsgBox "请选择或输入班级", vbExclamation

Combo1.SetFocus

Exit Sub

End If

If Not IsDate(Text3.Text) Then

MsgBox "出生日期应按日期格式(yyyy-mm-dd)输入!", vbExclamation

With Text3

.SelStart = 0

.SelLength = Len(.Text)

.SetFocus

End With

Else

Text3.Text = Format(Text3.Text, "yyyy-mm-dd")

Adodc1.Recordset.Update

If Err = -2147467259 Then

Adodc1.Recordset.CancelUpdate

MsgBox "学号重复，请重新输入", vbExclamation

Call FocusBack(Text1)

Exit Sub

End If

MsgBox "修改成功", vbInformation

End Sub

Private Sub Command3_Click()

With Adodc1.Recordset

.CancelUpdate

.MoveNext

.MovePrevious

End With

Call MyLock(True)

End Sub

Private Sub Command4_Click()

With Adodc1.Recordset

.Delete

.MoveNext

If .EOF And .RecordCount ＞ 0 Then .MoveLast

End With

MsgBox "删除成功", vbInformation

End Sub

Private Sub Command5_Click()

Unload Me

End Sub

Private Sub Form_Load()

Call MyLock(True)

Call AddClassItem(Combo2)

'Call AddClassItem(Combo3)

Combo1.Clear

Combo1.AddItem "男"

Combo1.AddItem "女"

End Sub

Private Sub MyLock(ByVal bLock As Boolean)

Text1.Locked = bLock

Text2.Locked = bLock

Text3.Locked = bLock

Combo1.Locked = bLock

Combo2.Locked = bLock

'Text4.Locked = Not bLock

Command5.Enabled = bLock

Command3.Enabled = Not bLock

Command2.Enabled = Not bLock

Command4.Enabled = bLock

Adodc1.Enabled = bLock

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

Private Sub Command1_Click()

stuNo = Text1.Text

courseNo = Combo1.Text

grade = Val(Text2.Text)

If Trim$(Text1.Text) = "" Then

MsgBox "请输入学号", vbExclamation

Text1.SetFocus

Exit Sub

End If

If Trim$(Combo1.Text) = "" Then

MsgBox "请选择课程号", vbExclamation

Combo1.SetFocus

Exit Sub

End If

If Trim$(Text2.Text) = "" Then

MsgBox "请输入学号", vbExclamation

Text2.SetFocus

Exit Sub

End If

Adodc1.Refresh '刷新记录集

'检查是否有重复学号

Adodc1.Recordset.Find ("stud_id='" & Text1.Text & "'")

If Not Adodc1.Recordset.EOF Then

MsgBox "学号重复，请重新输入", vbExclamation

Call FocusBack(Text1) '焦点返回学号框

Exit Sub

End If

With Adodc1.Recordset

.AddNew

.Fields(0) = Trim$(Text1.Text)

.Fields(1) = Trim$(Combo1.Text)

.Fields(2) = Trim$(Text2.Text)

.Update

End With

MsgBox "学籍信息已成功添加", vbInformation

End Sub

Private Sub Command2_Click()

Unload Me

frmMain.Show

End Sub

Private Sub Form_Load()

Call AddCourseItem(Combo1)

End Sub

Private Sub Command1_Click()

If Trim$(Text1.Text) = "" Then

MsgBox "请输入课程号", vbExclamation

Text1.SetFocus

Exit Sub

End If

If Trim$(Text2.Text) = "" Then

MsgBox "请输入课程名", vbExclamation

Combo1.SetFocus

Exit Sub

End If

Adodc1.Refresh '刷新记录集

'检查是否有重复学号

Adodc1.Recordset.Find ("course_id='" & Text1.Text & "'")

If Not Adodc1.Recordset.EOF Then

MsgBox "课程号重复，请重新输入", vbExclamation

Call FocusBack(Text1) '焦点返回学号框

Exit Sub

End If

With Adodc1.Recordset

.AddNew

.Fields(0) = Trim$(Text1.Text)

.Fields(1) = Trim$(Text2.Text)

.Update

End With

MsgBox "课程信息已成功添加", vbInformation

End Sub

Private Sub Command2_Click()

Unload Me

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

Private Sub Command1_Click()

Call MyLock(False)

End Sub

Private Sub Command2_Click()

On Error Resume Next

If Trim$(Text1.Text) = "" Then

MsgBox "请输入课程号", vbExclamation

Text1.SetFocus

Exit Sub

End If

If Trim$(Text2.Text) = "" Then

MsgBox "请输入课程名", vbExclamation

Text2.SetFocus

Exit Sub

End If

Adodc1.Recordset.Update

If Err = -2147467259 Then

Adodc1.Recordset.CancelUpdate

MsgBox "学号重复，请重新输入", vbExclamation

Call FocusBack(Text1)

Exit Sub

End If

MsgBox "修改成功", vbInformation

End Sub

Private Sub Command3_Click()

With Adodc1.Recordset

.CancelUpdate

.MoveNext

.MovePrevious

End With

Call MyLock(True)

End Sub

Private Sub Command4_Click()

With Adodc1.Recordset

.Delete

.MoveNext

If .EOF And .RecordCount ＞ 0 Then .MoveLast

End With

MsgBox "删除成功", vbInformation

End Sub

Private Sub Command5_Click()

Unload Me

End Sub

Private Sub Form_Load()

Call MyLock(True)

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

Private Sub MyLock(ByVal bLock As Boolean)

Text1.Locked = bLock

Text2.Locked = bLock

'Text4.Locked = Not bLock

Command1.Enabled = bLock

Command3.Enabled = Not bLock

Command2.Enabled = Not bLock

Adodc1.Enabled = bLock

End Sub

Private Sub Command1_Click()

Dim conn1 As New ADODB.Connection

Dim rs As New ADODB.Recordset

' Call db_link(conn1)

If Trim$(Text1.Text) = "" Then

MsgBox "请输入新密码", vbExclamation

Text1.SetFocus

Exit Sub

End If

If Trim$(Text2.Text) = "" Then

MsgBox "请输入新密码", vbExclamation

Text2.SetFocus

Exit Sub

End If

If Trim$(Text1.Text) ＜＞ Trim$(Text2.Text) Then

MsgBox "确认密码错误，请重新输入", vbExclamation

Exit Sub

End If

'rs.Open "update admin_info set password='" + Trim$(Text1.Text) &

"'where admin_name='" & gstrUser & "'"

With Adodc1

.RecordSource = "select * from admin_info where admin_name='"

& gstrUser & "'"

.Refresh

.Recordset("password") = Text1.Text

.Recordset.Update

End With

MsgBox "密码修改成功", vbInformation

End Sub

Private Sub Command2_Click()

Unload Me

End Sub

Private Sub Form_Unload(Cancel As Integer)

frmMain.Show

End Sub

第五章设计心得

通过本次课程设计，进一步加强了对VB语言的理解，提高了对数据库知识的认识，从中学到了许多，对以后程序设计有很大帮助.

相关推荐

数据库应用技术课程设计报告

第一章 系统分析

第二章 系统功能流程图

第三章 数据库设计

专栏推荐

第一章系统分析

第二章系统功能流程图

第三章数据库设计