数据库实训报告工资管理系统(SQL+VB)

1、数据库实训报告设计题目： 工资管理系统 学 院：计算机与信息工程专 业： 年 级： 2009级 班 级： 二班 姓 名： 指导老师： 学 号： 一、需求分析1.1 系统分析对于一个公司来说，拥有一个正确、及时而且规范地经行员工工资的管理和发放是非常重要的。由于工资的计算涉及的数据是多方面的，每位员工的工资又都需要单独计算，在员工数量越多时，工资管理的工作量就会越大，而且在重复的数据输入和计算中容易出错。所以就有了工资管理系统，本系统的宗旨及总体任务就是帮助财务部门提高工作效率，实现公司工资信息管理的自动化、规范化以及系统化。本系统是通过对公司基本信息的管理、工资管理等环节的数据收集而建立的数据

2、库工资管理系统，实现有用的数据的快速查询，减少操作中可能出现的错误以及减少数据管理的工作量。1.2 系统功能设计本系统主要包括的功能有：（1） 保存职工的基本信息资料（2） 设置工资的级别（3） 根据工资管理数据和员工的工资进行计算（4） 将职工工资的数据进行保存，便于查找和核实（5） 对保存的数据，可以根据特殊字段进行查找1.3系统模块划分开发这个系统的目的就是帮助企业的财务主管部门提高工作效率，实现企业工资信息管理的系统化、规范化和自动化。能够和认识管理系统、考勤管理系统相结合，真正实现企业高效、科学、现代化的员工管理。系统最终实现的主要功能如下。工资信息设置部分：包括设置员工的基本工资、

3、岗位工资的级别和金额等。工资信息管理部分：包括统计计算工资、查询工资和修改工资信息等。系统部分：包括添加用户和删除用户等。二、开发平台微软公司的visual basic 6.0是windows应用程序开发工具，是目前最为广泛的、易学易用的面向对象的开发工具。visual basic提供了大量的控件，这些控件可用于设计界面和实现各种功能，减少了编程人员的工作量，也简化了界面设计过程，从而有效的提高了应用程序的运行效率和可靠性。因此，本系统使用microsoft visual basic 6.0开发。数据库引用的是本期所学的sql server 2000。三、数据库分析设计3.1 数据库的建立用s

4、ql建立一个名为“工资管理系统”的数据库。3.2 数据表的建立针对企业工资信息管理系统的需求，通过对员工工资计算过程的内容分析，为本系统设计如下的数据项和数据结构：基本工资信息：包括工资级别和工资金额。岗位工资信息：包括工资级别和工资金额。工资信息：包括员工编号、基本工资、岗位工资、水电费、实发金额。部门信息：包括部门编号、部门名称、部门负责人、部门人数。工资统计信息：包括员工编号、姓名、出生年月、所属部门、文化程度、职称。用户信息：包括用户名、密码和权限。员工信息表员工编号char (8)主键，不允许重复姓名char (8)性别char (2)出生年月char(10)所属部门char (4)

5、外键文化程度char (4)职称char (12)部门信息部门编号char (4)主键,不允许重复部门名称char (20)部门负责人char (8)部门人数int基本工资工资级别char (2)主键,不允许重复工资金额int(8, 2)岗位工资工资级别char (2)主键,不允许重复工资金额int工资信息员工编号char (8)主键,不允许重复； 外键基本工资int岗位工资int水电费int实发金额int用户表用户名char (8)主键,不允许重复密码char (20)权限char (20)3.3 建立关系图其关系图如下所示：各表之间的联系四、工资管理系统应用程序设计工资信息实体员工编号员工

