VB开发小 型 书 店 管 理 系 统

某某学院计算机科学与技术系课程设计报告课 程 名 SQL Server 2005 数据库管理与开发教程 设计题目 小 型 书 店 管 理 系 统 班 级 08级本科(2)班 专 业 计算机科学与技术 姓 名 张世俊 指导教师 孙海霞 2011年6月16日制一设计任务1.利用Visual Basic 6.0和microsoft SQL server 2005建立“小型书店管理系统”2.查询购买书的数量超过10本的客户信息。3.按照输入图书名称和购买的月份，来查询该图书当月销售情况（总数量，总金额）任务分配：何洋洋 负责小型书店管理系统需求分析、逻辑结构设计(ER图)。 洪锐 负责图书管理系统功能结构图设计。 万磊 负责图书管理系统物理结构设计(创建数据库)。 张世俊 负责图书管理系统的VB界面与代码设计，并完成测试二完成任务的思路、方法与算法： 首先完成“小型图书管理系统”的需求分析，在此基础上完成E-R模型设计，再次设计数据库，完成系统数据库的物理结构设计，建立相应的数据库；然后使用VB连接数据库，设计登录界面、主界面、以及系统主要功能界面，最后编写代码完成系统设计要求，并作测试、最总完成整个图书管理系统设计三程序框图：1.系统功能结构图：2.系统E-R图：该图书管理系统的E-R图如下所示：四核心程序（对主要的语句要加注释)1.登陆界面代码： Public Function conn() As ADODB.Connection On Error GoTo 1 Set conn = New ADODB.Connection conn.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63 Exit Function1: MsgBox Err.Description End End Function Private Sub Command1_Click() If Trim(Text1.Text) And Trim(Text2.Text) Then Dim rs As New Recordset rs.Open select username from tb_user where username= & Me.Text1 & and password = & Me.Text2 & , conn, adOpenKeyset, adLockOptimistic If Not rs.EOF Then Frm_Main.Show Unload Me Else MsgBox 用户名或密码不正确, vbCritical End If rs.Close Set rs = Nothing Else MsgBox 请输入用户名和密码, vbCritical End If End Sub Private Sub Command2_Click() Unload Me 卸载当前窗体 End Sub2. 主窗体代码： Private Sub about_Click() Dim ab As New Frm_about ab.Show End Sub Private Sub add_tsinfo_Click() Dim ad As New Frm_addbookinfo ad.Show End Sub Private Sub de_tsinfo_Click() Dim de As New Frm_deletebookinfo de.Show End Sub Private Sub Exit_Click() Unload Me End Sub Private Sub gs10_Click() Dim gs10 As New Frm_gmcg10 gs10.Show End Sub Private Sub modify_tsinfo_Click() Dim mm As New Frm_modifybookinfo mm.Show End Sub Private Sub search_gmzinfo_Click() Dim sgz As New Frm_searchgmzinfo sgz.Show End Sub Private Sub search_monthxs_Click() Dim smx As New Frm_searchmonthxs smx.Show End Sub Private Sub search_tsinfo_Click() Dim ss As New Frm_searchinfo ss.Show End Sub Private Sub search_xsinfo_Click() Dim sx As New Frm_searchxsinfo sx.Show End Sub3. 个子窗体代码： Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset Private Sub Command1_Click() Dim d As Double d = Text3.Text rs.AddNew rs.Fields(图书编号) = Text1.Text rs.Fields(图书名称) = Text2.Text rs.Fields(单价) = d rs.Update Set MSHFlexGrid1.DataSource = rs End Sub Private Sub Command2_Click() Unload Me End Sub Private Sub Form_Load() con.OpenProvider=SQLOLEDB.1;IntegratedSecurity=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select * from 图书信息表, con, adOpenKeyset, adLockOptimistic, adCmdTextSet MSHFlexGrid1.DataSource = rsrs.UpdateEnd Sub Private Sub Command1_Click() rs.Close If Option1.Value = True Then rs.Open delete from 图书信息表 where 图书编号= & Text1.Text & , con, adOpenKeyset, adLockOptimistic, adCmdText Else If Option2.Value = True Then rs.Open delete from 图书信息表 where 图书名称= & Text1.Text & , con, adOpenKeyset, adLockOptimistic, adCmdText End If End If rs.Open select * from 图书信息表 , con, adOpenKeyset, adLockOptimistic, adCmdText rs.Update Set MSHFlexGrid1.DataSource = rs rs.Close End Sub Private Sub Command2_Click() Unload Me End Sub Private Sub Form_Load() con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63 rs.Open select * from 图书信息表, con, adOpenKeyset, adLockOptimistic, adCmdText Set MSHFlexGrid1.DataSource = rs rs.Close End Sub Private Sub Option1_Click() Option1.Value = True End Sub Private Sub Option2_Click() Option2.Value = True End Sub Private Sub Command1_Click() rs.Update rs.Close rs.Open select * from 图书信息表 where 图书编号= + Text2.Text + , con, adOpenKeyset, adLockOptimistic, adCmdTextDim a As Doublea = Text3.Textrs.Fields(图书名称) = Text1.Textrs.Fields(单价) = aIf Trim(Text1.Text) And Trim(Text3.Text) Thenrs.UpdateText1.Text = Text3.Text = End Ifrs.UpdateSet MSHFlexGrid1.DataSource = rsEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Command3_Click()rs.Updaters.Closers.Open select * from 图书信息表 where 图书编号= + Text2.Text + , con, adOpenKeyset, adLockOptimistic, adCmdTextrs.UpdateSet MSHFlexGrid1.DataSource = rs End SubPrivate Sub Form_Load() con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select * from 图书信息表, con, adOpenKeyset, adLockOptimistic, adCmdTextSet MSHFlexGrid1.DataSource = rsrs.UpdateEnd SubPrivate Sub Command1_Click()rs.Closers.Open select * from 购买者信息表 where 购买者编号= + Text1.Text + or 姓名= + Text2.Text + , con, adOpenKeyset, adLockOptimistic, adCmdTextSet MSHFlexGrid1.DataSource = rsrs.UpdateEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Form_Load()con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select * from 购买者信息表, con, adOpenKeyset, adLockOptimistic, adCmdTextSet MSHFlexGrid1.DataSource = rsEnd SubPrivate Sub Command1_Click()Dim aa As Integeraa = Text2.Textrs.Closers.Open select SUM (数量) as 总数量,SUM(单价*数量)as 总金额 from 查书当月销售情况表 where 图书名称= & Text1.Text & and 购买时间 in(select 购买时间 from 查书当月销售情况表 where DATEPART (MM,购买时间)= & aa & ), con, adOpenKeyset, adLockOptimistic, adCmdTextrs.UpdateSet MSHFlexGrid1.DataSource = rsrs.UpdateEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Form_Load()con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select * from 查书当月销售情况表, con, adOpenKeyset, adLockOptimistic, adCmdTextSet MSHFlexGrid1.DataSource = rsEnd SubPrivate Sub Command1_Click()rs.Closers.Open select * from 图书信息表 where 图书名称= + Text1.Text + or 图书编号= + Text2.Text + , con, adOpenKeyset, adLockOptimistic, adCmdText Set MSHFlexGrid1.DataSource = rsrs.UpdateEnd SubPrivate Sub Command2_Click()Unload MeEnd SubPrivate Sub Form_Load()con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select * from 图书信息表, con, adOpenKeyset, adLockOptimistic, adCmdTextSet MSHFlexGrid1.DataSource = rsEnd Sub Private Sub Command1_Click() rs.Close rs.Open select * from 销售信息表 where 购买者编号= + Text1.Text + or 图书编号= + Text2.Text + or 购买时间= + Text3.Text + , con, adOpenKeyset, adLockOptimistic, adCmdText Set MSHFlexGrid1.DataSource = rs rs.Update End Sub Private Sub Command2_Click() Unload Me End Sub Private Sub Form_Load() con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select * from 销售信息表, con, adOpenKeyset, adLockOptimistic, adCmdText Set MSHFlexGrid1.DataSource = rs End Sub Private Sub Command1_Click() Unload Me End Sub Private Sub Form_Load() con.Open Provider=SQLOLEDB.1 ;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=小型书店管理系统;Data Source=F301-63rs.Open select 购买者信息表.购买者编号,姓名,联系方式, 购书总量 from 购书逾10者信息查询视图vie,购买