家庭物品管理系统(数据库课设)

1、沈阳理工大学课程设计专用纸 摘 要随着现代生活的发展，我国经济迅速发展，人民生活水平越来越好，家家户户都买了各种家电和生活用品，家庭物品也就越来越丰富了。但是，我们的家庭总是有限的，对于这些家庭物品我们需要有一个系统的整理。通过对数据库的学习，学习了一些对数据的整理，对此，为家庭物品做了一个管理系统。用visual basic 6.0作为前端开发工具，利用其提供的集成开发环境及支持面向对象的各种标准化的控件，尤其是对ado的支持完成对数据库的各种操作。使这个系统能够添加、修改、删除家庭各成员与物品的信息相关信息，当进入到系统时，可以直接通过查询用户名或购买日期来浏览物品的信息，登陆到系统之后，

2、就可以对房间和物品信息进行添加、修改删除了，从而完成对家庭物品的整理，使各家庭成员更加方便地了解自己家里的物品信息关键字： 家庭物品 整理 方便0沈阳理工大学课程设计专用纸目 录1 系统功能概述32 数据库设计31.1 需求分析31.2 关系模型31.3 e-r模型41.4表结构设计53系统各功能模块的详细设计73.1主窗体73.2登录93.3查询113.4用户信息133.5物品信息183.6房间信息254参考文献28沈阳理工大学课程设计专用纸1 系统功能概述为该家庭物品管理系统设计一个mdi窗体作为主界面，在菜单栏创建了四个菜单：文件、查询、添加、用户名。（1） 文件中有两个子菜单：登录和退

3、出。这两个子菜单分别控制登录用户和退出整个程序。（2） 查询中有两个子菜单：按用户名查询和按日期查询。按用户名查询可以在用户输入某个用户名之后，显示该用户的物品情况；按日期查找可以在输入一个日期之后，显示该日期之后购买的所有物品。（3） 添加中有三个子菜单：添加用户、添加房间、添加物品。点击添加用户，可以对用户信息进行增删改查；点击添加房间，可以对房间信息进行增删改查操作；点击添加物品，则可以对物品信息进行增删改查操作。（4） 用户名按钮有一个子菜单：注销。用来注销已经成功登录的用户，如没有登录用户，该子菜单显示无用户，成功登录之后即显示已登录用户的名称。2 数据库设计1.1 需求分析 （1)

4、根据家庭成员的身份，为每个成员分配有一个专门的账号。 (2)此家庭物品系统管理能够添加、删除、修改、查询家庭成员的信息以及与房间、物品之间的联系。（3）在进入到家庭物品管理系统之后，不需要登陆就可以直接查询属于某个人的物品信息，也可以查询某个日期之后所购买的物品。（4）当用户成功登录时，菜单栏上能够显示该用户的名称，此时，用户才能对物品信息和家庭成员信息进行添加、修改、删除1.2 关系模型 （1）家庭成员信息表 uuser：username、password、sex、age、birthday、status、profession、hobby、wage （2）物品信息表 item：itemdate

5、、purchasedate、price、usedtime、quantity、publicitem、roomnumber、category （3）房间表 room： roomnumber、roomname （4）归属表 belong：itemname、username、itemnumber 1.3 e-r模型uuserbelongitem 整体e-r图mnnroomsave1图2-1用户信息：usernamehobbyuuserprofessionpasswordbirthdaysexwageagestatus图2-2物品信息：itemroomnumberpurchasedatequantity

