影片管理系统-数据库管理系统

一、设计目标与要求1、课程设计目的：探讨如何开发数据库应用系统2、课程设计要求：初步认识和了解ADO.NET；掌握ADO.NET存取SQLSever2000数据库数据的基本方法；掌握VB.NET操作数据库的方法。二、课程设计的应用背景及实际工作流程1、应用背景电影协会租借电影给学生所用2、工作流程需求分析、概念设计、逻辑设计、数据库实施阶段、调试与测试3、开发环境SQLSever2000数据库、MicrosoftVisualStudio2005编程语言：T-SQL，vb三、数据库设计1、需求分析根据影片管理的特点，影片管理系统应具有如下基本功能：（1）能对系统用户进行管理；（2）能对租借学生数据进行管理，即可以对租借学生数据进行查询、增加、删除、修改操作；（3）可对影片数据进行管理，即可对影片数据进行查询、增加、删除、修改操作；（4）对影片数据的查询可以普通查询、按种类查询、按名称查询。（5）可对租借影片数据进行管理，即可对租借影片数据进行查询、增加、删除操作；（6）对租借影片数据的查询可以按学号、影片编号、影片种类查询。2、概念设计图1

影片管理系统的E-R简图3、逻辑设计创建“影片管理”数据库：打开企业管理器，创建数据库创建“用户”表：图2

用户表创建“租借学生信息”表：图4

租借学生信息表属性创建“影片信息”表：图5

创建影片信息表创建“影片租借信息”表：图6

创建影片租借信息表四、应用系统设计1、系统功能模块结构图图6

影片管理系统功能模块图2、主要模块的代码实现及关键部分的算法说明1.公共模块设计ModuleModule1PublicflagAsIntegerPublicflagReaderAsIntegerPublicflagBookAsIntegerPublicflagBorrowAsIntegerSubmian()flag=0EndSubEndModule2.主控模块设计图8

主控界面的设计主控界面的代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm1PublicSubForm1_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.LoadIfflag=0ThenMenuItem2.Enabled=FalseMenuItem3.Enabled=FalseMenuItem4.Enabled=FalseElseMenuItem2.Enabled=TrueMenuItem3.Enabled=TrueMenuItem4.Enabled=TrueEndIfEndSubPrivateSubMenuItem1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem1.ClickDimobjChildAsNewForm2()objChild.MdiParent=MeobjChild.frm=MeobjChild.Show()EndSubPrivateSubMenuItem5_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem5.ClickflagReader=1DimobjChildAsNewForm3()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem6_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem6.ClickflagReader=2DimobjChildAsNewForm3()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem7_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem7.ClickflagReader=3DimobjChildAsNewForm3()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem8_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem8.ClickflagReader=4DimobjChildAsNewForm3()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem9_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem9.ClickflagBook=1DimobjChildAsNewForm4()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem10_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem10.ClickflagBook=2DimobjChildAsNewForm4()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem11_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem11.ClickflagBook=3DimobjChildAsNewForm4()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem13_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem13.ClickflagBook=4DimobjChildAsNewForm4()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem14_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem14.ClickDimobjChildAsNewForm7()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem15_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem15.ClickDimobjChildAsNewForm8()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem16_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem16.ClickflagBorrow=1DimobjChildAsNewForm5()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem17_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem17.ClickflagBorrow=2DimobjChildAsNewForm5()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem19_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem19.ClickflagBorrow=3DimobjChildAsNewForm5()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem20_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem20.ClickflagBorrow=4DimobjChildAsNewForm5()objChild.MdiParent=MeobjChild.Show()EndSubPrivateSubMenuItem21_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMenuItem21.ClickDimobjChildAsNewForm6()objChild.MdiParent=MeobjChild.Show()EndSubEndClass3.登录模块设计（1）登录界面设计图9

登录界面的设计（2）主要代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm2PublicfrmAsForm1PrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=123;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from用户where用户名='"&Trim(TextBox1.Text)&"'"&"and密码='"&Trim(TextBox2.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"用户")mytable=mySet.Tables("用户")Ifmytable.Rows.Count>0Thenflag=1frm.Form1_Load(NewObject,NewSystem.EventArgs)Close()ElseMsgBox("密码不正确，请重新输入!")TextBox1.Text=""TextBox2.Text=""EndIfEndSub4.租借学生信息管理模块设计（1）租借学生信息管理添加、修改、删除、查询界面设计图10

