1.数据库设计部分内容
（1）系统功能分析【袋装咖啡网上销售系统】
该咖啡进销系统的使用主要是客户和销售管理员。客户可以通过系统浏览咖啡信息，查询咖啡的余量，产生订单。销售管理员通过系统可以对客户信息、供应商信息、咖啡信息以及销售的信息进行管理。
前台客户功能：1）咖啡信息模块：咖啡信息的浏览，查询搜索等
2）顾客信息管理模块：处理顾客信息的注册、登录
3）订单模块：查询、修改（订单可以分成两个部分，一部分记录地址、邮编 等信息，另一部分记录咖啡数量、咖啡编号等信息）
后台管理员功能：1）供应商管理：对供应商信息进行增加、删除、修改、查询
2）咖啡数据管理：插入、删除、修改
（2）将实体转换为关系模式
1）供应商（供应商编号，供应商名称，供应商地址，供应商联系方式）
2）咖啡（咖啡编号，咖啡名称，咖啡价格）
3）供应单（供应商编号，咖啡编号，供应量）
4）进货单（进货单编号，咖啡编号，供应商编号，咖啡数量，进货单价，进货日期）
5）入库单（入库单编号，咖啡编号，咖啡数量，入库日期）
6）顾客（顾客编号，顾客名字，顾客联系方式）
7）销售单（销售单号，咖啡编号，顾客编号，咖啡数量，销售金额，销售日期）
8）出库单（出库单号，咖啡编号，咖啡数量，出库日期）
9）库存单（咖啡编号，咖啡名称，咖啡数量）
10）管理员表（管理员编号、密码、管理员名字、联系方式）
2.vb语言设计的前端界面
1.欢迎页面：提供三种身份
2.游客身份进入：进入咖啡数据界面。
（若已有顾客账号可直接下单，若没有可以跳到顾客登录注册界面进行注册。）
3.顾客身份进入（登录成功后跟以游客身份进入所看到的一样）
4.管理员身份进入
3.【重点】SQL SERVER数据库和vb语言设计的前端如何连接
1.打开vb界面——>工程——>部件——>勾选Microsoft ADO Data Control 6.0——>应用——>点击左侧窗口栏右边列倒数第二个图标——>将图标放到第一个窗口的某个位置——>右击——>ADODC属性页——>使用连接字符串（将连接字符串复制好，用于各窗口与数据库进行代码连接）——>生成——>应用
4.前端界面实现代码
Form1:
Private Sub Command1_Click()
Form5.Show
End Sub
Private Sub Command2_Click()
Form2.Show
End Sub
Private Sub Command3_Click()
Form3.Show
End Sub
Private Sub Form_Load()
End Sub
Form2：
Private Sub Command2_Click()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=********;Password=********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Text1.Text) = "" Then
MsgBox "顾客编号不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Text1.SetFocus
Else
SQL = "select * from Customer where CustomerNo ='" & Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "顾客编号不存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Text1 = ""
Text1.SetFocus
Else
If Trim(rs.Fields(1)) = Trim(Text2) Then
MsgBox "登录成功！"
rs.Close
Unload Me
Form5.Show
Else
MsgBox "密码错误，请重新输入！", vbOKOnly + vbExclamation, "错误"
End If
End If
End If
End Sub
Private Sub Command1_Click()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=*********;Password=********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Text1.Text) = "" Then
MsgBox "顾客编号不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
Text1.Text = Trim(Text1.Text)
SQL = "select CustomerNo from Customer where CustomerNo = '" & Text1.Text & "'"
rs.Open SQL, cn
If rs.EOF = False Then
MsgBox "用户名已存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
rs.Close
Dim str1 As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
SQL = "Provider=**********;Password=*********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
SQL = "select * from Customer where CustomerNo = '" & Trim(Text1.Text) & "'"
rs.Open SQL, cn, 3, 2
If Text1.Text = "" Or Text2.Text = "" Then
MsgBox "输入的所有信息都不能为空！"
Else
cn.Execute ("insert into Customer (CustomerNo,CustomerName,CustomerTel) values('" + Text1.Text + " ','" + Text2.Text + " ', '" + Text3.Text + " ') ")
MsgBox "注册成功！"
End If
End If
End If
End Sub
Private Sub Form_Load()
End Sub
Form4：
Private Sub Command2_Click()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=***********;Password=*********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Form4.Text1.Text) = "" Then
MsgBox "咖啡不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
Text1.Text = Trim(Text1.Text)
SQL = "select CoffeeNo from Coffee where CoffeeNo = '" & Text1.Text & "'"
rs.Open SQL, cn
If rs.EOF = False Then
MsgBox "已存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
rs.Close
Dim str1 As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
SQL = "Provider=***********;Password=*********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee;"
cn.Open SQL
SQL = "select * from Coffee where CoffeeNo = '" & Trim(Form4.Text1.Text) & "'"
rs.Open SQL, cn, 3, 2
If Text1.Text = "" Or Text2.Text = "" Or Text3.Text = "" Then
MsgBox "输入的所有信息都不能为空！"
Else
cn.Execute ("insert into Coffee (CoffeeNo,CoffeeName,CoffeePrice) values('" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "') ")
MsgBox "成功！"
Unload Me
End If
End If
End If
End Sub
Private Sub Command1_Click()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=**********;Password=********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Form4.Text1.Text) = "" Then
MsgBox "咖啡编号不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
SQL = "select * from Coffee where CoffeeNo ='" & Form4.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "咖啡编号不存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Text1 = ""
Text1.SetFocus
Else
rs.Close
Dim str1 As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
SQL = "Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
SQL = "select * from Coffee where CoffeeNo = '" & Trim(Form4.Text1.Text) & "'"
rs.Open SQL, cn, 3, 2
str1 = MsgBox("你确定要修改吗！", vbOKCancel + 32, "提示")
If str1 = vbOK Then
If Form4.Text1.Text = "" Or Form4.Text2.Text = "" Or Form4.Text3.Text = "" Then
MsgBox "输入的所有信息都不能为空！"
Else
rs("CoffeeNo") = Trim(Text1.Text)
rs("CoffeeName") = Trim(Text2.Text)
rs("CoffeePrice") = Trim(Text3.Text)
rs.Update
MsgBox "修改成功！"
Unload Me
Form8.Show
End If
End If
End If
End If
End Sub
Private Sub Command3_Click()
Dim str1 As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
SQL = "Provider=***********;Password=**********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
SQL = "select * from Coffee where CoffeeNo ='" & Form4.Text1.Text & "'"
rs.Open SQL, cn, 3, 2
str1 = MsgBox("你确定要删除吗！", vbOKCancel + 32, "提示")
If str1 = vbOK Then
If Text1.Text = "" Then
MsgBox "咖啡编号不能为空！"
Text1.Text = ""
Text1.SetFocus
Else
rs.Delete
rs.Update
MsgBox "删除成功！"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text1.SetFocus
End If
End If
End Sub
Private Sub Command4_Click()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=**********;Password=*********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Form4.Text1.Text) = "" Then
MsgBox "咖啡编号不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
SQL = "select * from Coffee where CoffeeNo ='" & Form4.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "咖啡编号不存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Text1 = ""
Text1.SetFocus
Else
SQL = "select * from Coffee where CoffeeNo = '" & Form4.Text1.Text & "'"
Text1.Text = rs("CoffeeNo")
Text2.Text = rs("CoffeeName")
Text3.Text = rs("CoffeePrice")
End If
End If
End Sub
Private Sub Form_Load()
End Sub
From11：
Private Sub Command1_Click()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=************;Password=*********;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Form11.Text1.Text) = "" Then
MsgBox "订单编号不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
SQL = "select * from SaleList where SalesNo ='" & Form11.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "订单编号不存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Text1 = ""
Text1.SetFocus
Else
SQL = "select * from SaleList where SalesNo = '" & Form11.Text1.Text & "'"
Text1.Text = rs("SalesNo")
Text2.Text = rs("CoffeeNo")
Text3.Text = rs("CustomerNo")
Text4.Text = rs("CoffeeAmount")
Text5.Text = rs("SalesMoney")
Text6.Text = rs("SalesTime")
End If
End If
End Sub
Private Sub Form_Load()
Dim SQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Data Source=ASUS;Initial Catalog=daizhuangcoffee"
cn.Open SQL
If Trim(Form11.Text1.Text) = "" Then
MsgBox "订单编号不能为空，请重新输入！", vbOKOnly + vbExclamation, "错误"
Else
SQL = "select * from SaleList where SalesNo ='" & Form11.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "订单编号不存在，请重新输入！", vbOKOnly + vbExclamation, "错误"
Text1 = ""
Text1.SetFocus
Else
SQL = "select * from SaleList where SalesNo = '" & Form11.Text1.Text & "'"
Text1.Text = rs("SalesNo")
Text2.Text = rs("CoffeeNo")
Text3.Text = rs("CustomerNo")
Text4.Text = rs("CoffeeAmount")
Text5.Text = rs("SalesMoney")
Text6.Text = rs("SalesTime")
End If
End If
End Sub
