frmquerydisk.frm

数据库课程设计

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmQueryDisk 
   Caption         =   "影碟查询"
   ClientHeight    =   4095
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   8760
   LinkTopic       =   "Form1"
   ScaleHeight     =   4095
   ScaleWidth      =   8760
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton cmdCancel 
      Caption         =   "返回(&C)"
      Height          =   375
      Left            =   7080
      TabIndex        =   10
      Top             =   960
      Width           =   1455
   End
   Begin VB.CommandButton cmdQuery 
      Caption         =   "查询(&Q)"
      Height          =   375
      Left            =   7080
      TabIndex        =   9
      Top             =   360
      Width           =   1455
   End
   Begin MSFlexGridLib.MSFlexGrid fgDisks 
      Height          =   2295
      Left            =   120
      TabIndex        =   8
      Top             =   1680
      Width           =   8535
      _ExtentX        =   15055
      _ExtentY        =   4048
      _Version        =   393216
   End
   Begin VB.Frame Frame1 
      Caption         =   "选择查询条件"
      Height          =   1455
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   6615
      Begin VB.OptionButton optQuery 
         Caption         =   "查询全部影碟"
         Height          =   375
         Index           =   3
         Left            =   3480
         TabIndex        =   7
         Top             =   840
         Width           =   1575
      End
      Begin VB.ComboBox cboDate 
         Height          =   315
         Left            =   4800
         TabIndex        =   6
         Text            =   "Combo2"
         Top             =   360
         Width           =   1695
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "按购买日期"
         Height          =   255
         Index           =   2
         Left            =   3480
         TabIndex        =   5
         Top             =   360
         Width           =   1335
      End
      Begin VB.TextBox txtName 
         Height          =   375
         Left            =   1560
         TabIndex        =   4
         Top             =   840
         Width           =   1575
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "按影碟名称"
         Height          =   375
         Index           =   1
         Left            =   240
         TabIndex        =   3
         Top             =   840
         Width           =   1335
      End
      Begin VB.ComboBox cboCategory 
         Height          =   315
         Left            =   1560
         TabIndex        =   2
         Text            =   "Combo1"
         Top             =   360
         Width           =   1575
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "按影碟类别"
         Height          =   375
         Index           =   0
         Left            =   240
         TabIndex        =   1
         Top             =   360
         Width           =   1335
      End
   End
End
Attribute VB_Name = "frmQueryDisk"
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 cboCategory_Click()
optQuery(0).Value = True
End Sub

Private Sub cboDate_Click()
optQuery(2).Value = True

End Sub

Private Sub cmdCancel_Click()
Unload Me

End Sub

Private Sub cmdQuery_Click()
queryDisks
End Sub

Private Sub Form_Load()

'窗体居中显示
Me.Top = (Screen.Height - Me.Height) \ 2
Me.Left = (Screen.Width - Me.Width) \ 2

initCategories
initBuyDate
initTableTitle
End Sub
Sub initCategories()
'方法的作用：将类别表中的类别名称显示到列表框中

Dim rstCategory As ADODB.Recordset

    '从数据库中读取所有类别并添加到组合列表框中
    sqlStr = "select name from categories"
    Set rstCategory = ExecuteSQL(sqlStr, msgText)
    cboCategory.Clear
    
    If Not rstCategory.EOF Then
        
            Do While Not rstCategory.EOF
                cboCategory.AddItem Trim(rstCategory.Fields(0))
                rstCategory.MoveNext
            Loop
            cboCategory.ListIndex = 0
        
    Else
        MsgBox "没有类别信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstCategory.Close

End Sub

Sub initBuyDate()
Dim rstBuyDate As ADODB.Recordset

    '从数据库中读取所有购买日期并添加到组合列表框中
    sqlStr = "select DISTINCT buyDate from disks"
    Set rstBuyDate = ExecuteSQL(sqlStr, msgText)
    cboDate.Clear
    
    If Not rstBuyDate.EOF Then
        
            Do While Not rstBuyDate.EOF
                cboDate.AddItem Trim(rstBuyDate.Fields(0))
                rstBuyDate.MoveNext
            Loop
            cboDate.ListIndex = 0
        
    Else
        MsgBox "没有类别信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstBuyDate.Close

End Sub

