frmaddorder.frm

数据库课程设计

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmAddOrder 
   Caption         =   "添加新订单"
   ClientHeight    =   5955
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7335
   LinkTopic       =   "Form1"
   ScaleHeight     =   5955
   ScaleWidth      =   7335
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton cmdCancel 
      Caption         =   "返回(&C)"
      Height          =   375
      Left            =   3960
      TabIndex        =   21
      Top             =   5400
      Width           =   1455
   End
   Begin VB.CommandButton cmdSave 
      Caption         =   "保存(&S)"
      Height          =   375
      Left            =   1800
      TabIndex        =   20
      Top             =   5400
      Width           =   1455
   End
   Begin MSFlexGridLib.MSFlexGrid fgProducts 
      Height          =   1335
      Left            =   120
      TabIndex        =   18
      Top             =   3840
      Width           =   7095
      _ExtentX        =   12515
      _ExtentY        =   2355
      _Version        =   393216
   End
   Begin VB.Frame Frame2 
      Caption         =   "订单产品信息"
      Height          =   1455
      Left            =   120
      TabIndex        =   12
      Top             =   2040
      Width           =   7095
      Begin VB.TextBox txtProduct 
         Enabled         =   0   'False
         Height          =   285
         Index           =   1
         Left            =   4080
         TabIndex        =   25
         Text            =   "Text2"
         Top             =   960
         Width           =   1455
      End
      Begin VB.TextBox txtProduct 
         Height          =   285
         Index           =   0
         Left            =   1320
         TabIndex        =   24
         Text            =   "Text1"
         Top             =   960
         Width           =   1455
      End
      Begin VB.CommandButton cmdAdd 
         Caption         =   "添加(&A)"
         Height          =   375
         Left            =   5760
         TabIndex        =   17
         Top             =   480
         Width           =   1095
      End
      Begin VB.ComboBox cboNo 
         Height          =   315
         Left            =   4080
         TabIndex        =   15
         Top             =   480
         Width           =   1455
      End
      Begin VB.ComboBox cboProduct 
         Height          =   315
         Left            =   1320
         TabIndex        =   14
         Top             =   480
         Width           =   1455
      End
      Begin VB.Label Label11 
         Caption         =   "产品价格："
         Height          =   255
         Left            =   3120
         TabIndex        =   23
         Top             =   960
         Width           =   975
      End
      Begin VB.Label Label10 
         Caption         =   "定购数量："
         Height          =   255
         Left            =   360
         TabIndex        =   22
         Top             =   960
         Width           =   975
      End
      Begin VB.Label Label8 
         Caption         =   "产品编号："
         Height          =   255
         Left            =   3120
         TabIndex        =   16
         Top             =   480
         Width           =   975
      End
      Begin VB.Label Label7 
         Caption         =   "产品名称："
         Height          =   255
         Left            =   360
         TabIndex        =   13
         Top             =   480
         Width           =   975
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "订单基本信息"
      Height          =   1815
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   7095
      Begin VB.TextBox txtOrder 
         Height          =   285
         Index           =   3
         Left            =   1320
         TabIndex        =   10
         Top             =   1320
         Width           =   1935
      End
      Begin VB.TextBox txtOrder 
         Height          =   285
         Index           =   2
         Left            =   4560
         TabIndex        =   8
         Top             =   840
         Width           =   2175
      End
      Begin VB.TextBox txtOrder 
         Height          =   285
         Index           =   1
         Left            =   1320
         TabIndex        =   6
         Top             =   810
         Width           =   1935
      End
      Begin VB.ComboBox cboClient 
         Height          =   315
         Left            =   4440
         TabIndex        =   4
         Top             =   360
         Width           =   2295
      End
      Begin VB.TextBox txtOrder 
         Height          =   285
         Index           =   0
         Left            =   1320
         TabIndex        =   2
         Top             =   360
         Width           =   1935
      End
      Begin VB.Label Label6 
         Caption         =   "（若订单未完成，此项为空）"
         ForeColor       =   &H000000C0&
         Height          =   255
         Left            =   3360
         TabIndex        =   11
         Top             =   1320
         Width           =   2415
      End
      Begin VB.Label Label5 
         Caption         =   "交付日期："
         Height          =   255
         Left            =   360
         TabIndex        =   9
         Top             =   1320
         Width           =   975
      End
      Begin VB.Label Label4 
         Caption         =   "应交付日期："
         Height          =   255
         Left            =   3480
         TabIndex        =   7
         Top             =   840
         Width           =   1215
      End
      Begin VB.Label Label3 
         Caption         =   "下单日期："
         Height          =   255
         Left            =   360
         TabIndex        =   5
         Top             =   840
         Width           =   975
      End
      Begin VB.Label Label2 
         Caption         =   "客户名称："
         Height          =   255
         Left            =   3480
         TabIndex        =   3
         Top             =   360
         Width           =   1095
      End
      Begin VB.Label Label1 
         Caption         =   "订单编号："
         Height          =   255
         Left            =   360
         TabIndex        =   1
         Top             =   360
         Width           =   975
      End
   End
   Begin VB.Label Label9 
      Caption         =   "订单中已添加的产品："
      Height          =   255
      Left            =   120
      TabIndex        =   19
      Top             =   3600
      Width           =   1935
   End
