vb+access图书管理系统.docx
《vb+access图书管理系统.docx》由会员分享,可在线阅读,更多相关《vb+access图书管理系统.docx(41页珍藏版)》请在冰豆网上搜索。
![vb+access图书管理系统.docx](https://file1.bdocx.com/fileroot1/2022-12/7/e7722db4-dce4-465e-bbb7-fd78546fee30/e7722db4-dce4-465e-bbb7-fd78546fee301.gif)
vb+access图书管理系统
图书管理系统
●需求分析
●分为超级用户跟普通用户,普通用户只有查询图书信息功能
《图书管理系统》主要实现的功能:
⏹用户信息模块
◆用户添加
◆用户修改
◆用户删除
◆用户检索
用户编码userid
用户姓名username
用户班级class
用户密码password
⏹图书类别信息模块
◆图书类别添加
◆图书类别修改
◆图书类别删除(下架)
图书类别编码bookid
图书类别名称bookkind
备注remark
⏹图书信息模块
◆图书添加
◆图书修改
◆图书删除(下架)
◆图书检索
项目
图书条码ISBN
图书名称bookname
图书作者author
图书出版社publish
图书简介content
图书单价piece
图书本数number
图书剩余数remain
图书类别bookkind
图书所在位置position
⏹图书借还管理模块
◆借书信息添加记录
◆还书信息添加记录
借或还mode
学生学号studentid
图书条码isbn
图书名称bookname
借还日期date
●数据库建模
⏹抽象概念模型
◆实体(Entity)
●用户信息
●图书类别信息
●图书信息
●图书借还信息
◆关系(relation)
●图书类别和图书之间存在一对多的关系
⏹数据库系统建表
◆Mysql的数据建表工具实现表对象的创建
◆通过纯sql脚本进行创建
●框架搭建
技术选型
本来是用mysql+vb的(sql脚本建表好屌的样子),结果XX好久都没找到正确的连接方法,只好改为Access+VB(c/s)
Debug/release(图书管理系统.exe)
⏹Mysql建表
--创建数据库
Createdatabasetest;
Usetest;
--创建用户信息表
createtablet_user(
useridvarchar(20)notnullprimarykey,
usernamevarchar(20)notnull,
classvarchar(30)notnull,
passwordvarchar(20)notnull);
insertintot_uservalues('1','管理员','admin','123456');
insertintot_uservalues('201110020123','尹雄辉','信计11101班','123456');
select*fromt_user;
+--------------+--------+-------------+----------+
|userid|username|class|password|
+--------------+--------+-------------+----------+
|1|管理员|admin|123456|
|201110020123|尹雄辉|信计11101班|123456|
+--------------+--------+-------------+----------+
--创建图书类别表
createtablet_bookkind(
bookidvarchar(20)notnullprimarykey,
bookkindvarchar(30)notnull,
remarkvarchar(200));
insertintot_bookkindvalues('1001','计算机类','');
insertintot_bookkindvalues('1002','数学类','');
select*fromt_bookkind;
+--------+----------+--------+
|bookid|bookkind|remark|
+--------+----------+--------+
|1001|计算机类||
|1002|数学类||
+--------+----------+--------+
--创建图书信息表
createtablet_book(
isbnvarchar(30)notnullprimarykey,
booknamevarchar(30)notnull,
authorvarchar(30)notnull,
publishvarchar(40)notnull,
contentvarchar(200)notnull,
piecevarchar(10)notnull,
numberint(3)notnull,
remainint(3)notnull,
bookkindvarchar(30)notnull,
Positionvarchar(30)notnull);
insertintot_bookvalues('10001','C语言程序设计','alice','huas','......','25','5','5','计算机类',’T200.201’);
insertintot_bookvalues('10002','ACM程序设计','bob','huas','......','30','5','5','计算机类',,’T201.102’);
Select*fromt_book;
+------+--------------+-------+--------+--------+------+-------+-------+---------+--------+
|isbn|bookname|author|publish|content|piece|number|remain|bookkind|position|
+------+--------------+-------+--------+--------+------+-------+-------+---------+--------+
|10001|C语言程序设计|alice|huas|......|25|5|5|计算机类|T200.201|
|10002|ACM程序设计|bob|huas|......|30|5|5|计算机类|T201.102|
+------+--------------+-------+--------+--------+------+-------+-------+---------+--------+
--创建借还信息表
createtablet_form(
modevarchar(10)notnull,
Studentidvarchar(20)notnullprimarykey,
isbnvarchar(20)notnull,
booknamevarchar(30)notnull,
datedatenotnull);
insertintot_formvalues('借','10001','C语言程序设计',curdate());
insertintot_formvalues('还','10002','ACM程序设计',curdate());
select*fromt_form;
+------+-----------------------+---------------+------------+
|mode|isbn|studentid|bookname|date|
+------+-----------------------+---------------+------------+
|还|10002|201110020123|ACM程序设计|2014-06-17|
|借|10001|201110020123|C语言程序设计|2014-06-17|
+------+-----------------------+---------------+------------+
⏹代码编写
◆登录界面
OptionExplicit
DimcntAsInteger'记录确定次数
PrivateSubCommand1_Click()
DimsqlAsString
Dimrs_loginAsNewADODB.Recordset
IfTrim(Text1.Text)=""Then'判断输入的用户名是否为空
MsgBox"用户名不能为空",vbOKOnly+vbExclamation,""
Text1.Text=""
Text2.Text=""
Text1.SetFocus
Else
sql="select*fromt_userwhereuserid='"&Text1.Text&"'"
rs_login.Opensql,con1,adOpenKeyset,adLockPessimistic
Ifrs_login.EOF=TrueThen
MsgBox"没有这个用户",vbOKOnly+vbExclamation,""
Text1.Text=""
Text2.Text=""
Text1.SetFocus
Else'检验密码是否正确
IfTrim(rs_login.Fields(3))=Trim(Text2.Text)Then
userid=Text1.Text
password=rs_login.Fields(3)
rs_login.Close
UnloadMe
menu0.Show
Else
MsgBox"密码不正确",vbOKOnly+vbExclamation,""
Text2.Text=“”
Text2.SetFocus
EndIf
EndIf
EndIf
cnt=cnt+1
Ifcnt=3Then
rs_login.Close
UnloadMe
EndIf
ExitSub
EndSub
PrivateSubCommand2_Click()
UnloadMe
EndSub
PrivateSubForm_Load()
con1.Open"provider=Microsoft.Jet.oledb.4.0;"&"datasource=t_user.mdb"
con2.Open"provider=Microsoft.Jet.oledb.4.0;"&"datasource=t_bookkind.mdb"
con3.Open"provider=Microsoft.Jet.oledb.4.0;"&"datasource=t_book.mdb"
con4.Open"provider=Microsoft.Jet.oledb.4.0;"&"datasource=t_form.mdb"
cnt=0
EndSub
◆修改密码
PrivateSubCommand1_Click()
Dimrs_changAsNewADODB.Recordset
DimsqlAsString
IfTrim(Text1.Text)<>Trim(Text2.Text)Then
MsgBox"密码不一致!
",vbOKOnly+vbExclamation,""
Text1.SetFocus
Text1.Text=""
Text2.Text=""
Else
sql="select*fromt_userwhereuserid='"&userid&"'"
rs_chang.Opensql,con1,adOpenKeyset,adLockPessimistic
rs_chang.Fields(3)=Text1.Text
rs_chang.Update
rs_chang.Close
MsgBox"密码修改成功",vbOKOnly+vbExclamation,""
UnloadMe
EndIf
EndSub
PrivateSubCommand2_Click()
UnloadMe
EndSub
PrivateSubForm_Load()
EndSub
◆超级用户菜单
◆添加用户
DimiAsLong
PrivateSubCommand1_Click()
DimsqlAsString
Dimrs_addAsNewADODB.Recordset
IfTrim(Text1(0).Text)=""Then
MsgBox"userid不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1(0).SetFocus
ElseIfTrim(Text1
(1).Text)=""Then
MsgBox"username不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1
(1).SetFocus
ElseIfTrim(Text1
(2).Text)=""Then
MsgBox"class不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1
(2).SetFocus
ElseIfTrim(Text1(3).Text)=""Then
MsgBox"password不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1(3).SetFocus
Else
sql="select*fromt_user"
rs_add.Opensql,con1,adOpenKeyset,adLockPessimistic
While(rs_add.EOF=False)
IfTrim(rs_add.Fields(0))=Trim(Text1(0).Text)Then
MsgBox"已有这个用户",vbOKOnly+vbExclamation,""
Text1(0).SetFocus
Fori=0To3
Text1(i).Text=""
Nexti
ExitSub
Else
rs_add.MoveNext
EndIf
Wend
rs_add.AddNew
Fori=0To3
rs_add.Fields(i)=Text1(i).Text
Nexti
rs_add.Update
rs_add.Close
MsgBox"添加用户成功",vbOKOnly+vbExclamation,""
Fori=0To3
Text1(i).Text=""
Nexti
Text1(0).SetFocus
EndIf
EndSub
PrivateSubCommand2_Click()
UnloadMe
EndSub
PrivateSubForm_Load()
EndSub
◆修改用户信息
DimflagAsBoolean
DimiAsLong
DimsqlAsString
Dimrs_changeAsNewADODB.Recordset
PrivateSubCommand1_Click()
IfTrim(Text1
(1).Text)=""Then
MsgBox"username不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1
(1).SetFocus
ElseIfTrim(Text1
(2).Text)=""Then
MsgBox"class不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1
(2).SetFocus
ElseIfTrim(Text1(3).Text)=""Then
MsgBox"password不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1(3).SetFocus
Else
Fori=1To3
rs_change.Fields(i)=Text1(i).Text
Nexti
rs_change.Update
rs_change.Close
MsgBox"修改用户信息成功",vbOKOnly+vbExclamation,""
Fori=0To3
Text1(i).Text=""
Nexti
Text1(0).SetFocus
flag=False
EndIf
ExitSub
EndSub
PrivateSubCommand2_Click()
UnloadMe
EndSub
PrivateSubCommand3_Click()
IfTrim(Text1(0).Text)=""Then
MsgBox"学号不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1(0).SetFocus
Else
sql="select*fromt_user"
rs_change.Opensql,con1,adOpenKeyset,adLockPessimistic
DoWhile(rs_change.EOF=False)
IfTrim(rs_change.Fields(0))=Trim(Text1(0).Text)Then
flag=True
ExitDo
Else
rs_change.MoveNext
EndIf
Loop
Ifflag=TrueThen
Fori=1To3
Text1(i).Text=rs_change.Fields(i)
Nexti
Else
MsgBox"不存在该用户",vbOKOnly+vbExclamation,""
Fori=0To3
Text1(i).Text=""
Nexti
Text1(0).SetFocus
rs_change.Close
EndIf
EndIf
ExitSub
EndSub
PrivateSubForm_Load()
flag=False
EndSub
◆删除用户
DimsqlAsString
DimflagAsBoolean
Dimrs_deleteAsNewADODB.Recordset
PrivateSubCommand1_Click()
IfTrim(Text1.Text)=""Then
MsgBox"学号不能为空",vbOKOnly+vbExclamation,""
ExitSub
Text1.SetFocus
Else
sql="select*fromt_user"
rs_delete.Opensql,con1,adOpenKeyset,adLockPessimistic
DoWhile(rs_delete.EOF=False)
IfTrim(rs_delete.Fields(0))=Trim(Text1.Text)Then
flag=True
ExitDo
Else
rs_delete.MoveNext
EndIf
Loop
Ifflag=TrueThen
rs_delete.Delete
rs_delete.Update
rs_delete.Close
MsgBox"删除成功",vbOKOnly+vbExclamation,""
Text1.Text=""
flag=False
Else
MsgBox"不存在该用户",vbOKOnly+vbExclamation,""
rs_delete.Close
Text1.Text=""
EndIf
EndIf
EndSub
PrivateSubCommand2_Click()
UnloadMe
EndSub
PrivateSubForm_Load()
flag=False
EndSub
PrivateSubForm_Load()
flag=False
DimconnectionstringAsString
connectionstring="provider=Microsoft.Jet.oledb.4.0;"&_
"datasource=t_user.mdb"
conn.Openconnectionstring
EndSub
◆查询用户信息
DimflagAsBoolean
DimsqlAsString
Dimrs_queryAsNewADODB.Recordset
PrivateSubCommand1_Click()
UnloadMe
EndSub
PrivateSubCommand3_Click()
DimiAsLong
IfTrim(Text1(0).Text)=""Then
MsgBox"学号不能为空",vbOKOnly+vbExclamation,""
Else
sql="select*fromt_user"
rs_query.Opensql,con1,adOpenKeyset,adLockPessimistic
DoWh