租借学生信息管理界面设计（2）主要代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm3PrivateSubForm3_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.LoadSelectCaseflagReaderCase1Button1.Text="添加"Case2Button1.Text="修改"Case3Button1.Text="删除"Case4Button1.Text="查询"EndSelectDisplay()EndSubPrivateSubDisplay()DimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringDimiAsIntegersql="Select*from租借学生信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息")mytable=mySet.Tables("租借学生信息")DataGrid1.DataSource=mySet.Tables("租借学生信息")Fori=0Tomytable.Rows.Count-1ComboBox1.Items.Add(mytable.Rows(i).Item(0))NextEndSubPrivateSubComboBox1_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBox1.SelectedIndexChangedDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from租借学生信息where学号='"&Trim(ComboBox1.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息1")mytable=mySet.Tables("租借学生信息1")Ifmytable.Rows(0).Item(0)=ComboBox1.TextThenTextBox1.Text=mytable.Rows(0).Item(1)TextBox2.Text=mytable.Rows(0).Item(2)TextBox3.Text=mytable.Rows(0).Item(3)EndIfEndSubPrivateSubDataGrid1_Navigate(ByValsenderAsSystem.Object,ByValneAsSystem.Windows.Forms.NavigateEventArgs)HandlesDataGrid1.NavigateDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from租借学生信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息2")mytable=mySet.Tables("租借学生信息2")DimiAsIntegerFori=0Tomytable.Rows.Count-1IfDataGrid1.IsSelected(i)ThenComboBox1.Text=mytable.Rows(i).Item(0)TextBox1.Text=mytable.Rows(i).Item(1)TextBox2.Text=mytable.Rows(i).Item(2)TextBox3.Text=mytable.Rows(i).Item(3)EndIfNextEndSubPrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringDimiAsIntegerDimStudentID,name,department,claAsStringSelectCaseflagReaderStudentID=ComboBox1.TextComboBox1.Items.Add(Trim(StudentID))name=TextBox1.Textdepartment=TextBox2.Textcla=TextBox3.Textsql="Select*from租借学生信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息3")mytable=mySet.Tables("租借学生信息3")Fori=0Tomytable.Rows.Count-1Ifmytable.Rows(i).Item(0)=StudentIDThenMsgBox("这个学号已存在！",vbOKOnly+vbExclamation,"警告")ExitSubEndIfNextsql="insertinto租借学生信息(学号,姓名,系别,班级)values("&"'"&StudentID&"','"&name&"','"&department&"','"&cla&"')"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"save")DataGrid1.DataSource=mySet.Tables("save")Display()MsgBox("数据已添加！",vbOKOnly+vbExclamation,"提示")name=TextBox1.Textdepartment=TextBox2.Textcla=TextBox3.Textsql="Select*from租借学生信息where学号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息3")mytable=mySet.Tables("租借学生信息3")Ifmytable.Rows.Count=0ThenMsgBox("这个学号不存在，不能修改！")ExitSubEndIfsql="update租借学生信息set姓名='"&name&"',系别='"&department&"',班级='"&cla&"'where学号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")DataGrid1.DataSource=mySet.Tables("update")Display()MsgBox("数据已修改！",vbOKOnly+vbExclamation,"提示")sql="Select*from租借学生信息where学号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息3")mytable=mySet.Tables("租借学生信息3")Ifmytable.Rows.Count=0ThenMsgBox("这个学号不存在，不能删除！")ExitSubEndIfsql="deletefrom租借学生信息where学号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"delete")DataGrid1.DataSource=mySet.Tables("delete")Display()MsgBox("数据已删除！",vbOKOnly+vbExclamation,"提示")Case4MsgBox("请直接通过选择相应的记录进行查询！")EndSelectEndSub5.影片信息管理模块设计（1）影片信息管理添加、修改、删除、查询界面设计图11

