1、 摘 要隨著現代生活的發展，我國經濟迅速發展，人民生活水平越來越好，家家戶戶都買了各種家電和生活用品，家庭物品也就越來越豐富了。但是，我們的家庭總是有限的，對于這些家庭物品我們需要有一個系統的整理。通過對數據庫的學習，學習了一些對數據的整理，對此，為家庭物品做了一個管理系統。用Visual Basic 6.0作為前端開發工具，利用其提供的集成開發環境及支持面向對象的各種標準化的控件，尤其是對ADO的支持完成對數據庫的各種操作。使這個系統能夠添加、修改、刪除家庭各成員與物品的信息相關信息，當進入到系統時，可以直接通過查詢用戶名或購買日期來瀏覽物品的信息，登陸到系統之后，就可以對房間和物品信息進行

2、添加、修改刪除了，從而完成對家庭物品的整理，使各家庭成員更加方便地了解自己家里的物品信息關鍵字： 家庭物品 整理 方便目 錄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參考文獻281 系統功能概述為該家庭物品管理系統設計一個MDI窗體作為主界面，在菜單欄創建了四個菜單：文件、查詢、添加、用戶名。（1） 文件中有兩個子菜單：登錄和退出。這兩個子菜單分別控制登錄用戶和退出整個程序。（2） 查詢中有兩個子菜單：按用

3、戶名查詢和按日期查詢。按用戶名查詢可以在用戶輸入某個用戶名之后，顯示該用戶的物品情況；按日期查找可以在輸入一個日期之后，顯示該日期之后購買的所有物品。（3） 添加中有三個子菜單：添加用戶、添加房間、添加物品。點擊添加用戶，可以對用戶信息進行增刪改查；點擊添加房間，可以對房間信息進行增刪改查操作；點擊添加物品，則可以對物品信息進行增刪改查操作。（4） 用戶名按鈕有一個子菜單：注銷。用來注銷已經成功登錄的用戶，如沒有登錄用戶，該子菜單顯示無用戶，成功登錄之后即顯示已登錄用戶的名稱。2 數據庫設計1.1 需求分析 （1)根據家庭成員的身份，為每個成員分配有一個專門的賬號。 (2)此家庭物品系統管理能

4、夠添加、刪除、修改、查詢家庭成員的信息以及與房間、物品之間的聯系。（3）在進入到家庭物品管理系統之后，不需要登陸就可以直接查詢屬于某個人的物品信息，也可以查詢某個日期之后所購買的物品。（4）當用戶成功登錄時，菜單欄上能夠顯示該用戶的名稱，此時，用戶才能對物品信息和家庭成員信息進行添加、修改、刪除1.2 關系模型 （1）家庭成員信息表 Uuser：userName、password、sex、age、birthday、status、profession、hobby、wage （2）物品信息表 Item：itemDate、purchaseDate、price、usedTime、quantity、pu

5、blicItem、roomNumber、category （3）房間表 Room： roomNumber、roomName （4）歸屬表 Belong：itemName、userName、itemNumber 1.3 E-R模型UuserBelongItem 整體E-R圖mnnRoomsave1圖2-1用戶信息：userNamehobbyUuserprofessionpasswordbirthdaysexwageagestatus圖2-2物品信息：ItemroomNumberpurchaseDatequantityusedTimepublicItemitemDatapricecategory圖

6、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.RecordSource = sqlstr3Adodc1.RefreshDataG

7、rid1.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_Click()Dim sqlstr2 As StringIf Text1.Tex

8、t = 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_Load()Move (Screen.Width - Width) / 2, (Scr

9、een.Height - Height) / 2End Sub3.4用戶信息用戶窗口設計 從主界面進入用戶信息界面后，可以在每個文本框里填寫新用戶的基本 信息，填寫完成后，點擊添加按鈕，彈出添加成功，即成功添加。也可以在此界面里通過輸入用戶名查找每個家庭成員的基本信息，可以點擊上一個、下一個進行瀏覽。查找之后對結果也可以進行修改、刪除操作。圖3-5代碼如下：Private Sub Command1_Click()If Text1.Text = Or Text2.Text = ThenMsgBox 請輸入完整的添加信息！, vbCritical, 提醒Else 將輸入的信息寫進數據庫中Adodc