6、姓名旷工扣除剩余工资基本工资部门编号部门名称加班工资奖金实发工资应发工资4.1 系统构成系统构成如下图所示，包括登录、主界面、添加删除用户、基本工资、岗位工资、部门信息管理、员工信息管理、发放工资、添加或修改员工工资、查询员工工资10个窗体和两个公共模块组成。4.2 公共模块一public power as stringpublic tempuser as stringpublic cnmod as new adodb.connectionpublic username as stringpublic userpwd as stringpublic function executesql(by

7、val sql as string, msgstring as string) as adodb.recordsetdim cnn as adodb.connectiondim rst as adodb.recordsetdim stokens() as stringon error goto executesql_errorstokens = split(sql)set cnn = new adodb.connectioncnn.open connectstringif instr(insert,delete,update, ucase$(stokens(0) thencnn.execute

8、 sqlmsgstring = stokens(0) & 操作成功elseset rst = new adodb.recordsetrst.open trim$(sql), cnn, adopenkeyset, adlockoptimisticset executesql = rstmsgstring = 查询到 & rst.recordcount & 条记录 end ifexecutesql_exit:set rst = nothingset cnn = nothingexit functionexecutesql_error:msgstring = 查询错误: & err.descript

9、ionresume executesql_exitend functionpublic function connectstring() as stringconnectstring = provider=sqloledb.1;integrated security=sspi;persist security info=false;initial catalog=工资管理系统;data source=.end function4.3 公共模块二public function select_sql(sql as string) as adodb.recordsetdim cn as adodb.

10、connectiondim rst as adodb.recordseton error goto sql_err:set cn = new adodb.connectionset rst = new adodb.recordsetcn.open provider=msdasql;driver=sql server; & _server=fall;database=工资管理系统;uid=;pwd=rst.open sql, cn, 3, 2set select_sql = rstsql_err:set cn = nothingset rst = nothingend functionpubli

11、c function execute_sql(sql as string) as adodb.recordsetdim cn as adodb.connectiondim rst as adodb.recordseton error goto sql_err:set cn = new adodb.connectioncn.open provider=msdasql;driver=sql server; & _server=fall;database=工资管理系统;uid=;pwd=cn.begintrans cn.execute sql cn.committranssql_err:set cn

12、 = nothingset rst = nothingend function4.4 “登录模块”详细设计 登录模块的详细设计主要是用户登录的一个界面，判断登录的用户是管理员还是普通用户，进入后将面对的是主界面，从而进行相关的操作。登录的界面主要由用户名和密码的信息，还有一些标题设置，确定和取消的按钮组成。通过标题可以知道此系统的大概内容，由此，登录界面的设计完成。private sub cmd_cancel_click() unload meend subprivate sub cmd_ok_click() dim sql as string dim rst as adodb.records

13、etif (trim(txt_key.text) = ) then msgbox 请输入密码, vbokonly + vbexclamation, 提示else sql = select * from 用户表 where 用户名= & trim(cmo_user.text) & set rst = executesql(sql, ) if trim(rst.fields(1) = trim(txt_key.text) then if trim(rst.fields(2) 管理员 then frm_main.tianjiayonghu = false frm_main.shanchuyonghu

14、 = false frm_main.xiugaiyonghu = false frm_main.shezhi.enabled = false frm_main.xinxiweihu.enabled = false frm_main.command2.enabled = false frm_main.command3.enabled = false frm_main.command4.enabled = false frm_main.command5.enabled = false frm_main.command6.enabled = false frm_main.command7.enabl

15、ed = false frm_main.command8.enabled = false end if rst.close username = trim(cmo_user.text) frm_main.show unload me else msgbox 密码不正确，请重新输入, vbokonly + vbexclamation, 警告 txt_key.setfocus txt_key.text = exit sub end if end ifend subprivate sub cmo_user_keypress(keyascii as integer)keyascii = 0end su

16、bprivate sub form_load() dim i as integer dim sql as string dim rst as adodb.recordset sql = select 用户名 from 用户表 set rst = executesql(sql, ) for i = 1 to rst.recordcount cmo_user.additem (rst.fields(0) rst.movenext next iend sub4.5 “主窗体模块”详细设计主窗体设计的界面是用菜单编辑器做的，主要由系统、设置、信息维护和工资查询关于这五个大模块组成。主窗体是进行相关操作

17、的主界面。在这几个里，管理员有所有的权利进行各种操作。private sub anbumenchaxun_click()enabled = falseform13.showend subprivate sub anyuangongchaxun_click()enabled = falseform12.showend subprivate sub command1_click()enabled = falseform12.showend subprivate sub command2_click()enabled = falseform9.showend subprivate sub comma

18、nd3_click()enabled = falsefrm_bumenxinxi.showend subprivate sub command4_click()enabled = falsefrm_jiben.showend subprivate sub command5_click()enabled = falsefrm_gangweigongzi.showend subprivate sub command6_click()enabled = falsefrm_tianjiayushanchu.caption = 添加用户frm_tianjiayushanchu.cmd_delete.en

19、abled = falsefrm_tianjiayushanchu.cmd_alter.enabled = falsefrm_tianjiayushanchu.cmd_adduser.default = truefrm_tianjiayushanchu.showend subprivate sub command7_click()enabled = falsefrm_tianjiayushanchu.caption = 删除用户frm_tianjiayushanchu.cmd_adduser.enabled = falsefrm_tianjiayushanchu.txt_password.en

20、abled = falsefrm_tianjiayushanchu.txt_power.enabled = falsefrm_tianjiayushanchu.cmd_alter.enabled = falsefrm_tianjiayushanchu.cmd_delete.default = truefrm_tianjiayushanchu.showend subprivate sub command8_click()enabled = falsefrm_tianjiayushanchu.caption = 修改用户frm_tianjiayushanchu.txt_username.enabl

21、ed = falsefrm_tianjiayushanchu.cmd_delete.enabled = falsefrm_tianjiayushanchu.cmd_adduser.enabled = falsefrm_tianjiayushanchu.cmd_alter.enabled = truefrm_tianjiayushanchu.showend subprivate sub exit_click()unload meend subprivate sub fafanggongzi_click()enabled = falseform10.showend subprivate sub f

22、orm_load()dim i as integer dim sql as string dim rst as adodb.recordset sql = select 权限 from 用户表 set rst = executesql(sql, ) for i = 1 to rst.recordcount rst.movenext next iend subprivate sub form_unload(cancel as integer)endend subprivate sub gangweigongzi_click()enabled = falsefrm_gangweigongzi.sh

23、owend subprivate sub bumenguanli_click()enabled = falsefrm_bumenxinxi.showend subprivate sub jibengongzi_click()enabled = falsefrm_jiben.showend subprivate sub shanchuyonghu_click()enabled = falsefrm_tianjiayushanchu.caption = 删除用户frm_tianjiayushanchu.cmd_adduser.enabled = falsefrm_tianjiayushanchu.

24、txt_password.enabled = falsefrm_tianjiayushanchu.txt_power.enabled = falsefrm_tianjiayushanchu.cmd_alter.enabled = falsefrm_tianjiayushanchu.cmd_delete.default = truefrm_tianjiayushanchu.showend subprivate sub tianjiayonghu_click()enabled = falsefrm_tianjiayushanchu.caption = 添加用户frm_tianjiayushanch

25、u.cmd_delete.enabled = falsefrm_tianjiayushanchu.cmd_alter.enabled = falsefrm_tianjiayushanchu.cmd_adduser.default = truefrm_tianjiayushanchu.showend subprivate sub xiugaiyonghu_click()enabled = falsefrm_tianjiayushanchu.caption = 修改用户frm_tianjiayushanchu.txt_username.enabled = falsefrm_tianjiayusha

26、nchu.cmd_delete.enabled = falsefrm_tianjiayushanchu.cmd_adduser.enabled = falsefrm_tianjiayushanchu.cmd_alter.enabled = truefrm_tianjiayushanchu.showend subprivate sub yuangongguanli_click()enabled = falseform9.showend sub4.6 “添加、删除、修改模块”详细设计单击主窗体“系统”菜单下面各项，会弹出下面窗体。可进行添加用户、删除用户及修改用户信息的操作。dim msg_sql

27、 as stringdim objrs as adodb.recordsetprivate sub cmd_adduser_click()dim sql as stringdim newrst as adodb.recordsetsql = insert into 用户表 values( & & trim(txt_username.text) & , & & trim(txt_password.text) & , & & trim(txt_power) & & )set newrst = execute_sql(sql)sql = select * from 用户表set newrst = s

28、elect_sql(sql)set datagrid1.datasource = newrsttxt_username.setfocustxt_username.text = txt_password.text = txt_power.text = end subprivate sub cmd_cancel_click()frm_main.enabled = trueunload meend subprivate sub cmd_delete_click() if objrs.recordcount = 0 then msgbox 库中没有记录!elseif objrs.recordcount

29、 0 thenif msgbox(真的要删除该用户?, vbyesno + vbquestion, 警告) = vbyes thenobjrs.deletemsgbox 删除成功!, vbinformation, 提示 end if end if end ifend subprivate sub cmd_alter_click()if trim(txt_password.text) = or trim(txt_power.text) = thenmsgbox 请将修改后的信息填写完整！, vbokonly + vbexclamation, 警告elseif msgbox(确定修改吗？, vby

30、esno + vbexclamation, 警告) = vbyes thenset rst = executesql(update 用户表 set 密码= & trim(txt_password.text) & where 用户名 = & trim(txt_username.text) & , msg_sql)set rst = executesql(update 用户表 set 权限= & trim(txt_power.text) & where 用户名 = & trim(txt_username.text) & , msg_sql)set objrs = executesql(select

31、 * from 用户表, msg_sql)set datagrid1.datasource = objrsmsgbox 修改成功！, vbokonly + vbexclamation, 提示txt_password.setfocustxt_username.text = txt_password.text = txt_power.text = end ifend ifend subprivate sub datagrid1_click()txt_username.text = datagrid1.columns(0)txt_password.text = datagrid1.columns(1

32、)txt_power.text = datagrid1.columns(2)end subprivate sub form_load()set objrs = executesql(select * from 用户表, msg_sql)set datagrid1.datasource = objrsend subprivate sub form_unload(cancel as integer)frm_main.enabled = trueend sub4.7 “基本工资设置模块”详细设计单击主窗体“设置”菜单下的“基本工资”命令，会弹出“基本工资”窗口，可对基本工资的级别和相应金额进行添加、

33、删除与修改。dim objrs as adodb.recordsetdim msg_sql as stringprivate sub cmd_add_click()if combo_level.text then set objrs = executesql(insert into 基本工资 values( + str(combo_level.text) + , + (text_pay.text) + ), msg_sql) set objrs = executesql(select * from 基本工资, msg_sql) set datagrid1.datasource = objrs

34、combo_level.setfocus combo_level.text = text_pay.text = end ifend subprivate sub cmd_alter_click()if trim(combo_level.text) = or trim(text_pay.text) = thenmsgbox 请将修改后的信息填写完整！, vbokonly + vbexclamation, 警告elseif msgbox(确定修改吗？, vbyesno + vbexclamation, 警告) = vbyes thenset rst = executesql(update 基本工资

35、 set 工资级别= & trim(combo_level.text) & where 工资级别 = & trim(combo_level.text) & , msg_sql)set rst = executesql(update 基本工资 set 工资金额= & trim(text_pay.text) & where 工资级别 = & trim(combo_level.text) & , msg_sql)set objrs = executesql(select * from 基本工资, msg_sql)set datagrid1.datasource = objrsmsgbox 修改成功！

36、, vbokonly + vbexclamation, 提示combo_level.text = text_pay.text = end ifend ifend subprivate sub cmd_cancel_click()frm_main.enabled = trueunload meend subprivate sub cmd_delete_click() if objrs.recordcount = 0 then msgbox 库中没有记录! exit sub end if if objrs.recordcount 0 then if msgbox(真的要删除该级别工资信息吗?, v

37、byesno + vbquestion, 警告) = vbyes then objrs.delete msgbox 删除成功!, vbinformation, 提示 combo_level.setfocus combo_level.text = text_pay.text = end if end ifend subprivate sub datagrid1_click()combo_level.text = datagrid1.columns(0)text_pay.text = datagrid1.columns(1)end subprivate sub form_load()set obj

38、rs = executesql(select * from 基本工资, msg_sql)set datagrid1.datasource = objrsend subprivate sub form_unload(cancel as integer)frm_main.enabled = trueend subprivate sub text_pay_lostfocus()if val(text_pay.text) = 10000 then msgbox 数据非法 text_pay.text = text_pay.setfocusend ifend sub4.8 “岗位工资设置模块”详细设计单击

39、主窗体“设置”菜单下的“岗位工资”命令，会弹出“岗位工资”窗口，可对岗位工资的级别和相应金额进行添加、删除与修改。dim objrs as adodb.recordsetdim msg_sql as stringprivate sub cmd_add_click()if combo_level.text then set objrs = executesql(insert into 岗位工资 values( + str(combo_level.text) + , + (text_pay.text) + ), msg_sql) set objrs = executesql(select * fr

40、om 岗位工资, msg_sql) set datagrid1.datasource = objrs combo_level.setfocus combo_level.text = text_pay.text = end ifend subprivate sub cmd_alter_click()if trim(combo_level.text) = or trim(text_pay.text) = thenmsgbox 请将修改后的信息填写完整！, vbokonly + vbexclamation, 警告elseif msgbox(确定修改吗？, vbyesno + vbexclamatio

41、n, 警告) = vbyes thenset rst = executesql(update 岗位工资 set 工资级别= & trim(combo_level.text) & where 工资级别 = & trim(combo_level.text) & , msg_sql)set rst = executesql(update 岗位工资 set 工资金额= & trim(text_pay.text) & where 工资级别 = & trim(combo_level.text) & , msg_sql)set objrs = executesql(select * from 岗位工资, m

42、sg_sql)set datagrid1.datasource = objrsmsgbox 修改成功！, vbokonly + vbexclamation, 提示combo_level.text = text_pay.text = end ifend ifend subprivate sub cmd_cancel_click()frm_main.enabled = trueunload meend subprivate sub cmd_delete_click() if objrs.recordcount = 0 then msgbox 库中没有记录! exit sub end if if o

43、bjrs.recordcount 0 then if msgbox(真的要删除该级别工资信息吗?, vbyesno + vbquestion, 警告) = vbyes then objrs.delete msgbox 删除成功!, vbinformation, 提示 combo_level.setfocus combo_level.text = text_pay.text = end if end ifend subprivate sub datagrid1_click()combo_level.text = datagrid1.columns(0)text_pay.text = datagr

44、id1.columns(1)end subprivate sub form_load()set objrs = executesql(select * from 岗位工资, msg_sql)set datagrid1.datasource = objrsend subprivate sub form_unload(cancel as integer)frm_main.enabled = trueend subprivate sub text_pay_lostfocus()if val(text_pay.text) = 10000 then msgbox 数据非法 text_pay.text =

45、 text_pay.setfocus end ifend sub4.9 “部门信息管理模块”详细设计单击主窗体“信息维护”菜单下的“部门信息管理”命令，会弹出“部门信息管理”窗口，可对部门信息进行添加、删除与修改。dim objrs as adodb.recordsetdim msg_sql as stringprivate sub cmd_add_click()dim sqlbianhao, sqlmingcheng, sqlfuzeren, sqlrenshu as string sqlbianhao = cmo_bianhao.text sqlmingcheng = cmo_mingch

46、eng.text sqlfuzeren = txt_fuzeren.text sqlrenshu = txt_renshu.text if cmo_bianhao.text and cmo_mingcheng.text then if msgbox(确定添加吗？, vbyesno + vbexclamation, 警告) = vbyes then set objrs = executesql(insert into 部门信息 values( + + sqlbianhao + + , + sqlmingcheng + , + sqlfuzeren + , + sqlrenshu + ), msg

47、_sql) set objrs = executesql(select * from 部门信息, msg_sql) set datagrid1.datasource = objrs msgbox 添加成功！, vbokonly + vbexclamation, 提示 cmo_bianhao.setfocuscmo_bianhao.text = cmo_mingcheng.text = txt_fuzeren.text = txt_renshu.text = end if end ifend subprivate sub cmd_alter_click()if trim(cmo_bianhao.

48、text) = or trim(cmo_mingcheng.text) = or trim(txt_fuzeren.text) = or trim(txt_renshu.text) = thenmsgbox 请将修改后的信息填写完整！, vbokonly + vbexclamation, 警告elseif msgbox(确定修改吗？, vbyesno + vbexclamation, 警告) = vbyes thenset rst = executesql(update 部门信息 set 部门名称= & trim(cmo_mingcheng.text) & where 部门编号 = & tri

49、m(cmo_bianhao.text) & , msg_sql)set rst = executesql(update 部门信息 set 部门负责人= & trim(txt_fuzeren.text) & where 部门编号 = & trim(cmo_bianhao.text) & , msg_sql)set rst = executesql(update 部门信息 set 部门人数= & trim(txt_renshu.text) & where 部门编号 = & trim(cmo_bianhao.text) & , msg_sql)set objrs = executesql(selec

50、t * from 部门信息, msg_sql)set datagrid1.datasource = objrsmsgbox 修改成功！, vbokonly + vbexclamation, 提示cmo_bianhao.setfocuscmo_bianhao.text = cmo_mingcheng.text = txt_fuzeren.text = txt_renshu.text = end ifend ifend subprivate sub cmd_cancel_click()frm_main.enabled = trueunload meend subprivate sub cmd_de

51、lete_click() if objrs.recordcount = 0 then msgbox 库中没有记录!elseif objrs.recordcount 0 thenif msgbox(真的要删除该部门?, vbyesno + vbquestion, 警告) = vbyes thenobjrs.deletemsgbox 删除成功!, vbinformation, 提示 end if end if end ifend subprivate sub datagrid1_click()cmo_bianhao.text = datagrid1.columns(0)cmo_mingcheng.text = datagrid1.columns(1)txt_fuzeren.text = datagrid1.columns(2)txt_renshu.text = datagrid1.columns(3)end subprivate sub form_load()set objrs = executesql(select * from 部门信息, msg_sql)set data