影片信息管理界面设计（2）主要代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm4PrivateSubForm4_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.LoadSelectCaseflagBookCase1Button1.Text="添加"Case2Button1.Text="修改"Case3Button1.Text="删除"Case4Button1.Text="查询"EndSelectDisplay()EndSubPrivateSubDisplay()DimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringDimiAsIntegersql="Select*from影片信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息")mytable=mySet.Tables("影片信息")DataGrid1.DataSource=mySet.Tables("影片信息")ComboBox1.Items.Clear()Fori=0Tomytable.Rows.Count-1ComboBox1.Items.Add(mytable.Rows(i).Item(0))NextEndSubPrivateSubComboBox1_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBox1.SelectedIndexChangedDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from影片信息where影片编号='"&Trim(ComboBox1.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息1")mytable=mySet.Tables("影片信息1")Ifmytable.Rows(0).Item(0)=ComboBox1.TextThenTextBox1.Text=mytable.Rows(0).Item(1)TextBox2.Text=""+mytable.Rows(0).Item(2)TextBox3.Text=mytable.Rows(0).Item(3)TextBox4.Text=mytable.Rows(0).Item(4)TextBox5.Text=""+mytable.Rows(0).Item(5)TextBox6.Text=""+mytable.Rows(0).Item(6)EndIfEndSubPrivateSubDataGrid1_Navigate(ByValsenderAsSystem.Object,ByValneAsSystem.Windows.Forms.NavigateEventArgs)HandlesDataGrid1.NavigateDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from影片信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息2")mytable=mySet.Tables("影片信息2")DimiAsIntegerFori=0Tomytable.Rows.Count-1IfDataGrid1.IsSelected(i)ThenComboBox1.Text=mytable.Rows(i).Item(0)TextBox1.Text=mytable.Rows(i).Item(1)TextBox2.Text=""+mytable.Rows(i).Item(2)TextBox3.Text=mytable.Rows(i).Item(3)TextBox4.Text=mytable.Rows(i).Item(4)TextBox5.Text=""+mytable.Rows(i).Item(5)TextBox6.Text=""+mytable.Rows(i).Item(6)EndIfNextEndSubPrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringDimiAsIntegerDimbookID,bookName,booksSpecies,publishingTime,authorAsStringDimpriceAsDoubleDimstorageAsIntegerSelectCaseflagBookbookID=ComboBox1.TextComboBox1.Items.Add(Trim(bookID))bookName=TextBox1.TextbooksSpecies=TextBox2.Textprice=CDbl(TextBox3.Text)storage=CInt(TextBox4.Text)publishingTime=TextBox5.Textauthor=TextBox6.Textsql="Select*from影片信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息3")mytable=mySet.Tables("影片信息3")Fori=0Tomytable.Rows.Count-1Ifmytable.Rows(i).Item(0)=bookIDThenMsgBox("这个影片编号已存在！",vbOKOnly+vbExclamation,"警告")ExitSubEndIfNextIfTrim(TextBox2.Text)=""Thensql="insert影片信息(影片编号,影片名称,影片种类,价格,库存量,上映时间,导演)values('"&Trim(ComboBox1.Text)&"','"&Trim(TextBox1.Text)&"',NULL,"&Trim(TextBox3.Text)&","&Trim(TextBox4.Text)&",NULL,NULL)"Elsesql="insert影片信息(影片编号,影片名称,影片种类,价格,库存量,上映时间,导演)values('"&Trim(ComboBox1.Text)&"','"&Trim(TextBox1.Text)&"','"&Trim(TextBox2.Text)&"',"&Trim(TextBox3.Text)&","&Trim(TextBox4.Text)&",NULL,NULL)"EndIfmydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"save")mytable=mySet.Tables("影片信息3")IfTrim(TextBox5.Text)<>""Thensql="update影片信息set上映时间='"&Trim(TextBox5.Text)&"'where影片编号='"&bookID&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"save")EndIfIfTrim(TextBox6.Text)<>""Thensql="update影片信息set导演='"&Trim(TextBox6.Text)&"'where影片编号='"&bookID&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"save")EndIfDataGrid1.DataSource=mySet.Tables("save")Display()MsgBox("数据已添加！",vbOKOnly+vbExclamation,"提示")sql="update影片信息set影片名称='"&TextBox1.Text&"',价格='"&price&"',库存量='"&storage&"'where影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")IfTrim(TextBox2.Text)<>""Thensql="update影片信息set影片种类='"&TextBox2.Text&"'where影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")ElseIfTrim(TextBox2.Text)=""Thensql="update影片信息set影片种类=NULLwhere影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")EndIfIfTrim(TextBox5.Text)<>""Thensql="update影片信息set上映时间='"&TextBox5.Text&"'where影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")ElseIfTrim(TextBox5.Text)=""Thensql="update影片信息set上映时间=NULLwhere影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")EndIfIfTrim(TextBox6.Text)<>""Thensql="update影片信息set导演='"&TextBox6.Text&"'where影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")ElseIfTrim(TextBox6.Text)=""Thensql="update影片信息set导演=NULLwhere影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"update")EndIfDataGrid1.DataSource=mySet.Tables("update")Display()MsgBox("数据已修改！",vbOKOnly+vbExclamation,"提示")sql="delete