6、usedtimepublicitemitemdatapricecategory图2-3房间信息：roomroomnumberroomname图2-4归属信息：belongusernameitemnumberitemname图2-51.4表结构设计uuser表：create table uuser (username varchar(6) primary key, password char(6) not null , sex char(2) check (sex in(男,女), age smallint check (age0 and age= & text1.text & adodc1.r

7、ecordsource = sqlstr3adodc1.refreshdatagrid1.visible = true 显示所查询的结果end subprivate sub command2_click()unload me 退回主窗体end subprivate sub form_load()move (screen.width - width) / 2, (screen.height - height) / 2end sub（2） 按日期查询窗口设计 在用户输入一个用户名之后，在datagrid控件里就会显示此用户的所有物品信息。图3-4代码如下：private sub command1_

8、click()dim sqlstr2 as stringif text1.text = thenmsgbox 请输入查询用户名！, vbdefaultbutton1, 提示exit subend ifsqlstr2 = select * from byuser where username= & text1.text & adodc2.recordsource = sqlstr2adodc2.refreshdatagrid1.visible = trueend subprivate sub command2_click()unload meend subprivate sub form_loa

9、d()move (screen.width - width) / 2, (screen.height - height) / 2end sub3.4用户信息用户窗口设计 从主界面进入用户信息界面后，可以在每个文本框里填写新用户的基本 信息，填写完成后，点击添加按钮，弹出添加成功，即成功添加。也可以在此界面里通过输入用户名查找每个家庭成员的基本信息，可以点击上一个、下一个进行浏览。查找之后对结果也可以进行修改、删除操作。图3-5代码如下：private sub command1_click()if text1.text = or text2.text = thenmsgbox 请输入完整的添加信

10、息！, vbcritical, 提醒else 将输入的信息写进数据库中adodc1.recordset.addnewadodc1.recordset.fields(username) = text1.textadodc1.recordset.fields(password) = text2.textif option1.value = true thenadodc1.recordset.fields(sex) = label6.captionelseadodc1.recordset.fields(sex) = label7.captionend ifadodc1.recordset.field

11、s(birthday) = text3.textadodc1.recordset.fields(age) = text4.textadodc1.recordset.fields(status) = text5.textadodc1.recordset.fields(profession) = text6.textadodc1.recordset.fields(hobby) = text7.textadodc1.recordset.fields(wage) = text8.textadodc1.recordset.updateadodc1.refreshmsgbox 添加用户成功！, vbdef

12、aultbutton1, 提示end ifend subprivate sub command2_click()if text1.text = or text2.text = thenmsgbox 请输入完整的添加信息！, vbcritical, 提醒elseif not text2.text = adodc1.recordset.fields(password) thenmsgbox 密码不正确！您无权修改！, vbcritical, 错误elseif option1.value = true then adodc1.recordset.fields(sex) = label6.captio

13、nelseadodc1.recordset.fields(sex) = label7.captionend ifadodc1.recordset.fields(birthday) = text3.textadodc1.recordset.fields(age) = text4.textadodc1.recordset.fields(status) = text5.textadodc1.recordset.fields(profession) = text6.textadodc1.recordset.fields(hobby) = text7.textif not text8.text = th

14、enadodc1.recordset.fields(wage) = text8.textend ifadodc1.recordset.update 更新后台数据库adodc1.refreshmsgbox 修改成功！, vbdefaultbutton1, 提醒end ifend ifend subprivate sub command3_click()if text1.text = or text2.text = thenmsgbox 请输入完整的添加信息！, vbcritical, 提醒else 判断是否有删除的权限if not text2.text = adodc1.recordset.fi

15、elds(password) thenmsgbox 密码不正确！您无权删除此用户！, vbcritical, 错误elseadodc1.recordset.deleteadodc1.recordset.updateadodc1.refreshtext1.text = text2.text = option1.value = falseoption2.value = falsetext3.text = text4.text = text5.text = text6.text = text7.text = text8.text = msgbox 用户删除成功！, vbdefaultbutton1,

16、 提醒end ifend ifend subprivate sub command4_click()unload meend subprivate sub command5_click()adodc1.recordset.movenextif adodc1.recordset.eof then 判断记录指针的位置 adodc1.recordset.movelast command5.enabled = false command6.enabled = true msgbox 已经是最后一个用户了！, vbdefaultbutton1, 提示 exit sub end iftext1.text

17、= adodc1.recordset.fields(username)text2.text = adodc1.recordset.fields(password)if adodc1.recordset.fields(sex) = 男 thenoption1.value = trueelseoption2.value = trueend iftext3.text = adodc1.recordset.fields(birthday)text4.text = adodc1.recordset.fields(age)text5.text = adodc1.recordset.fields(statu

18、s)text6.text = adodc1.recordset.fields(profession)text7.text = adodc1.recordset.fields(hobby)text8.text = adodc1.recordset.fields(wage)end subprivate sub command6_click()adodc1.recordset.moveprevious 将记录指针向上移 if adodc1.recordset.bof then adodc1.recordset.movefirst command6.enabled = false command5.e

19、nabled = true msgbox 已经是第一个用户了！, vbdefaultbutton1, 提示 exit sub end iftext1.text = adodc1.recordset.fields(username)text2.text = adodc1.recordset.fields(password)if adodc1.recordset.fields(sex) = 男 thenoption1.value = trueelseoption2.value = trueend iftext3.text = adodc1.recordset.fields(birthday)tex

20、t4.text = adodc1.recordset.fields(age)text5.text = adodc1.recordset.fields(status)text6.text = adodc1.recordset.fields(profession)text7.text = adodc1.recordset.fields(hobby)text8.text = adodc1.recordset.fields(wage)if adodc1.recordset.eof thencommand5.enabled = falseend ifend subprivate sub command7

21、_click()command5.enabled = truecommand6.enabled = truedim sql as stringsql = select * from uuserif text1.text = or text2.text = thenmsgbox 请输入用户名和密码！, vbcritical, 提醒exit subend ifadodc1.recordsource = sqladodc1.refreshif adodc1.recordset.bof then msgbox 无此用户,请重新输入, vbexclamation, 警告 text1.text = tex

22、t1.setfocusexit subelse 判断密码是否正确 if not adodc1.recordset.fields(password) = text2.text then msgbox 密码错误，重新输入 , vbexclamation, 警告text2.text = text2.setfocusexit subelseif adodc1.recordset.fields(sex) = 男 thenoption1.value = trueelseoption2.value = trueend iftext3.text = adodc1.recordset.fields(birthd

23、ay)text4.text = adodc1.recordset.fields(age)text5.text = adodc1.recordset.fields(status)text6.text = adodc1.recordset.fields(profession)text7.text = adodc1.recordset.fields(hobby)text8.text = adodc1.recordset.fields(wage)end ifend ifend subprivate sub form_load()move (screen.width - width) / 2, (scr

24、een.height - height) / 2command5.enabled = falsecommand6.enabled = falseend sub3.5物品信息物品窗口设计进入物品信息窗口之后，可以在文本框中输入要添加的物品信息，然后点击添加按钮，弹出添加成功的对话框，即添加物品信息成功！也可以通过输入物品名称、购买日期、是否是公用品、所有者来精确查找某条或某类物品信息。上一个、下一个可以浏览物品信息。修改完信息之后，也可以点击修改按钮来修改此物品信息。点击删除按钮即可直接删除此物品信息。退出即可直接返回主界面。图3-6代码如下：private function setnull()

25、 构造一个置空的函数text1.text = text2.text = text3.text = text4.text = text5.text = text6.text = text7.text = text8.text = combo1.text = end functionprivate sub combo1_lostfocus()if combo1.text = 是 thenlabel9.visible = falsetext8.visible = falseend ifif combo1.text = 否 thenlabel9.visible = truetext8.visible

26、= trueend ifend subprivate sub command1_click()if text1.text = or combo1.text = thenmsgbox 请输入添加信息！, vbcritical, 错误exit subend ifif combo1.text = 是 then adodc1.recordset.addnew 添加物品信息 adodc1.recordset.fields(itemname) = text1.text adodc1.recordset.fields(purchasedate) = text2.text adodc1.recordset.f

27、ields(price) = text3.text adodc1.recordset.fields(usedtime) = text4.text adodc1.recordset.fields(quantity) = text5.text adodc1.recordset.fields(category) = text6.text adodc1.recordset.fields(roomnumber) = text7.text adodc1.recordset.fields(publicitem) = combo1.textadodc1.recordset.updateadodc1.refre

28、shsetnullmsgbox 添加成功！, vbdefaultbutton1, 提示exit subend ifif combo1.text = 否 thendim sqls as stringsqls = select * from itemadodc1.recordsource = sqlsadodc1.recordset.addnew adodc1.recordset.fields(itemname) = text1.text adodc1.recordset.fields(purchasedate) = text2.text adodc1.recordset.fields(price

29、) = text3.text adodc1.recordset.fields(usedtime) = text4.text adodc1.recordset.fields(quantity) = text5.text adodc1.recordset.fields(category) = text6.text adodc1.recordset.fields(roomnumber) = text7.text adodc1.recordset.fields(publicitem) = combo1.textadodc1.recordset.updateadodc1.refreshdim sql a

30、s stringsql7 = select * from belongadodc1.recordsource = sql7adodc1.recordset.addnewadodc1.recordset.fields(itemname) = text1.textadodc1.recordset.fields(username) = text8.textadodc1.recordset.fields(itemnumber) = text5.textadodc1.recordset.updateadodc1.refreshsetnullmsgbox 添加成功！, vbdefaultbutton1,

31、提示exit subend ifend subprivate sub command2_click()if combo1.text = 是 then adodc1.recordset.fields(itemname) = text1.text adodc1.recordset.fields(purchasedate) = text2.text adodc1.recordset.fields(price) = text3.text adodc1.recordset.fields(usedtime) = text4.text adodc1.recordset.fields(quantity) =

32、text5.text adodc1.recordset.fields(category) = text6.text adodc1.recordset.fields(roomnumber) = text7.text adodc1.recordset.fields(publicitem) = combo1.textadodc1.recordset.updateadodc1.refreshsetnull 调用置空函数msgbox 修改成功！, vbdefaultbutton1, 提示exit subend ifend subprivate sub command3_click()adodc1.rec

33、ordset.delete 删除物品信息adodc1.recordset.updateadodc1.refreshmsgbox 删除成功！, vbdefaultbutton1, 提示end subprivate sub command4_click()unload meend subprivate sub command5_click()command6.enabled = truecommand7.enabled = trueif not text1.text = thendim sql1 as stringsql1 = select * from item where itemname=

34、& text1.text & adodc1.recordsource = sql1adodc1.refreshtext2.text = adodc1.recordset.fields(purchasedate)text3.text = adodc1.recordset.fields(price)text4.text = adodc1.recordset.fields(usedtime)text5.text = adodc1.recordset.fields(quantity)text6.text = adodc1.recordset.fields(category)text7.text = a

35、dodc1.recordset.fields(roomnumber)combo1.text = adodc1.recordset.fields(publicitem)if combo1.text = 否 thentext8.text = adodc1.recordset.fields(username)end ifexit subend ifif not text2.text = thendim sql2 as stringsql2 = select * from items where purchasedate= & text2.text & adodc1.recordsource = sq

36、l2adodc1.refreshtext1.text = adodc1.recordset.fields(itemname)text3.text = adodc1.recordset.fields(price)text4.text = adodc1.recordset.fields(usedtime)text5.text = adodc1.recordset.fields(quantity)text6.text = adodc1.recordset.fields(category)text7.text = adodc1.recordset.fields(roomnumber)combo1.te

37、xt = adodc1.recordset.fields(publicitem)if combo1.text = 否 thentext8.text = adodc1.recordset.fields(username)end ifexit subend ifif not text6.text = thendim sql3 as stringsql3 = select * from items where category= & text6.text & adodc1.recordsource = sql3adodc1.refreshtext1.text = adodc1.recordset.f

38、ields(itemname)text2.text = adodc1.recordset.fields(purchasedate)text3.text = adodc1.recordset.fields(price)text4.text = adodc1.recordset.fields(usedtime)text5.text = adodc1.recordset.fields(quantity)text7.text = adodc1.recordset.fields(roomnumber)combo1.text = adodc1.recordset.fields(publicitem)if

39、combo1.text = 否 thentext8.text = adodc1.recordset.fields(username)end ifexit subend ifif not text8.text = thendim sql4 as stringsql4 = select * from items where username= & text8.text & adodc1.recordsource = sql4adodc1.refreshtext1.text = adodc1.recordset.fields(itemname)text2.text = adodc1.recordse

40、t.fields(purchasedate)text3.text = adodc1.recordset.fields(price)text4.text = adodc1.recordset.fields(usedtime)text5.text = adodc1.recordset.fields(quantity)text6.text = adodc1.recordset.fields(category)text7.text = adodc1.recordset.fields(roomnumber)combo1.text = adodc1.recordset.fields(publicitem)

41、if combo1.text = 否 thentext8.text = adodc1.recordset.fields(username)end ifexit subend ifif combo1.text = 是 thendim sql5 as stringsql5 = select * from item where publicitem = & combo1.text & adodc1.recordsource = sql5adodc1.refreshtext1.text = adodc1.recordset.fields(itemname)text2.text = adodc1.rec

42、ordset.fields(purchasedate)text3.text = adodc1.recordset.fields(price)text4.text = adodc1.recordset.fields(usedtime)text5.text = adodc1.recordset.fields(quantity)text6.text = adodc1.recordset.fields(category)text7.text = adodc1.recordset.fields(roomnumber)exit subend ifif combo1.text = 否 thendim sql

43、6 as stringsql6 = select * from items where publicitem = & combo1.text & adodc1.recordsource = sql6adodc1.refreshtext1.text = adodc1.recordset.fields(itemname)text2.text = adodc1.recordset.fields(purchasedate)text3.text = adodc1.recordset.fields(price)text4.text = adodc1.recordset.fields(usedtime)text5.text = adodc1.recordset.fields(quantity)text6.text = adodc1.recordset.fields(category)text7.text = adodc1.recordset.fields(roomnumber)text8.text = adodc1.recordset.fields(username)exit subend ifend subprivate sub command6_click()ad