1.數(shù)據(jù)庫(kù)設(shè)計(jì)部分內(nèi)容
（1）系統(tǒng)功能分析【袋裝咖啡網(wǎng)上銷售系統(tǒng)】
該咖啡進(jìn)銷系統(tǒng)的使用主要是客戶和銷售管理員。客戶可以通過(guò)系統(tǒng)瀏覽咖啡信息，查詢咖啡的余量，產(chǎn)生訂單。銷售管理員通過(guò)系統(tǒng)可以對(duì)客戶信息、供應(yīng)商信息、咖啡信息以及銷售的信息進(jìn)行管理。
前臺(tái)客戶功能：1）咖啡信息模塊：咖啡信息的瀏覽，查詢搜索等
? ? ? ? ? ? ? ? ? ? ? ? ?2）顧客信息管理模塊：處理顧客信息的注冊(cè)、登錄
? ? ? ? ? ? ? ? ? ? ? ? ? 3）訂單模塊：查詢、修改（訂單可以分成兩個(gè)部分，一部分記錄地址、郵編 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 等信息，另一部分記錄咖啡數(shù)量、咖啡編號(hào)等信息）
后臺(tái)管理員功能：1）供應(yīng)商管理：對(duì)供應(yīng)商信息進(jìn)行增加、刪除、修改、查詢
? ? ? ? ? ? ? ? ? ? ? ? ? ? 2）咖啡數(shù)據(jù)管理：插入、刪除、修改
（2）將實(shí)體轉(zhuǎn)換為關(guān)系模式
? ? ? ? 1）供應(yīng)商（供應(yīng)商編號(hào)，供應(yīng)商名稱，供應(yīng)商地址，供應(yīng)商聯(lián)系方式）
? ? ? ? 2）咖啡（咖啡編號(hào)，咖啡名稱，咖啡價(jià)格）
? ? ? ? 3）供應(yīng)單（供應(yīng)商編號(hào)，咖啡編號(hào)，供應(yīng)量）
? ? ? ? 4）進(jìn)貨單（進(jìn)貨單編號(hào)，咖啡編號(hào)，供應(yīng)商編號(hào)，咖啡數(shù)量，進(jìn)貨單價(jià)，進(jìn)貨日期）
? ? ? ? 5）入庫(kù)單（入庫(kù)單編號(hào)，咖啡編號(hào)，咖啡數(shù)量，入庫(kù)日期）
? ? ? ?6）顧客（顧客編號(hào)，顧客名字，顧客聯(lián)系方式）
? ? ? ?7）銷售單（銷售單號(hào)，咖啡編號(hào)，顧客編號(hào)，咖啡數(shù)量，銷售金額，銷售日期）
? ? ? ?8）出庫(kù)單（出庫(kù)單號(hào)，咖啡編號(hào)，咖啡數(shù)量，出庫(kù)日期）
? ? ? ?9）庫(kù)存單（咖啡編號(hào)，咖啡名稱，咖啡數(shù)量）
? ? ? 10）管理員表（管理員編號(hào)、密碼、管理員名字、聯(lián)系方式）
2.vb語(yǔ)言設(shè)計(jì)的前端界面
1.歡迎頁(yè)面：提供三種身份
2.游客身份進(jìn)入：進(jìn)入咖啡數(shù)據(jù)界面。
（若已有顧客賬號(hào)可直接下單，若沒有可以跳到顧客登錄注冊(cè)界面進(jìn)行注冊(cè)。）
3.顧客身份進(jìn)入（登錄成功后跟以游客身份進(jìn)入所看到的一樣）
4.管理員身份進(jìn)入
3.【重點(diǎn)】SQL SERVER數(shù)據(jù)庫(kù)和vb語(yǔ)言設(shè)計(jì)的前端如何連接
1.打開vb界面——>工程——>部件——>勾選Microsoft ADO Data Control 6.0——>應(yīng)用——>點(diǎn)擊左側(cè)窗口欄右邊列倒數(shù)第二個(gè)圖標(biāo)——>將圖標(biāo)放到第一個(gè)窗口的某個(gè)位置——>右擊——>ADODC屬性頁(yè)——>使用連接字符串（將連接字符串復(fù)制好，用于各窗口與數(shù)據(jù)庫(kù)進(jìn)行代碼連接）——>生成——>應(yīng)用
4.前端界面實(shí)現(xiàn)代碼
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 "顧客編號(hào)不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
Text1.SetFocus
Else
SQL = "select * from Customer? where CustomerNo ='" & Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "顧客編號(hào)不存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
Text1 = ""
Text1.SetFocus
Else
If Trim(rs.Fields(1)) = Trim(Text2) Then
MsgBox "登錄成功！"
rs.Close
Unload Me
Form5.Show
Else
MsgBox "密碼錯(cuò)誤，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "顧客編號(hào)不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "用戶名已存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "注冊(cè)成功！"
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 "咖啡不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "已存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "咖啡編號(hào)不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
Else
SQL = "select * from Coffee? where CoffeeNo ='" & Form4.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "咖啡編號(hào)不存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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("你確定要?jiǎng)h除嗎！", vbOKCancel + 32, "提示")
If str1 = vbOK Then
If Text1.Text = "" Then
MsgBox "咖啡編號(hào)不能為空！"
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 "咖啡編號(hào)不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
Else
SQL = "select * from Coffee? where CoffeeNo ='" & Form4.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "咖啡編號(hào)不存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "訂單編號(hào)不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
Else
SQL = "select * from SaleList? where SalesNo ='" & Form11.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "訂單編號(hào)不存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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 "訂單編號(hào)不能為空，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
Else
SQL = "select * from SaleList? where SalesNo ='" & Form11.Text1.Text & "'"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic
If rs.EOF Then
MsgBox "訂單編號(hào)不存在，請(qǐng)重新輸入！", vbOKOnly + vbExclamation, "錯(cuò)誤"
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