from影片信息

where影片编号='"&ComboBox1.Text&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"delete")DataGrid1.DataSource=mySet.Tables("delete")Display()MsgBox("数据已删除！",vbOKOnly+vbExclamation,"提示")（3）影片信息管理按种类查询界面设计图12

影片信息管理按种类查询界面设计（4）影片信息管理按种类查询主要代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm7PrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringIfTrim(TextBox1.Text)=""ThenMsgBox("按影片种类查询时，影片种类不能为空！")ExitSubEndIfsql="Select*from影片信息where影片种类like'%"&Trim(TextBox1.Text)&"%'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"按种类查询")mytable=mySet.Tables("按种类查询")DataGrid1.DataSource=mySet.Tables("按种类查询")EndSubPrivateSubButton2_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton2.ClickClose()EndSubEndClass（5）影片信息管理按名称查询界面设计图13

影片信息管理按名称查询界面设计（6）影片信息管理按名称查询主要代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm8PrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringIfTrim(TextBox1.Text)=""ThenMsgBox("按影片名称查询时，影片名称不能为空！")ExitSubEndIfsql="Select*from影片信息where影片名称like'%"&Trim(TextBox1.Text)&"%'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"按名称查询")mytable=mySet.Tables("按名称查询")DataGrid1.DataSource=mySet.Tables("按名称查询")EndSubPrivateSubButton2_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton2.ClickClose()EndSubEndClass6.影片租借管理模块设计（1）影片租借管理添加、删除、查询界面设计图14