10、1.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.Fields(birthday) = Text3.TextAdodc1.Recordset

11、.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 添加用戶成功！, vbDefaultButton1, 提示End IfEnd SubPrivate Sub

12、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.CaptionElseAdodc1.Recordset.Fields(sex) = Labe

13、l7.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 = ThenAdodc1.Recordset.Fields(wage) = Text8.

14、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.Fields(password) ThenMsgBox 密碼不正確！您無權刪除此用戶

15、！, 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, 提醒End IfEnd IfEnd SubPrivate Sub Comman

16、d4_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 = Adodc1.Recordset.Fields(userName)Text2

17、.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(status)Text6.Text = Adodc1.Recordset.Fields(p

18、rofession)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.Enabled = True MsgBox 已經是第一個用戶了！, vbDefau

19、ltButton1, 提示 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)Text4.Text = Adodc1.Recordset.Fields(age)Te

20、xt5.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_Click()Command5.Enabled = TrueCommand6.

21、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 = Text1.SetFocusExit SubElse 判斷密碼是否正確 If Not

22、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(birthday)Text4.Text = Adodc1.Recordset.Fields(

23、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, (Screen.Height - Height) / 2Command5.Enabled

24、 = FalseCommand6.Enabled = FalseEnd Sub3.5物品信息物品窗口設計進入物品信息窗口之后，可以在文本框中輸入要添加的物品信息，然后點擊添加按鈕，彈出添加成功的對話框，即添加物品信息成功！也可以通過輸入物品名稱、購買日期、是否是公用品、所有者來精確查找某條或某類物品信息。上一個、下一個可以瀏覽物品信息。修改完信息之后，也可以點擊修改按鈕來修改此物品信息。點擊刪除按鈕即可直接刪除此物品信息。退出即可直接返回主界面。圖3-6代碼如下：Private Function setNull() 構造一個置空的函數Text1.Text = Text2.Text = Text

25、3.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 = TrueEnd IfEnd SubPrivate Sub Command1_

26、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.Fields(price) = Text3.Text Adodc1.Records

27、et.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.RefreshsetNullMsgBox 添加成功！, vbDefaultButton1,

28、 提示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) = Text3.Text Adodc1.Recordset.Fields(u

29、sedTime) = 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 As Stringsql7 = select * from BelongAdodc

30、1.RecordSource = sql7Adodc1.Recordset.AddNewAdodc1.Recordset.Fields(itemName) = Text1.TextAdodc1.Recordset.Fields(userName) = Text8.TextAdodc1.Recordset.Fields(itemNumber) = Text5.TextAdodc1.Recordset.UpdateAdodc1.RefreshsetNullMsgBox 添加成功！, vbDefaultButton1, 提示Exit SubEnd IfEnd SubPrivate Sub Comma

31、nd2_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) = Text5.Text Adodc1.Recordset.Fields(categ

32、ory) = 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.Recordset.Delete 刪除物品信息Adodc1.Recordset.Upd

33、ateAdodc1.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= & Text1.Text & Adodc1.RecordSource = sql

34、1Adodc1.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 = Adodc1.Recordset.Fields(roomnumber)Combo1

35、.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 = sql2Adodc1.RefreshText1.Text = Adodc1.Reco

36、rdset.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.Text = Adodc1.Recordset.Fields(publicItem)

37、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.Fields(itemName)Text2.Text = Adodc1.Recor

38、dset.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 Combo1.Text = 否 ThenText8.Text = Adodc1.

39、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.Recordset.Fields(purchaseDate)Text3.Text = Adodc

40、1.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)If Combo1.Text = 否 ThenText8.Text = Adod

41、c1.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.Recordset.Fields(purchaseDate)Text3.Text =

42、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 sql6 As Stringsql6 = Select * From items Wh

43、ere 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()Adodc1.Recordset.MoveNextIf Adodc1.Recordset.EOF