End
Attribute VB_Name = "frmAddOrder"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public sqlStr As String
Public msgText As String

Private Sub cboNo_Click()
getPriceInfo
End Sub

Private Sub cboProduct_Click()
getProductNo
End Sub

Private Sub cmdAdd_Click()
addProductToOrder
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdSave_Click()
saveOrder
End Sub

Private Sub Form_Load()
initTextbox
initClientInfo
initProductName
initTable
End Sub

Sub initClientInfo()

Dim rstClient As ADODB.Recordset

    '从数据库中读取所有客户名称
    sqlStr = "select clientName from client"
    Set rstClient = ExecuteSQL(sqlStr, msgText)
    cboClient.Clear
    
    If Not rstClient.EOF Then
        Do While Not rstClient.EOF
            cboClient.AddItem Trim(rstClient.Fields(0))
            rstClient.MoveNext
        Loop
        cboClient.ListIndex = 0
    Else
        MsgBox "没有客户信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstClient.Close

End Sub

Sub initProductName()
Dim rstProductName As ADODB.Recordset

    '从数据库中读取所有的产品名称
    sqlStr = "select productName from products"
    Set rstProductName = ExecuteSQL(sqlStr, msgText)
    cboProduct.Clear
    
    If Not rstProductName.EOF Then
        Do While Not rstProductName.EOF
            cboProduct.AddItem Trim(rstProductName.Fields(0))
            rstProductName.MoveNext
        Loop
        cboProduct.ListIndex = 0
    Else
        MsgBox "没有产品信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstProductName.Close

End Sub

Sub getProductNo()
Dim rstProductNo As ADODB.Recordset

    '从数据库中获取产品ID
    sqlStr = "select productID from products" _
           & " where productName='" & cboProduct.Text & "'"
    Set rstProductNo = ExecuteSQL(sqlStr, msgText)
    cboNo.Clear
    
    If Not rstProductNo.EOF Then
        Do While Not rstProductNo.EOF
            cboNo.AddItem Trim(rstProductNo.Fields(0))
            rstProductNo.MoveNext
        Loop
        cboNo.ListIndex = 0
    Else
        MsgBox "没有找到产品ID信息！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstProductNo.Close

End Sub

Sub getPriceInfo()
Dim rstPrice As ADODB.Recordset

    '从数据库中获取产品ID
    sqlStr = "select price from products" _
           & " where productID='" & cboNo.Text & "'"
    Set rstPrice = ExecuteSQL(sqlStr, msgText)
    
    If Not rstPrice.EOF Then
            txtProduct(1).Text = Trim(rstPrice.Fields(0))
    Else
        MsgBox "没有找到产品价格信息！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstPrice.Close

End Sub


Sub initTextbox()
Dim i As Integer
For i = 0 To 3
txtOrder(i).Text = ""
Next i
txtProduct(0).Text = ""
txtProduct(1).Text = ""
End Sub

