vb+access图书管理系统.docx

1、vb+access图书管理系统图书管理系统 需求分析 分为超级用户跟普通用户，普通用户只有查询图书信息功能图书管理系统主要实现的功能： 用户信息模块 用户添加 用户修改 用户删除 用户检索用户编码 userid用户姓名 username用户班级 class用户密码 password 图书类别信息模块 图书类别添加 图书类别修改 图书类别删除（下架）图书类别编码 bookid图书类别名称 bookkind备注 remark 图书信息模块 图书添加 图书修改 图书删除（下架） 图书检索项目图书条码ISBN图书名称bookname图书作者author图书出版社publish图书简介content图书

2、单价piece图书本数number图书剩余数remain图书类别bookkind图书所在位置position 图书借还管理模块 借书信息添加记录 还书信息添加记录借或还 mode学生学号studentid图书条码 isbn图书名称 bookname借还日期 date 数据库建模 抽象概念模型 实体（Entity） 用户信息 图书类别信息 图书信息 图书借还信息 关系（relation） 图书类别和图书之间存在一对多的关系 数据库系统建表 Mysql的数据建表工具实现表对象的创建 通过纯sql脚本进行创建 框架搭建技术选型本来是用mysql+vb的(sql脚本建表好屌的样子），结果XX好久都没找

3、到正确的连接方法，只好改为Access+ VB (c/s) Debug/release(图书管理系统.exe) Mysql建表-创建数据库Create database test;Use test;-创建用户信息表create table t_user(userid varchar(20) not null primary key,username varchar(20) not null,class varchar(30) not null,password varchar(20) not null);insert into t_user values(1,管理员,admin,123456)

4、;insert into t_user values(201110020123,尹雄辉,信计11101班,123456);select *from t_user;+-+-+-+-+| userid |username| class | password |+-+-+-+-+| 1 | 管理员 | admin | 123456 | 201110020123 | 尹雄辉 | 信计11101班| 123456 |+-+-+-+-+-创建图书类别表create table t_bookkind(bookid varchar(20) not null primary key,bookkind varch

5、ar(30) not null,remark varchar(200);insert into t_bookkind values(1001,计算机类,);insert into t_bookkind values(1002,数学类,);select *from t_bookkind;+-+-+-+| bookid | bookkind | remark |+-+-+-+| 1001 | 计算机类 | | 1002 | 数学类 | |+-+-+-+-创建图书信息表create table t_book(isbn varchar(30) not null primary key,bookname

6、 varchar(30) not null,author varchar(30) not null,publish varchar(40) not null,content varchar(200) not null,piece varchar(10) not null,number int(3) not null,remain int(3) not null,bookkind varchar(30) not null,Position varchar(30) not null);insert into t_book values(10001,C语言程序设计,alice,huas,.,25,5

7、,5,计算机类,T200.201);insert into t_book values(10002,ACM程序设计,bob,huas,.,30,5,5,计算机类,T201.102);Select *from t_book;+-+-+-+-+-+-+-+-+-+-+| isbn | bookname | author| publish| content| piece| number| remain| bookkind|position|+-+-+-+-+-+-+-+-+-+-+| 10001| C语言程序设计| alice | huas | . | 25 | 5 | 5 | 计算机类 |T200

8、.201| 10002| ACM程序设计 | bob | huas | . | 30 | 5 | 5 | 计算机类 |T201.102|+-+-+-+-+-+-+-+-+-+-+-创建借还信息表create table t_form(mode varchar(10) not null,Studentid varchar(20) not null primary key,isbn varchar(20) not null,bookname varchar(30) not null,date date not null);insert into t_form values(借,10001,C语言程

9、序设计,curdate();insert into t_form values(还,10002,ACM程序设计,curdate();select *from t_form;+-+-+-+-+| mode | isbn | studentid | bookname | date |+-+-+-+-+| 还 | 10002 | 201110020123 | ACM程序设计 | 2014-06-17 | 借 | 10001 | 201110020123 | C语言程序设计 | 2014-06-17 |+-+-+-+-+ 代码编写 登录界面Option ExplicitDim cnt As Integ

10、er 记录确定次数Private Sub Command1_Click()Dim sql As StringDim rs_login As New ADODB.RecordsetIf Trim(Text1.Text) = Then 判断输入的用户名是否为空 MsgBox 用户名不能为空, vbOKOnly + vbExclamation, Text1.Text = Text2.Text = Text1.SetFocusElse sql = select * from t_user where userid= & Text1.Text & rs_login.Open sql, con1, adO

11、penKeyset, adLockPessimistic If rs_login.EOF = True Then MsgBox 没有这个用户, vbOKOnly + vbExclamation, Text1.Text = Text2.Text = Text1.SetFocus Else 检验密码是否正确 If Trim(rs_login.Fields(3) = Trim(Text2.Text) Then userid = Text1.Text password = rs_login.Fields(3) rs_login.Close Unload Me menu0.Show Else MsgBo

12、x 密码不正确, vbOKOnly + vbExclamation, Text2.Text = “” Text2.SetFocus End If End IfEnd Ifcnt = cnt + 1If cnt = 3 Then rs_login.Close Unload MeEnd IfExit SubEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Form_Load()con1.Open provider=Microsoft.Jet.oledb.4.0; & data source=t_user.mdbcon2.O

13、pen provider=Microsoft.Jet.oledb.4.0; & data source=t_bookkind.mdbcon3.Open provider=Microsoft.Jet.oledb.4.0; & data source=t_book.mdbcon4.Open provider=Microsoft.Jet.oledb.4.0; & data source=t_form.mdbcnt = 0End Sub 修改密码Private Sub Command1_Click()Dim rs_chang As New ADODB.RecordsetDim sql As Strin

14、gIf Trim(Text1.Text) Trim(Text2.Text) Then MsgBox 密码不一致！, vbOKOnly + vbExclamation, Text1.SetFocus Text1.Text = Text2.Text = Else sql = select * from t_user where userid= & userid & rs_chang.Open sql, con1, adOpenKeyset, adLockPessimistic rs_chang.Fields(3) = Text1.Text rs_chang.Update rs_chang.Clos

15、e MsgBox 密码修改成功, vbOKOnly + vbExclamation, Unload MeEnd IfEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Form_Load()End Sub 超级用户菜单 添加用户Dim i As LongPrivate Sub Command1_Click()Dim sql As StringDim rs_add As New ADODB.RecordsetIf Trim(Text1(0).Text) = Then MsgBox userid不能为空, vbOKOnly

16、+ vbExclamation, Exit Sub Text1(0).SetFocusElseIf Trim(Text1(1).Text) = Then MsgBox username不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(1).SetFocusElseIf Trim(Text1(2).Text) = Then MsgBox class不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(2).SetFocusElseIf Trim(Text1(3).Text) = Then MsgBox passw

17、ord不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(3).SetFocusElse sql = select * from t_user rs_add.Open sql, con1, adOpenKeyset, adLockPessimistic While (rs_add.EOF = False) If Trim(rs_add.Fields(0) = Trim(Text1(0).Text) Then MsgBox 已有这个用户, vbOKOnly + vbExclamation, Text1(0).SetFocus For i = 0 To 3

18、 Text1(i).Text = Next i Exit Sub Else rs_add.MoveNext End If Wend rs_add.AddNew For i = 0 To 3 rs_add.Fields(i) = Text1(i).Text Next i rs_add.Update rs_add.Close MsgBox 添加用户成功, vbOKOnly + vbExclamation, For i = 0 To 3 Text1(i).Text = Next i Text1(0).SetFocusEnd IfEnd SubPrivate Sub Command2_Click()U

19、nload MeEnd SubPrivate Sub Form_Load()End Sub 修改用户信息Dim flag As BooleanDim i As LongDim sql As StringDim rs_change As New ADODB.RecordsetPrivate Sub Command1_Click()If Trim(Text1(1).Text) = Then MsgBox username不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(1).SetFocusElseIf Trim(Text1(2).Text) = The

20、n MsgBox class不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(2).SetFocusElseIf Trim(Text1(3).Text) = Then MsgBox password不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(3).SetFocusElse For i = 1 To 3 rs_change.Fields(i) = Text1(i).Text Next i rs_change.Update rs_change.Close MsgBox 修改用户信息成功, vbOKOnly

21、 + vbExclamation, For i = 0 To 3 Text1(i).Text = Next i Text1(0).SetFocus flag = FalseEnd IfExit SubEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Command3_Click()If Trim(Text1(0).Text) = Then MsgBox 学号不能为空, vbOKOnly + vbExclamation, Exit Sub Text1(0).SetFocusElse sql = select * from

22、 t_user rs_change.Open sql, con1, adOpenKeyset, adLockPessimistic Do While (rs_change.EOF = False) If Trim(rs_change.Fields(0) = Trim(Text1(0).Text) Then flag = True Exit Do Else rs_change.MoveNext End If Loop If flag = True Then For i = 1 To 3 Text1(i).Text = rs_change.Fields(i) Next i Else MsgBox

23、不存在该用户, vbOKOnly + vbExclamation, For i = 0 To 3 Text1(i).Text = Next i Text1(0).SetFocus rs_change.Close End IfEnd IfExit SubEnd SubPrivate Sub Form_Load()flag = FalseEnd Sub 删除用户Dim sql As StringDim flag As BooleanDim rs_delete As New ADODB.RecordsetPrivate Sub Command1_Click()If Trim(Text1.Text)

24、= Then MsgBox 学号不能为空, vbOKOnly + vbExclamation, Exit Sub Text1.SetFocusElse sql = select * from t_user rs_delete.Open sql, con1, adOpenKeyset, adLockPessimistic Do While (rs_delete.EOF = False) If Trim(rs_delete.Fields(0) = Trim(Text1.Text) Then flag = True Exit Do Else rs_delete.MoveNext End If Loo

25、p If flag = True Then 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 = End IfEnd IfEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Form_Load()fl

26、ag = FalseEnd SubPrivate Sub Form_Load()flag = FalseDim connectionstring As Stringconnectionstring = provider=Microsoft.Jet.oledb.4.0; & _ data source=t_user.mdbconn.Open connectionstringEnd Sub 查询用户信息Dim flag As BooleanDim sql As StringDim rs_query As New ADODB.RecordsetPrivate Sub Command1_Click()Unload MeEnd SubPrivate Sub Command3_Click()Dim i As LongIf Trim(Text1(0).Text) = Then MsgBox 学号不能为空, vbOKOnly + vbExclamation, Else sql = select * from t_user rs_query.Open sql, con1, adOpenKeyset, adLockPessimistic Do Wh