基于VB及SQLSERVER的管理系统实训指导书.doc

基于VB及SQLSERVER的管理系统实训指导书计算机软件技术专业适用大连职业技术学院信息技术系计算机软件技术教研室实验（训）一 连接数据库及调用存储过程（15学时）一、创建数据库及其表1新建个人信息管理系统数据库“Pinformation”。2建表Contacts、ContactPhones、Reminders表。(1) Contacts（联系人表）字段名字段数据类型主键标识列说明ContactIDIntYesYes联系人编号NameVarchar(50)联系人姓名AddressVarchar(100)联系人所在城市ZipCodeVarchar(20)邮编EmailAddressVarchar(50)电子邮箱ContactNoteVarchar(50)备注(2) ContactPhones（联系人电话表）字段名字段数据类型主键标识列说明ContactPhoneIDIntYesYes联系人电话编号ContactIDInt联系人编号PhoneTypeVarchar(50)电话类型PhoneNumberVarchar(50)电话号码(3) Reminders（提示信息表）字段名字段数据类型主键标识列说明ReminderIDIntYesYes联系人电话编号AlarmOnBit报警标识AlarmTimedatetime报警时间ReminderNoteVarchar(1000)提示内容二、在VB中查看数据库对象新建工程，在VB界面中，执行“视图”菜单的“数据视图窗口”菜单项，或者在ADO控件属性中操作，创建数据库的连接。1在图一的属性页中选择“通用”选项卡，选择“使用连接字符串”，点击“生成”按钮，弹出图二。 2在图二中选择“提供者”选项卡，选择为SQL Server提供的数据驱动，点击“下一步”按钮，弹出图三。 （图一）（图二） （图三）3在图三中根据SQL Server的用户设置情况，确定使用的登录服务器方式；服务器的名称，取决于是网络访问还图三是本地访问，如果为本地访问，则不必输入任何信息；如网络访问必须输入服务器的名称；然后再选择要访问的数据库名称，最后点击“测试连接”按钮，如弹出图四所示对话框，则证明测试数据库连接成功，点击每一层的“确定”按钮即可。 （图四） （图五） 三、设计窗体界面 如图五、图六所示。四、设计应用程序代码1添加模块Module1。 '显示声明变量Option Explicit '登录用户账号Public sqluserneme As String '登录用户密码Public sqlpassword As String'数据库连接对象 （图六）Public wy As New ADODB.Connection 2工程启动窗体form1的设计。 l 窗体加载事件：Private Sub Form_Load() '为连接对象设置连接字符串，并打开。wy.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI; " _& "Persist Security Info=False;Initial Catalog=pinformation; " _& "Data source=" wy.OpenEnd Sub注意：代码折行的写法。l “下一步”按钮的单击事件：Private Sub Command1_Click() Form2.Show '显示窗体Form2 Unload Me '卸载窗体Form1End Sub3创建存储过程。 l 功能：在contacts表中添加一条记录CREATE PROCEDURE contactadd name varchar(50), address varchar(100), zipcode varchar(20), emailaddress varchar(50), contactnote varchar(50) AS begin insert into contacts (name,address,zipcode,emailaddress,contactnote) values (name,address,zipcode,emailaddress,contactnote) select identity as thenewid endreturnl 功能：在contacts表中查询联系人编号合姓名，并按姓名排序CREATE PROCEDURE contactlist AS begin select contactid,name from contacts order by name endreturnl 功能：在contacts表中查询联系人的一条记录CREATE PROCEDURE contactrecord contactid integer AS begin if contactid=0 begin select * from contacts where contactid=(select min(contactid) from contacts) end else begin select * from contacts where contactid=contactid end endreturnl 功能：在contacts表中对一条记录进行更改CREATE PROCEDURE contactupdate contactid integer,name varchar(50), address varchar(100),zipcode varchar(20), emailaddress varchar(50),contactnote varchar(500) AS begin update contacts set name=name,address=address, zipcode=zipcode,emailaddress=emailaddress, contactnote=contactnote where contactid=contactid endreturnl 功能：在contacts表中删除一条记录CREATE PROCEDURE deletecontact contactid integer AS begin delete from contacts where contactid=contactid endreturnl 功能：在ContactPhones 表中添加一条电话记录CREATE PROCEDURE contactphoneadd contactid integer, phonetype varchar(50), phonenumber varchar(50) AS begin insert into contactphones (contactid,phonetype,phonenumber) values (contactid,phonetype,phonenumber) endreturnl 功能：在ContactPhones 表中删除一条电话记录CREATE PROCEDURE contactphonedelete contactphoneid integer AS begin delete from contactphones where contactphoneid=contactphoneid endreturnl 功能：在ContactPhones 表中查询电话记录CREATE PROCEDURE contactphonelist contactid integer AS begin select contactphoneid,phonetype+'-'+phonenumber as thephone from contactphones where contactid=contactid order by phonetype+'-'+phonenumber endreturn4创建触发器。CREATE TRIGGER deletecontacts ON contacts after DELETE AS begin delete from contactphones where contactid=(select contactid from deleted) end5form2的代码设计。（1）变量的定义：Option ExplicitPrivate currentid As Long '当前联系人编号（2）用户自定义子程序：² 显示一条联系人的记录Public Sub displaycontact(id2use As Long) Dim rscontact As ADODB.Recordset Set rscontact = wy.Execute("exec Contactrecord " _ & id2use) If rscontact.EOF Then Command4_Click Else currentid = rscontact("contactid") Text1.Text = rscontact("name") Text2.Text = rscontact("address") Text3.Text = rscontact("zipcode") Text4.Text = rscontact("emailaddress") Text5.Text = rscontact("contactnote") displayphonelist End IfEnd Sub² 查询联系人姓名和编号，并添加到下拉列表框中Public Sub updatenamecombo() Dim rsnames As ADODB.Recordset Set rsnames = wy.Execute("exec contactlist") cmbcontacts.Clear Do Until rsnames.EOF cmbcontacts.AddItem rsnames("name") cmbcontacts.ItemData(cmbcontacts.NewIndex) = rsnames("contactid") rsnames.MoveNext LoopEnd Sub² 读取联系人的电话号码，并添加到列表框中Public Sub displayphonelist() Dim rsphonelist As ADODB.Recordset List1.Clear Set rsphonelist = wy.Execute("exec contactphonelist " & currentid) Do Until rsphonelist.EOF List1.AddItem rsphonelist("thephone") List1.ItemData(List1.NewIndex) = rsphonelist("contactphoneid") rsphonelist.MoveNext LoopEnd Sub² Cmbcontacts的单击事件Private Sub cmbcontacts_click() displaycontact cmbcontacts.ItemData(cmbcontacts.ListIndex)End Sub² Command3 “添加”按钮的单击事件Private Sub Command3_Click() Dim rsaddnew As ADODB.Recordset Set rsaddnew = wy.Execute("exec contactadd " _ & "'" & Text1.Text & "'," _ & "'" & Text2.Text & "'," _ & "'" & Text3.Text & "'," _ & "'" & Text4.Text & "'," _ & "'" & Text5.Text & "'") cmbcontacts.AddItem Text1.TextEnd Sub² Command4 “修改”按钮的单击事件Private Sub Command4_Click() If currentid = 0 Then Command3_Click Else wy.Execute "exec contactupdate " & currentid & "," _ & "'" & Text1.Text & "'," _ & "'" & Text2.Text & "'," _ & "'" & Text3.Text & "'," _ & "'" & Text4.Text & "'," _ & "'" & Text5.Text & "'" End If updatenamecomboEnd Sub² Command5 “删除”按钮的单击事件Private Sub Command5_Click() If currentid = 0 Then Command6_Click Else wy.Execute "exec deletecontact " _ & currentid updatenamecombo displaycontact 0 End IfEnd Sub² Command6 “清除”按钮的单击事件Private Sub Command6_Click() currentid = 0 Text1.Text = "" Text2.Text = "" Text3.Text = "" Text4.Text = "" Text5.Text = ""End Sub² Command8 “退出”按钮的单击事件Private Sub Command8_Click() Unload MeEnd Sub实验（训）二 创建数据库报表（15学时）一、添加数据报表设计器1如图七所示。添加标签、文本框线控件。在文本框的DataField属性中添加相应标签所对应数据库中contacts表中的数据字段。2在数据库“Pinformation”中创建存储过程contactreportCREATE PROCEDURE contactreport contactid intASSELECT dbo.contacts0110.name0110, dbo.contacts0110.address0110, dbo.contacts0110.zipcode0110, dbo.contacts0110.emailaddress0110, dbo.contacts0110.contactnote0110FROM dbo.contactphones0110 left outer JOIN dbo.contacts0110 ON dbo.contactphones0110.contactid0110 = dbo.contacts0110.contactid0110where dbo.contacts0110.contactid0110=contactidreturn3Form2中的代码设计：Command7 “报表”按钮的单击事件Private Sub Command7_Click() Dim rs As New ADODB.Recordset If currentid = 0 Then Exit Sub rs.Open "select * from contacts where contactid=" & currentid & "", wy, adOpenStatic, adLockReadOnly If rs.RecordCount > 0 Then Set DataReport1.DataSource = rs DataReport1.Show 1 Else rs.Close End IfEnd Sub4运行9