Sub initTable()
Dim j As Integer

    fgProducts.Rows = 1
    fgProducts.Cols = 6
    
    fgProducts.RowHeight(fgProducts.Rows - 1) = 280
        
   '设定列的属性
   fgProducts.Row = fgProducts.Rows - 1

   For j = 0 To fgProducts.Cols - 1
      
   fgProducts.Col = j '设置当前为列为第j列
   fgProducts.CellAlignment = 4  '每列内容居中显示
   
       
   Select Case j
       Case 0
             fgProducts.ColWidth(j) = 600  '设定列宽
             fgProducts.Text = "序号"
       Case 1
             fgProducts.ColWidth(j) = 1600  '设定列宽
             fgProducts.Text = "产品名称"
       Case 2
             fgProducts.ColWidth(j) = 1200  '设定列宽
             fgProducts.Text = "订单号"
       Case 3
             fgProducts.ColWidth(j) = 1600  '设定列宽
             fgProducts.Text = "客户名称"
       Case 4
             fgProducts.ColWidth(j) = 1000  '设定列宽
             fgProducts.Text = "定购数量"
       Case 5
             fgProducts.ColWidth(j) = 1000  '设定列宽
             fgProducts.Text = "单价"
       End Select
     Next j
        

End Sub

Sub addProductToOrder()
Dim i As Integer
Dim j As Integer

    If txtProduct(0).Text = "" Then
        MsgBox "请填写数量！", vbExclamation, "提示"
        Exit Sub
    End If
        
    fgProducts.Rows = fgProducts.Rows + 1
    
    fgProducts.RowHeight(fgProducts.Rows - 1) = 280
        
   '设定列的属性
   fgProducts.Row = fgProducts.Rows - 1
   

   For j = 0 To fgProducts.Cols - 1
      
   fgProducts.Col = j '设置当前为列为第j列
   fgProducts.CellAlignment = 4  '每列内容居中显示
   
       
   Select Case j
       Case 0
             fgProducts.Text = fgProducts.Rows - 1
       Case 1
             fgProducts.Text = cboProduct.Text
       Case 2
             fgProducts.Text = cboNo.Text
       Case 3
             fgProducts.Text = cboClient.Text
       Case 4
             fgProducts.Text = txtProduct(0).Text
       Case 5
             fgProducts.Text = txtProduct(1).Text
       End Select
     Next j

End Sub

Sub saveOrder()
'保存订单基本信息
saveBasicInfo
'保存订单中的产品信息
saveProductsInOrder
End Sub

Sub saveBasicInfo()
 Dim conn As ADODB.Connection
     
   '保存订单基本信息
   
   If txtOrder(3).Text = "" Then
    sqlStr = "insert into orders(orderNo,clientName," _
           & "orderDate,dueDate) values('" _
           & txtOrder(0).Text & "','" & cboClient.Text & "','" _
           & txtOrder(1).Text & "','" & txtOrder(2).Text & "')"
   Else
    sqlStr = "insert into orders(orderNo,clientName," _
        & "orderDate,dueDate,deliverDate) values('" _
        & txtOrder(0).Text & "','" & cboClient.Text & "','" _
        & txtOrder(1).Text & "','" & txtOrder(2).Text & "','" _
        & txtOrder(3).Text & "')"
   End If
   
   On Error GoTo exitSub
   Set conn = New ADODB.Connection
   conn.Open connStr
   
   '执行SQL语句
   conn.Execute sqlStr
   MsgBox "成功修改订单基本信息!!"
   
exitSub:
   
   conn.Close

End Sub

Sub saveProductsInOrder()
 Dim rstSales As ADODB.Recordset
 Dim i As Integer
 Dim j As Integer
 
  '检查是否在订单中添加了产品信息
   If fgProducts.Rows < 2 Then
        MsgBox "订单中未添加任何产品信息！！"
        Exit Sub
   Else
 
    '保存订单中的产品信息
     
     sqlStr = "select * from sales"
     Set rstSales = ExecuteSQL(sqlStr, msgText)
    
     For i = 1 To fgProducts.Rows - 1
        fgProducts.Row = i
        rstSales.AddNew
        For j = 1 To fgProducts.Cols - 1
            fgProducts.Col = j
            '在lendinfo表中添加新记录
            Select Case j
                Case 1
                    rstSales.Fields("productName") = fgProducts.Text
                Case 2
                    rstSales.Fields("orderNo") = fgProducts.Text
                Case 3
                    rstSales.Fields("clientname") = fgProducts.Text
                Case 4
                    rstSales.Fields("amount") = fgProducts.Text
                Case 5
                    rstSales.Fields("price") = fgProducts.Text
            End Select
        Next j
        rstSales.Update
     Next i
     rstSales.Close
         
     MsgBox "订单产品信息添加完成！", vbOKOnly + vbExclamation, "警告"
 
End If
End Sub