《数据库工资管理系统课程设计报告.doc》由会员分享，可在线阅读，更多相关《数据库工资管理系统课程设计报告.doc（15页珍藏版）》请在三一文库上搜索。
1、课程设计报告课程设计题目：工资管理系统 工资管理系统一、实验题目：1) 员工基本信息的管理功能。2) 单位员工变动、奖惩情况的管理功能。3) 工资的计算、修改功能。4) 查询统计功能。5) 报表打印功能。6) 具有数据备份和数据恢复功能。设计要求：在提供员工详细的工资资料的同时，尽量使系统能具一定的安全性和通用性。2、 编程1、主窗口的设计 MDIForm1.HideForm9.ShowEnd SubPrivate Sub MDIForm_Load()End SubPrivate Sub ss_Click() MDIForm1.HideForm4.ShowEnd SubPrivate Sub
2、SSS_Click() MDIForm1.HideForm2.ShowEnd SubPrivate Sub aa_Click() MDIForm1.HideForm3.ShowEnd SubPrivate Sub SSSS_Click()MDIForm1.HideForm5.ShowEnd SubPrivate Sub SSSSS_Click()MDIForm1.HideForm6.ShowEnd SubPrivate Sub SSSSSS_Click()MDIForm1.HideForm7.ShowEnd SubPrivate Sub SSSSSSS_Click()MDIForm1.Hide
3、Form8.ShowEnd Sub2、登入窗口设计Option ExplicitPrivate Function Selectsql(SQL As String) As ADODB.Recordset 返回ADODB.Recordset对象 Dim ConnStr As String Dim Conn As ADODB.Connection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Set Conn = New ADODB.Connection On Error GoTo MyErr: ConnStr = Provider=S
4、QLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=teacher;Data Source=LENOVO-PC 这是连接SQL数据库的语句Conn.Open ConnStr rs.CursorLocation = adUseClient rs.Open Trim$(SQL), Conn, adOpenDynamic, adLockOptimistic Set Selectsql = rs Exit FunctionMyErr: Set rs = Nothing Set Conn = Not
5、hing 释放相关的系统资源 MsgBox Err.Description, vbInformation, 系统提示 显示出错信息End FunctionPrivate Sub Command1_Click() Dim SQL As String Dim rs As ADODB.Recordset If Text1.Text = Then MsgBox 请输入口令!, 16 Text1.SetFocus Exit Sub End If SQL = SELECT * FROM 系统用户表结构 WHERE 用户名 = & Text1.Text & AND 口令 = & Text2.Text & a
6、nd 身份= & Combo1.Text & objcn, adOpenStatic, adLockOptimistic Set rs = Selectsql(SQL) If rs.RecordCount 0 Then MDIForm1.Show 想要打开的主窗体 MsgBox 登录成功!, 64, 提示 Unload Me Else MsgBox 口令不对,请重新输入!, 16, 提示 Text1.SetFocus End IfEnd SubPrivate Sub Form_Load()Form1.Left = (Screen.Width - Form1.Width) / 2Form1.To
7、p = (Screen.Height - Form1.Height) / 23、员工基本信息管理设计Private Sub Command7_Click()Text1.Text = Text2.Text = Text3.Text = Text4.Text = Text5.Text = Combo1.ListIndex = 4Combo2.ListIndex = 4Command5.Enabled = TrueCommand6.Enabled = True 在保存新记录之前禁用下述控件Command1.Enabled = TrueCommand2.Enabled = TrueCommand3.E
8、nabled = TrueCommand4.Enabled = TrueEnd SubPrivate Sub Command8_Click()Dim mno As String mno = InputBox(请输入员工编号, 查找窗) Adodc1.Recordset.Find 员工编号= & mno & , , , 1 If Adodc1.Recordset.EOF Then MsgBox 无此员工编号, , 提示End SubPrivate Sub Command5_Click()Command1.Enabled = FalseCommand2.Enabled = FalseCommand
9、3.Enabled = FalseCommand4.Enabled = FalseAdodc1.Recordset.AddNewEnd SubPrivate Sub Command8_Click()Form2.HideMDIForm1.ShowEnd SubPrivate Sub Command9_Click()Command1.Enabled = FalseCommand2.Enabled = FalseCommand3.Enabled = FalseCommand4.Enabled = FalseAdodc1.Recordset(姓名) = Text1.TextAdodc1.Records
10、et(编号) = Text2.TextAdodc1.Recordset(部门) = Text3.TextAdodc1.Recordset(有住房) = Text4.TextAdodc1.Recordset(专家) = Text5.TextAdodc1.Recordset(生日) = Text6.TextAdodc1.Recordset(工作时间) = Text7.TextAdodc1.Recordset(职称) = Combo1.TextAdodc1.Recordset(职务) = Combo2.TextAdodc1.Recordset.UpdateMsgBox (修改成功)End SubPr
11、ivate Sub Command6_Click()Command1.Enabled = FalseCommand2.Enabled = FalseCommand3.Enabled = FalseCommand4.Enabled = FalseIf MsgBox(确定删除当前记录？, vbQuestion + vbYesNo, 删除？) = vbYes ThenAdodc1.Recordset.DeleteAdodc1.Recordset.MoveNextIf Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLastEnd IfEnd SubPri
12、vate Sub Command10_Click()Command1.Enabled = FalseCommand2.Enabled = FalseCommand3.Enabled = FalseCommand4.Enabled = FalseDim strb() As ByteCommonDialog1.ShowOpenOpen CommonDialog1.FileName For Binary As #1f1 = LOF(1)ReDim strb(f1)Get #1, , strbAdodc1.Recordset.Fields(照片).AppendChunk strbClose #1Ima
13、ge1.Picture = LoadPicture(CommonDialog1.FileName)End SubPrivate Sub Command1_Click()Adodc1.Recordset.MoveFirstEnd Sub Private Sub Command2_Click()Adodc1.Recordset.MovePreviousEnd SubPrivate Sub Command3_Click()Adodc1.Recordset.MoveNextEnd SubPrivate Sub Command4_Click()Adodc1.Recordset.MoveLastEnd S
14、ub4、用户信息管理设计Private Sub Command1_Click()Command5.Enabled = FalseCommand6.Enabled = FalseCommand7.Enabled = FalseCommand8.Enabled = FalseAdodc1.Recordset(用户名) = Text1.TextAdodc1.Recordset(口令) = Text2.TextAdodc1.Recordset(身份) = Text3.TextAdodc1.Recordset.UpdateMsgBox (修改成功)End SubPrivate Sub Command2_
15、Click()Command5.Enabled = FalseCommand6.Enabled = FalseCommand7.Enabled = FalseCommand8.Enabled = FalseAdodc1.Recordset.AddNewEnd SubPrivate Sub Command3_Click()Command5.Enabled = FalseCommand6.Enabled = FalseCommand7.Enabled = FalseCommand8.Enabled = FalseIf MsgBox(确定删除当前记录？, vbQuestion + vbYesNo,
16、删除？) = vbYes ThenAdodc1.Recordset.DeleteAdodc1.Recordset.MoveNextIf Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLastEnd IfEnd SubPrivate Sub Command4_Click()Form4.HideMDIForm1.ShowEnd SubPrivate Sub Command5_Click()Adodc1.Recordset.MoveFirstEnd Sub Private Sub Command6_Click()Adodc1.Recordset.Mov
17、ePreviousEnd SubPrivate Sub Command7_Click()Adodc1.Recordset.MoveNextEnd SubPrivate Sub Command8_Click()Adodc1.Recordset.MoveLastEnd SubPrivate Sub Command9_Click()Command5.Enabled = TrueCommand6.Enabled = TrueCommand7.Enabled = TrueCommand8.Enabled = TrueEnd Sub5、工资合算表设计Form3.HideMDIForm1.ShowEnd S
18、ub Private Sub Command1_Click() Dim mno As String mno = InputBox(请输入员工编号, 查找窗)Adodc1.Recordset.Find 编号= & mno & , , , 1Text1 = Text21.Text Text3 = Text22.Text If Adodc1.Recordset.EOF Then MsgBox 无此员工编号, , 提示Adodc4.Recordset.Find 编号= & mno & , , , 1If Adodc4.Recordset.EOF Then MsgBox 无此员工编号, , 提示Dim
19、M As String M = Text1Text2.DataField = MAdodc3.Recordset(M) = Text2.TextDim p As String p = Text3Text4.DataField = pAdodc2.Recordset(p) = Text2.Text Text15.Text = Val(Text9) + Val(Text10) + Val(Text11) + Val(Text12) + Val(Text13) + Val(Text14)Text16.Text = Val(Text2) + Val(Text4) + Val(Text5) + Val(
20、Text6) + Val(Text7) + Val(Text8) Text25.Text = Val(Text16) - Val(Text15) End Sub6、恢复与备份设计Public Function fBackupDatabase_a(ByVal sBackUpfileName$ _ , ByVal sDataBaseName$ _ , Optional ByVal sIsAddBackup As Boolean = False _ ) As String Private Sub Command1_Click() Dim iDb As ADODB.Connection Dim iCo
21、ncStr$, iSql$, iReturn$ On Error GoTo lbErr 创建对象 Set iDb = New ADODB.Connection 连接数据库服务器 iConcStr = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=teacher;Data Source=LENOVO-PC iDb.Open iConcStr 生成数据库备份语句 iSql = backup database & sDataBaseName & & vbCrLf & _
22、 to disk= & sBackUpfileName & & vbCrLf & _ with description= & zj-backup at: & Date & ( & Time & ) & vbCrLf & _ IIf(sIsAddBackup, , ,init) iDb.Execute iSql GoTo lbExit lbErr: iReturn = ErrorlbExit: fBackupDatabase_a = iReturnEnd SubPublic Function fRestoreDatabase_a(ByVal sBackUpfileName$ _ , ByVal
23、sDataBaseName$ _ , Optional ByVal sDataBasePath$ = _ , Optional ByVal sBackupNumber& = 1 _ , Optional ByVal sReplaceExist As Boolean = False _ ) As String Private Sub Command2_Click() Dim iDb As ADODB.Connection, iRe As ADODB.Recordset Dim iConcStr$, iSql$, iReturn$, iI& On Error GoTo lbErr 连接数据库服务器
24、 iConcStr = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=teacher;Data Source=LENOVO-PC iDb.Open iConcStr 创建对象 Set iDb = New ADODB.Connection Set iRe = New ADODB.Recordset 得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录 If sDataBasePath = Then iSql = select filen
25、ame from master.sysfiles iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly iSql = iRe(0) iRe.Close sDataBasePath = Left(iSql, InStrRev(iSql, ) End If 检查数据库是否存在 If sReplaceExist = False Then iSql = select 1 from master.sysdatabases where name= & sDataBaseName & iRe.Open iSql, iDb, adOpenKeyset, adLock
26、ReadOnly If iRe.EOF = False Then iReturn = 数据库已经存在! iRe.Close GoTo lbExit End If iRe.Close End If 关闭用户进程 , 防止其它用户正在使用数据库, 导致数据恢复失败 iSql = select spid from master.sysprocesses where dbid=db_id( & sDataBaseName & ) iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly While iRe.EOF = False iSql = kill & iR
27、e(0) iDb.Execute iSql iRe.MoveNext Wend iRe.Close 获取数据库恢复信息 iSql = restore filelistonly from disk= & sBackUpfileName & & vbCrLf & _ with file= & sBackupNumber iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly 生成数据库恢复语句 iSql = restore database & sDataBaseName & & vbCrLf & _ from disk= & sBackUpfileNam
28、e & & vbCrLf & _ with file= & sBackupNumber & vbCrLf With iRe While Not .EOF iReturn = iRe(PhysicalName) iI = InStrRev(iReturn, .) iReturn = IIf(iI = 0, , Mid(iReturn, iI) & iSql = iSql & ,move & iRe(LogicalName) & _ to & sDataBasePath & sDataBaseName & iReturn & vbCrLf .MoveNext Wend .Close End Wit
29、h iSql = iSql & IIf(sReplaceExist, ,replace, ) iDb.Execute iSql iReturn = GoTo lbExit lbErr: iReturn = ErrorlbExit: fRestoreDatabase_a = iReturnEnd Sub3、 实验心得 通过本次实验我学会了知识是学不会的，只有在实践中才能体现它的作用。当然，我说这样的话不是绝对的否认。只是说，实践与理论结合才够完美。因为在此次课程设计当中，我不仅学会了用VB与数据库结合，还学会了怎么样用VB去调用数据库。虽然是这样，但还是不够完美，或许是因为学到的知识尚浅，对数据库的一些强大的功能还掌握的不够到位。但我会努力，在今后的道路中，我会不断摸索，不断前进，不断学习。致使理论与实践结合。 在此，我深深的体会到了，只要自己动手去做，认认真真的。不管什么难题，哪怕失败也是高心的。因为我努力和付出过。所以，这此的课程设计，我自己感觉很满足，也感觉很开心。虽然结果不的那么的理想。不过，下次的课程设计我会更加的努力。永远奔着最好去