一、设计目标与要求
1、课程设计目的：探讨如何开发数据库应用系统
2、课程设计要求：初步认识和了解ADO.NET；
掌握ADO.NET存取SQL Sever 2000数据库数据的基本方法；
掌握VB.NET操作数据库的方法。
二、课程设计的应用背景及实际工作流程
1、应用背景 电影协会租借电影给学生所用
2、工作流程 需求分析、概念设计、逻辑设计、数据库实施阶段、调试与测试
3、开发环境 SQL Sever 2000数据库、Microsoft Visual Studio 2005
编程语言：T-SQL，vb
三、数据库设计
1、需求分析
根据影片管理的特点，影片管理系统应具有如下基本功能：
（1） 能对系统用户进行管理；
（2） 能对租借学生数据进行管理，即可以对租借学生数据进行查询、增加、删除、修改操作；
（3） 可对影片数据进行管理，即可对影片数据进行查询、增加、删除、修改操作；
（4） 对影片数据的查询可以普通查询、按种类查询、按名称查询。
（5） 可对租借影片数据进行管理，即可对租借影片数据进行查询、增加、删除操作；
（6） 对租借影片数据的查询可以按学号、影片编号、影片种类查询。
2、概念设计
图1 影片管理系统的E-R简图
3、逻辑设计
创建“影片管理”数据库：
打开企业管理器，创建数据库
创建“用户”表：
图2 用户表
创建“租借学生信息”表：
图4 租借学生信息表属性
创建“影片信息”表：
图5 创建影片信息表
创建“影片租借信息”表：
图6 创建影片租借信息表
四、应用系统设计
1、系统功能模块结构图
图6 影片管理系统功能模块图
2、主要模块的代码实现及关键部分的算法说明
1.公共模块设计
Module Module1
Public flag As Integer
Public flagReader As Integer
Public flagBook As Integer
Public flagBorrow As Integer
Sub mian()
flag = 0
End Sub
End Module
2.主控模块设计
图8 主控界面的设计
主控界面的代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Public Sub Form1_Load(ByVal sender As System.Object, System.EventArgs) Handles MyBase.Load
If flag = 0 Then
e As ByVal
MenuItem2.Enabled = False
MenuItem3.Enabled = False
MenuItem4.Enabled = False
Else
MenuItem2.Enabled = True
MenuItem3.Enabled = True
MenuItem4.Enabled = True
End If
End Sub
Private Sub MenuItem1_Click(ByVal sender As System.Object, System.EventArgs) Handles MenuItem1.Click
Dim objChild As New Form2()
objChild.MdiParent = Me
e As ByVal
objChild.frm = Me
objChild.Show()
End Sub
Private Sub MenuItem5_Click(ByVal sender As System.Object, System.EventArgs) Handles MenuItem5.Click
flagReader = 1
Dim objChild As New Form3()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem6_Click(ByVal sender As System.Object, System.EventArgs) Handles MenuItem6.Click
flagReader = 2
e As e As ByVal ByVal
Dim objChild As New Form3()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem7_Click(ByVal sender As System.Object, System.EventArgs) Handles MenuItem7.Click
flagReader = 3
Dim objChild As New Form3()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem8_Click(ByVal sender As System.Object, System.EventArgs) Handles MenuItem8.Click
e As e As ByVal ByVal
flagReader = 4
Dim objChild As New Form3()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem9.Click
flagBook = 1
Dim objChild As New Form4()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem10_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MenuItem10.Click
flagBook = 2
Dim objChild As New Form4()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem11.Click
flagBook = 3
Dim objChild As New Form4()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem13.Click
flagBook = 4
Dim objChild As New Form4()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem14.Click
Dim objChild As New Form7()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem15_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem15.Click
Dim objChild As New Form8()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem16_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem16.Click
flagBorrow = 1
Dim objChild As New Form5()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem17_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem17.Click
flagBorrow = 2
Dim objChild As New Form5()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem19_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem19.Click
flagBorrow = 3
Dim objChild As New Form5()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem20_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem20.Click
flagBorrow = 4
Dim objChild As New Form5()
objChild.MdiParent = Me
objChild.Show()
End Sub
Private Sub MenuItem21_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem21.Click
Dim objChild As New Form6()
objChild.MdiParent = Me
objChild.Show()
End Sub
End Class
3.登录模块设计
（1）登录界面设计
图9 登录界面的设计
（2）主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form2
Public frm As Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select * from 用户 where 用户名='\" & Trim(TextBox1.Text) & \"'\" & \"and 密码='\" & Trim(TextBox2.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"用户\")
mytable = mySet.Tables(\"用户\")
If mytable.Rows.Count > 0 Then
flag = 1
frm.Form1_Load(New Object, New System.EventArgs)
Close()
Else
MsgBox(\"密码不正确，请重新输入!\")
TextBox1.Text = \"\"
TextBox2.Text = \"\"
End If
End Sub
4.租借学生信息管理模块设计
（1）租借学生信息管理添加、修改、删除、查询界面设计
图10 租借学生信息管理界面设计
（2）主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form3
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Select Case flagReader
Case 1
Button1.Text = \"添加\"
Case 2
Button1.Text = \"修改\"
Case 3
Button1.Text = \"删除\"
Case 4
Button1.Text = \"查询\"
End Select
Display()
End Sub
Private Sub Display()
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
Dim i As Integer
sql = \"Select*from 租借学生信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息\")
mytable = mySet.Tables(\"租借学生信息\")
DataGrid1.DataSource = mySet.Tables(\"租借学生信息\")
For i = 0 To mytable.Rows.Count - 1
ComboBox1.Items.Add(mytable.Rows(i).Item(0))
Next
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal ByVal
e
As
System.EventArgs)
sender As
System.Object, Handles
ComboBox1.SelectedIndexChanged
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 租借学生信息 where 学号='\" & Trim(ComboBox1.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息1\")
mytable = mySet.Tables(\"租借学生信息1\")
If mytable.Rows(0).Item(0) = ComboBox1.Text Then
TextBox1.Text = mytable.Rows(0).Item(1)
TextBox2.Text = mytable.Rows(0).Item(2)
TextBox3.Text = mytable.Rows(0).Item(3)
End If
End Sub
Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user
id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 租借学生信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息2\")
mytable = mySet.Tables(\"租借学生信息2\")
Dim i As Integer
For i = 0 To mytable.Rows.Count - 1
If DataGrid1.IsSelected(i) Then
ComboBox1.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)
End If
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
Dim i As Integer
Dim StudentID, name, department, cla As String
Select Case flagReader
StudentID = ComboBox1.Text
ComboBox1.Items.Add(Trim(StudentID))
name = TextBox1.Text
department = TextBox2.Text
cla = TextBox3.Text
sql = \"Select * from 租借学生信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息3\")
mytable = mySet.Tables(\"租借学生信息3\")
For i = 0 To mytable.Rows.Count - 1
If mytable.Rows(i).Item(0) = StudentID Then
MsgBox(\"这个学号已存在！\警告\")
Exit Sub
End If
Next
sql = \"insert into 租借学生信息(学号,姓名,系别,班级)values(\" & \" '\" & StudentID & \"','\" & name & \"','\" & department & \"','\" & cla & \"')\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"save\")
DataGrid1.DataSource = mySet.Tables(\"save\")
Display()
MsgBox(\"数据已添加！\提示\")
name = TextBox1.Text
department = TextBox2.Text
cla = TextBox3.Text
sql = \"Select * from 租借学生信息 where 学号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息3\")
mytable = mySet.Tables(\"租借学生信息3\")
If mytable.Rows.Count = 0 Then
MsgBox(\"这个学号不存在，不能修改！\")
Exit Sub
End If
sql = \"update 租借学生信息 set 姓名='\" & name & \"',系别='\" & department & \"',班级='\" & cla & \"' where 学号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
DataGrid1.DataSource = mySet.Tables(\"update\")
Display()
MsgBox(\"数据已修改！\提示\")
sql = \"Select * from 租借学生信息 where 学号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息3\")
mytable = mySet.Tables(\"租借学生信息3\")
If mytable.Rows.Count = 0 Then
MsgBox(\"这个学号不存在，不能删除！\")
Exit Sub
End If
sql = \"delete from 租借学生信息 where 学号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"delete\")
DataGrid1.DataSource = mySet.Tables(\"delete\")
Display()
MsgBox(\"数据已删除！\提示\")
Case 4
MsgBox(\"请直接通过选择相应的记录进行查询！\")
End Select
End Sub
5.影片信息管理模块设计
（1）影片信息管理添加、修改、删除、查询界面设计
图11 影片信息管理界面设计
（2）主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form4
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Select Case flagBook
Case 1
Button1.Text = \"添加\"
Case 2
Button1.Text = \"修改\"
Case 3
Button1.Text = \"删除\"
Case 4
Button1.Text = \"查询\"
End Select
Display()
End Sub
Private Sub Display()
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
Dim i As Integer
sql = \"Select*from 影片信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息\")
mytable = mySet.Tables(\"影片信息\")
DataGrid1.DataSource = mySet.Tables(\"影片信息\")
ComboBox1.Items.Clear()
For i = 0 To mytable.Rows.Count - 1
ComboBox1.Items.Add(mytable.Rows(i).Item(0))
Next
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal ByVal
e
As
System.EventArgs)
sender As
System.Object, Handles
ComboBox1.SelectedIndexChanged
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 影片信息 where 影片编号='\" & Trim(ComboBox1.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息1\")
mytable = mySet.Tables(\"影片信息1\")
If mytable.Rows(0).Item(0) = ComboBox1.Text Then
TextBox1.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)
End If
End Sub
Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 影片信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息2\")
mytable = mySet.Tables(\"影片信息2\")
Dim i As Integer
For i = 0 To mytable.Rows.Count - 1
If DataGrid1.IsSelected(i) Then
ComboBox1.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)
End If
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
Dim i As Integer
Dim bookID, bookName, booksSpecies, publishingTime, author As String
Dim price As Double
Dim storage As Integer
Select Case flagBook
bookID = ComboBox1.Text
ComboBox1.Items.Add(Trim(bookID))
bookName = TextBox1.Text
booksSpecies = TextBox2.Text
price = CDbl(TextBox3.Text)
storage = CInt(TextBox4.Text)
publishingTime = TextBox5.Text
author = TextBox6.Text
sql = \"Select * from 影片信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息3\")
mytable = mySet.Tables(\"影片信息3\")
For i = 0 To mytable.Rows.Count - 1
If mytable.Rows(i).Item(0) = bookID Then
MsgBox(\"这个影片编号已存在！\警告\")
Exit Sub
End If
Next
If Trim(TextBox2.Text) = \"\" Then
sql = \"insert 影片信息(影片编号,影片名称,影片种类,价格,库存量,上映时间,导演) values('\" & Trim(ComboBox1.Text) & \"','\" & Trim(TextBox1.Text) & \"',NULL,\" & Trim(TextBox3.Text) & \
Else
sql = \"insert 影片信息(影片编号,影片名称,影片种类,价格,库存量,上映时间,导演) values('\" & Trim(ComboBox1.Text) & \"','\" & Trim(TextBox1.Text) & \"','\" & Trim(TextBox2.Text) & \"',\" & Trim(TextBox3.Text) & \& Trim(TextBox4.Text) & \
End If
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"save\")
mytable = mySet.Tables(\"影片信息3\")
If Trim(TextBox5.Text) <> \"\" Then
sql = \"update 影片信息 set 上映时间='\" & Trim(TextBox5.Text) & \"' where 影片编号='\" & bookID & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"save\")
End If
If Trim(TextBox6.Text) <> \"\" Then
sql = \"update 影片信息 set 导演='\" & Trim(TextBox6.Text) & \"' where 影片编号='\" & bookID & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"save\")
End If
DataGrid1.DataSource = mySet.Tables(\"save\")
Display()
MsgBox(\"数据已添加！\提示\")
sql = \"update 影片信息 set 影片名称='\" & TextBox1.Text & \"',价格='\" & price & \"',库存量='\" & storage & \"'where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
If Trim(TextBox2.Text) <> \"\" Then
sql = \"update 影片信息 set 影片种类='\" & TextBox2.Text & \"' where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
ElseIf Trim(TextBox2.Text) = \"\" Then
sql = \"update 影片信息 set 影片种类=NULL where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
End If
If Trim(TextBox5.Text) <> \"\" Then
sql = \"update 影片信息 set 上映时间='\" & TextBox5.Text & \"' where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
ElseIf Trim(TextBox5.Text) = \"\" Then
sql = \"update 影片信息 set 上映时间=NULL where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
End If
If Trim(TextBox6.Text) <> \"\" Then
sql = \"update 影片信息 set 导演='\" & TextBox6.Text & \"' where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
ElseIf Trim(TextBox6.Text) = \"\" Then
sql = \"update 影片信息 set 导演=NULL where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"update\")
End If
DataGrid1.DataSource = mySet.Tables(\"update\")
Display()
MsgBox(\"数据已修改！\提示\")
sql = \"delete from 影片信息 where 影片编号='\" & ComboBox1.Text & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"delete\")
DataGrid1.DataSource = mySet.Tables(\"delete\")
Display()
MsgBox(\"数据已删除！\提示\")
（3）影片信息管理按种类查询界面设计
图12 影片信息管理按种类查询界面设计
（4）影片信息管理按种类查询主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form7
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
If Trim(TextBox1.Text) = \"\" Then
MsgBox(\"按影片种类查询时，影片种类不能为空！\")
Exit Sub
End If
sql = \"Select * from 影片信息 where 影片种类 like '%\" & Trim(TextBox1.Text) & \"%'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"按种类查询\")
mytable = mySet.Tables(\"按种类查询\")
DataGrid1.DataSource = mySet.Tables(\"按种类查询\")
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, System.EventArgs) Handles Button2.Click
Close()
End Sub
End Class
（5）影片信息管理按名称查询界面设计
ByVal e As
图13 影片信息管理按名称查询界面设计
（6）影片信息管理按名称查询主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form8
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
If Trim(TextBox1.Text) = \"\" Then
MsgBox(\"按影片名称查询时，影片名称不能为空！\")
Exit Sub
End If
sql = \"Select * from 影片信息 where 影片名称 like '%\" & Trim(TextBox1.Text) & \"%'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"按名称查询\")
mytable = mySet.Tables(\"按名称查询\")
DataGrid1.DataSource = mySet.Tables(\"按名称查询\")
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Close()
End Sub
End Class
6.影片租借管理模块设计
（1）影片租借管理添加、删除、查询界面设计
图14 影片租借管理界面设计
（2）主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form5
Private Sub Form5_Load(ByVal sender As System.Object, System.EventArgs) Handles MyBase.Load
Select Case flagBorrow
e As ByVal Case 1
Button1.Text = \"添加\"
Case 2
Button1.Text = \"删除\"
Private Sub Display()
Dim myconn As SqlConnection id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
Dim i As Integer
sql = \"Select*from 影片租借信息\"
New SqlConnection(\"server=(local);user =
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片租借信息\")
mytable = mySet.Tables(\"影片租借信息\")
DataGrid1.DataSource = mySet.Tables(\"影片租借信息\")
sql = \"Select*from 租借学生信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息\")
mytable = mySet.Tables(\"租借学生信息\")
ComboBox1.Items.Clear()
For i = 0 To mytable.Rows.Count - 1
ComboBox1.Items.Add(mytable.Rows(i).Item(0))
Next
sql = \"Select*from 影片信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息\")
mytable = mySet.Tables(\"影片信息\")
ComboBox2.Items.Clear()
For i = 0 To mytable.Rows.Count - 1
ComboBox2.Items.Add(mytable.Rows(i).Item(0))
Next
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal ByVal
e
As
System.EventArgs)
sender As
System.Object, Handles
ComboBox1.SelectedIndexChanged
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 租借学生信息 where 学号='\" & Trim(ComboBox1.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息1\")
mytable = mySet.Tables(\"租借学生信息1\")
If mytable.Rows(0).Item(0) = ComboBox1.Text Then
TextBox1.Text = mytable.Rows(0).Item(1)
TextBox2.Text = mytable.Rows(0).Item(2)
TextBox3.Text = mytable.Rows(0).Item(3)
End If
End Sub
Private Sub ComboBox2_SelectedIndexChanged(ByVal ByVal
e
As
System.EventArgs)
sender As
System.Object, Handles
ComboBox2.SelectedIndexChanged
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 影片信息 where 影片编号='\" & Trim(ComboBox2.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息1\")
mytable = mySet.Tables(\"影片信息1\")
If mytable.Rows(0).Item(0) = ComboBox2.Text Then
TextBox4.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)
End If
End Sub
Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user
id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
sql = \"Select*from 影片租借信息\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片租借信息2\")
mytable = mySet.Tables(\"影片租借信息2\")
Dim i As Integer
For i = 0 To mytable.Rows.Count - 1
If DataGrid1.IsSelected(i) Then
ComboBox1.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 = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"租借学生信息2\")
mytable = mySet.Tables(\"租借学生信息2\")
If mytable.Rows(0).Item(0) = ComboBox1.Text Then
TextBox1.Text = mytable.Rows(0).Item(1)
TextBox2.Text = mytable.Rows(0).Item(2)
TextBox3.Text = mytable.Rows(0).Item(3)
End If
sql = \"Select*from 影片信息 where 影片信息='\" & Trim(ComboBox2.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片信息2\")
mytable = mySet.Tables(\"影片信息2\")
If mytable.Rows(0).Item(0) = ComboBox2.Text Then
TextBox4.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)
End If
End If
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
Select Case flagBorrow
sql = \"Select*from 影片租借信息 where 学号='\" & Trim(ComboBox1.Text) & \"'And 影片编号='\" & Trim(ComboBox2.Text) & \"'And 影片种类='\" & Trim(TextBox10.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片租借信息2\")
mytable = mySet.Tables(\"影片租借信息2\")
If mytable.Rows.Count = 1 Then
MsgBox(\"这借阅记录已经存在！\警告\")
Exit Sub
End If
If Trim(TextBox11.Text) > Trim(TextBox7.Text) Then
MsgBox(\"库存量不足！\警告\")
Exit Sub
Else
sql = \"insert 影片租借信息(学号,影片编号,影片名称,影片种类,租借时间,数量) values ('\" & Trim(ComboBox1.Text) & \"','\" & Trim(ComboBox2.Text) & \"','\" & Trim(TextBox4.Text) & \"','\" & Trim(TextBox5.Text) & \"','\" & Trim(TextBox10.Text) & \"',\" & CInt(Trim(TextBox11.Text)) & \")\"
End If
mydata = New SqlDataAdapter(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 = New SqlDataAdapter(sql, myconn)
-
mydata.Fill(mySet, \"影片信息\")
mytable = mySet.Tables(\"影片信息\")
TextBox7.Text = Trim(TextBox7.Text) - Trim(TextBox11.Text)
MsgBox(\"数据已添加！\提示\")
sql = \"Select*from 影片租借信息 where 影片编号='\" & Trim(ComboBox2.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"影片租借信息\")
mytable = mySet.Tables(\"影片租借信息\")
DataGrid1.DataSource = mySet.Tables(\"影片租借信息\")
End Select
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Close()
End Sub
Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter
End Sub
Private Sub TextBox10_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox10.TextChanged
End Sub
Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox5.TextChanged
End Sub
End Class
（3）影片租借管理按时间查询界面设计
图15 影片租借管理按时间查询界面设计
（4）影片租借管理按时间查询主要代码
Imports System.Data
Imports System.Data.SqlClient
Public Class Form6
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As SqlConnection = New SqlConnection(\"server=(local);user id=sa;pwd=123;database=影片管理\")
Dim mydata As SqlDataAdapter
Dim mySet As New DataSet()
Dim mytable As New DataTable()
Dim sql As String
If Trim(TextBox1.Text) = \"\" Then
MsgBox(\"请填写正确的起始时间！\")
Exit Sub
End If
If Trim(TextBox2.Text) = \"\" Then
MsgBox(\"请填写正确的结束时间！\")
Exit Sub
End If
sql = \"Select * from 影片租借信息 where 租借时间 > '\" & Trim(TextBox1.Text) & \"'\" & \"and 租借时间< '\" & Trim(TextBox2.Text) & \"'\"
mydata = New SqlDataAdapter(sql, myconn)
mydata.Fill(mySet, \"按时间查询\")
mytable = mySet.Tables(\"按时间查询\")
DataGrid1.DataSource = mySet.Tables(\"按时间查询\")
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
End Sub
Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
End Class
五、运行及测试
图1
图2
图3
图4
图5
图6
图7
图8
图9
图10
图11
图12
图13
同一位学生不止可以租借一部电影：
图14
图15
图16
图17
六、用户说明
设置用户的名为：sa，密码是：123
七、设计心得体会
通过建立这个对电影协会管理影片有帮助的系统，我获益良多
我要对影片系统做了系统分析，用到哪些表，表之间怎样连接，设计几个窗体，窗体的功能等！同时也了解到写一个程序是多么的不易，它不但需要一定的专业知识，还要有足够的耐心和细心，一个小小的算法虽然不是很难，但你只要有一点点的错误，，它就不能运行，它就不是一个完整的好程序。在数据库链接上刚开始总是出错，数据库连接不上，后来经过请教同学，仔细看书，经改正，程序正常运行了。
通过这次的程序设计，我对SQL和vb语言加深了了解，能够建立存储过程，触发器，函数等!能过通过vb编程实现影片管理的基本功能，通过窗体界面操作，输出结果。同时我也看到我的许多不足，vb语言运用的还不太熟练，还有自己的耐心不够，写程序时经常粗心犯错，以后要好好锻炼一下。我还认识到在学习当中与同学的交流是十分必要的，通过与同学的交流我对许多的知识认识的更加深刻。
一个简单的影片管理系统（还有很多改进的余地），其实也是要花费很多的时间与精力的，但是成功后带来的愉悦，也与之成正比
因篇幅问题不能全部显示，请点此查看更多更全内容