影片租借管理界面设计（2）主要代码ImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm5PrivateSubForm5_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.LoadSelectCaseflagBorrowCase1Button1.Text="添加"Case2Button1.Text="删除"PrivateSubDisplay()DimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=123;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringDimiAsIntegersql="Select*from影片租借信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片租借信息")mytable=mySet.Tables("影片租借信息")DataGrid1.DataSource=mySet.Tables("影片租借信息")sql="Select*from租借学生信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息")mytable=mySet.Tables("租借学生信息")ComboBox1.Items.Clear()Fori=0Tomytable.Rows.Count-1ComboBox1.Items.Add(mytable.Rows(i).Item(0))Nextsql="Select*from影片信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息")mytable=mySet.Tables("影片信息")ComboBox2.Items.Clear()Fori=0Tomytable.Rows.Count-1ComboBox2.Items.Add(mytable.Rows(i).Item(0))NextEndSubPrivateSubComboBox1_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBox1.SelectedIndexChangedDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=123;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from租借学生信息where学号='"&Trim(ComboBox1.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息1")mytable=mySet.Tables("租借学生信息1")Ifmytable.Rows(0).Item(0)=ComboBox1.TextThenTextBox1.Text=mytable.Rows(0).Item(1)TextBox2.Text=mytable.Rows(0).Item(2)TextBox3.Text=mytable.Rows(0).Item(3)EndIfEndSubPrivateSubComboBox2_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBox2.SelectedIndexChangedDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=123;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from影片信息where影片编号='"&Trim(ComboBox2.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息1")mytable=mySet.Tables("影片信息1")Ifmytable.Rows(0).Item(0)=ComboBox2.TextThenTextBox4.Text=mytable.Rows(0).Item(1)TextBox5.Text=mytable.Rows(0).Item(2)TextBox6.Text=mytable.Rows(0).Item(3)TextBox7.Text=mytable.Rows(0).Item(4)TextBox8.Text=mytable.Rows(0).Item(5)TextBox9.Text=mytable.Rows(0).Item(6)EndIfEndSubPrivateSubDataGrid1_Navigate(ByValsenderAsSystem.Object,ByValneAsSystem.Windows.Forms.NavigateEventArgs)HandlesDataGrid1.NavigateDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=123;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringsql="Select*from影片租借信息"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片租借信息2")mytable=mySet.Tables("影片租借信息2")DimiAsIntegerFori=0Tomytable.Rows.Count-1IfDataGrid1.IsSelected(i)ThenComboBox1.Text=mytable.Rows(i).Item(0)ComboBox2.Text=mytable.Rows(i).Item(1)TextBox10.Text=mytable.Rows(i).Item(2)TextBox11.Text=mytable.Rows(i).Item(3)sql="Select*from租借学生信息where学号='"&Trim(ComboBox1.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"租借学生信息2")mytable=mySet.Tables("租借学生信息2")Ifmytable.Rows(0).Item(0)=ComboBox1.TextThenTextBox1.Text=mytable.Rows(0).Item(1)TextBox2.Text=mytable.Rows(0).Item(2)TextBox3.Text=mytable.Rows(0).Item(3)EndIfsql="Select*from影片信息where影片信息='"&Trim(ComboBox2.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片信息2")mytable=mySet.Tables("影片信息2")Ifmytable.Rows(0).Item(0)=ComboBox2.TextThenTextBox4.Text=mytable.Rows(0).Item(1)TextBox5.Text=""+mytable.Rows(0).Item(2)TextBox6.Text=""+mytable.Rows(0).Item(3)TextBox7.Text=""+mytable.Rows(0).Item(4)TextBox8.Text=""+mytable.Rows(0).Item(5)TextBox9.Text=""+mytable.Rows(0).Item(6)EndIfEndIfNextEndSubPrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.ClickDimmyconnAsSqlConnection=NewSqlConnection("server=(local);userid=sa;pwd=123;database=影片管理")DimmydataAsSqlDataAdapterDimmySetAsNewDataSet()DimmytableAsNewDataTable()DimsqlAsStringSelectCaseflagBorrowsql="Select*from影片租借信息where学号='"&Trim(ComboBox1.Text)&"'And影片编号='"&Trim(ComboBox2.Text)&"'And影片种类='"&Trim(TextBox10.Text)&"'"mydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片租借信息2")mytable=mySet.Tables("影片租借信息2")Ifmytable.Rows.Count=1ThenMsgBox("这借阅记录已经存在！",vbOKOnly+vbExclamation,"警告")ExitSubEndIfIfTrim(TextBox11.Text)>Trim(TextBox7.Text)ThenMsgBox("库存量不足！",vbOKOnly+vbExclamation,"警告")ExitSubElsesql="insert

影片租借信息(学号,影片编号,影片名称,影片种类,租借时间,数量)values('"&Trim(ComboBox1.Text)&"','"&Trim(ComboBox2.Text)&"','"&Trim(TextBox4.Text)&"','"&Trim(TextBox5.Text)&"','"&Trim(TextBox10.Text)&"',"&CInt(Trim(TextBox11.Text))&")"EndIfmydata=NewSqlDataAdapter(sql,myconn)mydata.Fill(mySet,"影片租借信息3")mytable=mySet.Tables("影片租借信息3")DataGrid1.DataSource=mySet.Tables("影片租借信息3")Display()sql="update影片信息set库存量="&Trim(TextBox7.Text)-Trim(TextBox11.Text)&"where影片编号='"&Trim(ComboBox2.Text)&"'"mydata