Sub queryDisks()
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    
    If optQuery(0).Value = True Then
         sqlStr = "select * from disks where category='" & cboCategory.Text & "'"
    End If
    
    If optQuery(1).Value = True Then
         sqlStr = "select * from disks where diskName='%" & txtName.Text & "%'"
    End If
    
    If optQuery(2).Value = True Then
         sqlStr = "select * from disks where buyDate=#" & cboDate.Text & "#"
    End If
    
    If optQuery(3).Value = True Then
         sqlStr = "select * from disks"
    End If
    
    
    Set rs = ExecuteSQL(sqlStr, msgText)
    
    If rs.RecordCount = 0 Then
    
        MsgBox "没有查找满足条件的数据！", vbExclamation, "提示"
        fgDisks.Rows = 1
       
    Else
        
        fgDisks.Rows = rs.RecordCount + 1
        
        fgDisks.Cols = 9
        
        '设定行高
        For i = 0 To fgDisks.Rows - 1
           fgDisks.RowHeight(i) = 280
        Next i
        
        '设定列的属性
        fgDisks.Row = 0
        
        For i = 0 To fgDisks.Cols - 1
           fgDisks.Col = i  '指定当前列为第i列
           fgDisks.FixedAlignment(i) = 4  '每列内容居中显示
           Select Case i
               Case 0
                  fgDisks.ColWidth(i) = 600  '设定列宽
                  fgDisks.Text = "编号"
               Case 1
                  fgDisks.ColWidth(i) = 1200  '设定列宽
                  fgDisks.Text = "影碟名称"
               Case 2
                  fgDisks.ColWidth(i) = 1000  '设定列宽
                  fgDisks.Text = "购买日期"
               Case 3
                  fgDisks.ColWidth(i) = 600  '设定列宽
                  fgDisks.Text = "日租金"
               Case 4
                  fgDisks.ColWidth(i) = 500  '设定列宽
                  fgDisks.Text = "库存"
               Case 5
                  fgDisks.ColWidth(i) = 600  '设定列宽
                  fgDisks.Text = "总数量"
               Case 6
                  fgDisks.ColWidth(i) = 1000  '设定列宽
                  fgDisks.Text = "影片类型"
               Case 7
                  fgDisks.ColWidth(i) = 2600  '设定列宽
                  fgDisks.Text = "内容简介"
               Case 8
                  fgDisks.ColWidth(i) = 1000  '设定列宽
                  fgDisks.Text = "备注"
            End Select
               
        Next i
           
           
        'rs.MoveFirst
        
        i = 1
        
        While (Not rs.EOF)
        
           fgDisks.Row = i
           For j = 0 To fgDisks.Cols - 1
           
            fgDisks.Col = j '设置当前为列为第j列
            fgDisks.CellAlignment = 4  '每列内容居中显示
        
            
            Select Case j
               Case 0
                  fgDisks.Text = rs.Fields("no")
               Case 1
                  fgDisks.Text = rs.Fields("diskName")
               Case 2
                  fgDisks.Text = rs.Fields("buyDate")
               Case 3
                  fgDisks.Text = rs.Fields("payForRent")
               Case 4
                  fgDisks.Text = rs.Fields("stock")
               Case 5
                  fgDisks.Text = rs.Fields("total")
               Case 6
                  fgDisks.Text = rs.Fields("category")
               Case 7
                  fgDisks.Text = rs.Fields("description")
               Case 8
                  fgDisks.Text = rs.Fields("memo")
            End Select
          Next j
        
            rs.MoveNext
            i = i + 1
        Wend
    
    End If
    
    rs.Close

End Sub


Private Sub txtName_Change()
optQuery(1).Value = True

End Sub

Sub initTableTitle()
Dim i As Integer

fgDisks.Rows = 1

fgDisks.Cols = 9

'设定行高
   fgDisks.RowHeight(0) = 280

'设定列的属性
fgDisks.Row = 0

For i = 0 To fgDisks.Cols - 1
   fgDisks.Col = i  '指定当前列为第i列
   fgDisks.FixedAlignment(i) = 4  '每列内容居中显示
   Select Case i
       Case 0
          fgDisks.ColWidth(i) = 600  '设定列宽
          fgDisks.Text = "编号"
       Case 1
          fgDisks.ColWidth(i) = 1200  '设定列宽
          fgDisks.Text = "影碟名称"
       Case 2
          fgDisks.ColWidth(i) = 1000  '设定列宽
          fgDisks.Text = "购买日期"
       Case 3
          fgDisks.ColWidth(i) = 600  '设定列宽
          fgDisks.Text = "日租金"
       Case 4
          fgDisks.ColWidth(i) = 500  '设定列宽
          fgDisks.Text = "库存"
       Case 5
          fgDisks.ColWidth(i) = 600  '设定列宽
          fgDisks.Text = "总数量"
       Case 6
          fgDisks.ColWidth(i) = 1000  '设定列宽
          fgDisks.Text = "影片类型"
       Case 7
          fgDisks.ColWidth(i) = 2600  '设定列宽
          fgDisks.Text = "内容简介"
       Case 8
          fgDisks.ColWidth(i) = 1000  '设定列宽
          fgDisks.Text = "备注"
    End Select
       
Next i

End Sub